时间戳

首页 > 解决方案 > 如何在 NodeJS 中使用 Sequelize 执行三表连接

node.js - 如何在 NodeJS 中使用 Sequelize 执行三表连接

问题描述

我正在尝试使用 Sequelize ORM 创建一个 3 表连接。到目前为止我开发的代码是

    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)
    });

但我不断收到指向第一个“报告未定义”的错误belongsTo。

表定义是使用 sequelize-auto 创建的，我已经验证 tblReportHeadings 具有报告字段。

以下是 3 个相关表的定义：

tblReport 标题

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" },
        ]
      },
    ]
  });
};

tbl标题

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" },
        ]
      },
    ]
  });
};

表访问

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" },
        ]
      },
    ]
  });
};

我确信问题在于我如何创建连接，但作为 Sequelize 的新手，我不确定问题出在哪里。

因此，我试图完成的查询是：

`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`

标签： node.jssequelize.js

解决方案

所以我遇到的问题不是通过更改上面的代码而是通过下面编写的连接代码来纠正的：

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

必须改为

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 })

为了工作。

此代码仍然存在多个问题，但这是此特定错误消息的解决方案。确保完全限定每张表在这里似乎是必不可少的，并且假设 Sequelize 会收拾残局（有时会这样做）走捷径是不可靠的。

推荐阅读