How to perform a three table join using Sequelize in NodeJS

Viewed 75

I am attempting to create a 3 table join using Sequelize ORM. The code I have developed so far is

    const sequelize = DB.GetDB(DB.reports);
    const rptDB = rpts.initModels(sequelize);

    rptDB.tblReportHeadings.hasMany(rptDB.tblReports, { foreignKey: rptDB.tblReports.id, targetKey: rptDB.tblReportHeadings.Report })
//Error points to Report on this next row
    rptDB.tblReports.belongsTo(rptDB.tblReportHeadings, { foreignKey: rptDB.tblReportHeadings.Report, targetKey: rptDB.tblReports.id });

    rptDB.tblReportHeadings.hasMany(rptDB.tblHeadings, { foreignKey: rptDB.tblHeadings.id, targetKey: rptDB.tblReportHeadings.Heading })
    rptDB.tblHeadings.belongsTo(rptDB.tblReportHeadings, { foreignKey: rptDB.tblReportHeadings.Heading, targetKey: rptDB.tblHeadings.id });

    rptDB.tblAccess.hasMany(rptDB.tblHeadings, { foreignKey: Heading, targetKey: id });
    rptDB.tblHeadings.belongsTo(rptDB.tblAccess, { foreignKey: id, targetKey: Heading })

    let ret = tblReports.findAll({
        attributes: [
            id, reportTitle
        ],
        include: [{
            model: rptDB.tblHeadings,
            attributes: [Heading]
        },
        {
            model: rptDB.tblAccess,
            where: { accessLevel: roles }
        }
        ],
        logging: (sql) => console.log(sql)
    });

but I keep getting the error that "Report is not defined" pointing to the first belongsTo.

The table definition was created using sequelize-auto and I have verified that tblReportHeadings has the report field.

Here are the definitions of the 3 relevant tables:

tblReportHeadings

const Sequelize = require('sequelize');
module.exports = function(sequelize, DataTypes) {
  return sequelize.define('tblReportHeadings', {
    id: {
      autoIncrement: true,
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true
    },
    Report: {
      type: DataTypes.INTEGER,
      allowNull: true
    },
    Heading: {
      type: DataTypes.INTEGER,
      allowNull: true
    }
  }, {
    sequelize,
    tableName: 'tblReportHeadings',
    schema: 'dbo',
    timestamps: false,
    indexes: [
      {
        name: "PK_tblReportHeadings",
        unique: true,
        fields: [
          { name: "id" },
        ]
      },
    ]
  });
};

tblHeadings

const Sequelize = require('sequelize');
module.exports = function(sequelize, DataTypes) {
  return sequelize.define('tblHeadings', {
    id: {
      autoIncrement: true,
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true
    },
    Heading: {
      type: DataTypes.STRING(100),
      allowNull: true
    }
  }, {
    sequelize,
    tableName: 'tblHeadings',
    schema: 'dbo',
    timestamps: false,
    indexes: [
      {
        name: "PK_tblHeadings",
        unique: true,
        fields: [
          { name: "id" },
        ]
      },
    ]
  });
};

tblAccess

const Sequelize = require('sequelize');
module.exports = function(sequelize, DataTypes) {
  return sequelize.define('tblAccess', {
    id: {
      autoIncrement: true,
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true
    },
    Heading: {
      type: DataTypes.INTEGER,
      allowNull: true
    },
    accessLevel: {
      type: DataTypes.STRING(40),
      allowNull: true
    }
  }, {
    sequelize,
    tableName: 'tblAccess',
    schema: 'dbo',
    timestamps: false,
    indexes: [
      {
        name: "PK_tblAccess",
        unique: true,
        fields: [
          { name: "id" },
        ]
      },
    ]
  });
};

I am sure the issue lies in how I am creating the join but as a newbie to Sequelize I am not certain where the issue lies.

The query I am attempting to accomplish is thus:

`SELECT     DISTINCT tblReports.id, tblReports.reportTitle, tblHeadings.Heading
    FROM         tblReports INNER JOIN
                  tblReportHeadings ON tblReports.id = tblReportHeadings.Report INNER JOIN
                  tblHeadings ON tblReportHeadings.Heading = tblHeadings.id INNER JOIN
                  tblAccess ON tblHeadings.id = tblAccess.Heading
    WHERE     (LOWER(tblAccess.accessLevel) IN (${roles.join(',')}))
    ORDER BY tblReports.reportTitle`
1 Answers

So the problem I was experiencing was rectified not by changing the code above but rather the join code written below:

rptDB.tblAccess.hasMany(rptDB.tblHeadings, { foreignKey: Heading, targetKey: id });
    rptDB.tblHeadings.belongsTo(rptDB.tblAccess, { foreignKey: id, targetKey: Heading })

had to be changed to

rptDB.tblAccess.hasMany(rptDB.tblHeadings, { foreignKey: rptDB.tblAccess.Heading, targetKey: rptDB.tblHeadings.id });
    rptDB.tblHeadings.belongsTo(rptDB.tblAccess, { foreignKey: rptDB.tblHeadings.id, targetKey: rptDB.tblAccess.Heading })

in order to work.

There are still multiple problems with this code but this is the solution to this specific error message. Making sure to fully qualify every table is essential here, it seems, and taking shortcuts assuming that Sequelize will pick up the pieces (as it sometimes does) is not reliable.

Related