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`