Sequelize include multiple tables in query

Question

So i have the following structure receipts -> policies -> policholders

Every receipt belongs to a policy and every policy belong to a policyholder

What i want to do is when i fetch receipts i get an object back with the both the policy and policyholder included (i am using feathers-sequelize, hook is included below). I get the error that policyholders is not associated to receipts

Desired response:

{
    "ReceiptId": 1,
    "Supplement": 10.1,
    "policy": {
        "PolicyNumber": 1234
    },
    "policyholder": {
        "Name": "Joe",
        "Surname": "Blogs"
    }
}

Policies model

// See http://docs.sequelizejs.com/en/latest/docs/models-definition/
// for more of what you can do here.
const Sequelize = require("sequelize");
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get("sequelizeClient");

  const policies = sequelizeClient.define(
    "policies",
    {
      PolicyId: {
        autoIncrement: true,
        type: DataTypes.INTEGER,
        allowNull: false,
        primaryKey: true,
      },
      PolicyNumber: {
        type: DataTypes.STRING(30),
        allowNull: true,
      },
      CompanyId: {
        type: DataTypes.INTEGER,
        allowNull: true,
      },
      PolicyholderId: {
        type: DataTypes.INTEGER,
        allowNull: true,
      },
    },
    {
      hooks: {
        beforeCount(options) {
          options.raw = true;
        },
      },
    }
  );

  // eslint-disable-next-line no-unused-vars
  policies.associate = function (models) {
    // Define associations here
    // See http://docs.sequelizejs.com/en/latest/docs/associations/
    const { policyholders, companies } = models;
    policies.belongsTo(policyholders, { foreignKey: "PolicyholderId" });

  };

  return policies;
};

Policyholder model

// See http://docs.sequelizejs.com/en/latest/docs/models-definition/
// for more of what you can do here.
const Sequelize = require("sequelize");
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get("sequelizeClient");
  const policyholders = sequelizeClient.define(
    "policyholders",
    {
      PolicyholderId: {
        autoIncrement: true,
        type: DataTypes.INTEGER,
        allowNull: false,
        primaryKey: true,
      },
      Name: {
        type: DataTypes.STRING(250),
        allowNull: true,
      },
      Surname: {
        type: DataTypes.STRING(250),
        allowNull: true,
      },
    },
    {
      hooks: {
        beforeCount(options) {
          options.raw = true;
        },
      },
    }
  );

  // eslint-disable-next-line no-unused-vars
  policyholders.associate = function (models) {
    // Define associations here
    // See http://docs.sequelizejs.com/en/latest/docs/associations/
    const { policies } = models;

    policyholders.hasMany(policies, { foreignKey: "PolicyholderId" });
  };

  return policyholders;
};

receipts model

// See http://docs.sequelizejs.com/en/latest/docs/models-definition/
// for more of what you can do here.
const Sequelize = require("sequelize");
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get("sequelizeClient");
  const receipts = sequelizeClient.define(
    "receipts",
    {
      ReceiptId: {
        autoIncrement: true,
        type: DataTypes.INTEGER,
        allowNull: false,
        primaryKey: true,
      },
      PolicyId: {
        type: DataTypes.INTEGER,
        allowNull: true,
      },
      Supplement: {
        type: DataTypes.INTEGER,
        allowNull: true,
      },
    },
    {
      hooks: {
        beforeCount(options) {
          options.raw = true;
        },
      },
    }
  );

  // eslint-disable-next-line no-unused-vars
  receipts.associate = function (models) {
    // Define associations here
    // See http://docs.sequelizejs.com/en/latest/docs/associations/
    const { policies } = models;
    receipts.belongsTo(policies, { foreignKey: "PolicyId" });

  };

  return receipts;
};

get related hook

module.exports = function (options = {}) {
  return async (context) => {
    const { include, ...query } = context.params.query;

    const Policies = context.app.services.policies.Model;
    const Policyholders = context.app.services.policyholders.Model;

    context.params.sequelize = {
      include: [Policies, Policyholders],
      raw: false,
    };

    // Update the query to not include `include`
    context.params.query = query;

    return context;
  };
};

Answer 1

If Placeholders is linked to Receipts through Policies then you need to indicate Placeholders in an include option of Policies itself:

 context.params.sequelize = {
      include: [{
        model: Policies,
        include: [Policyholders]
      }],
      raw: false,
    };