node.js - 使用 sequelize 检索链接状态字段的值

Question

我有一个带有字段的Vendor实体：statusId

'use strict';
module.exports = {
    up: (queryInterface, Sequelize) => {
        return queryInterface.createTable('Vendors', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            // other properties
            statusId: {
                type: Sequelize.INTEGER,
                references: {
                    model: 'VendorStatuses',
                    key: 'id'
                },
                onUpdate: 'CASCADE',
                onDelete: 'SET NULL'
            },
            // other properties
        });
    },
    down: (queryInterface, Sequelize) => {
        return queryInterface.dropTable('Vendors');
    }
};

这与相应的VendorStatus：

'use strict';
module.exports = {
    up: (queryInterface, Sequelize) => {
        return queryInterface.createTable('VendorStatuses', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            name: {
                type: Sequelize.STRING
            },
        });
    },
    down: (queryInterface, Sequelize) => {
        return queryInterface.dropTable('VendorStatuses');
    }
};

该Vendor模型具有关联：

Vendor.belongsTo(models.VendorStatus, { foreignKey: 'statusId'})

我怎样才能让 Sequelize 生成相当于：

SELECT "Vendor"."id", "Vendor"."name", 
    "VendorStatus"."name" AS "status"
FROM "Vendors" AS "Vendor"
    LEFT OUTER JOIN "VendorStatuses" AS "VendorStatus"
    ON "Vendor"."statusId" = "VendorStatus"."id";

基本上，我希望在返回给客户端的 JSON 结果中status填写该字段。VendorStatus.name

我试过了：

    const attribs = ['id', 'name', 'email', ['VendorStatus.name', 'status']];
    const vendors = await models.Vendor.findAll({ attributes: attribs, include: [models.VendorStatus] });

但这给了我错误column "VendorStatus.name" does not exist。生成的 SQL 为：

SELECT "Vendor"."id", 
       "Vendor"."name", 
       "Vendor"."email", 
       "vendorstatus.name"   AS "status", 
       "VendorStatus"."id"   AS "VendorStatus.id", 
       "VendorStatus"."name" AS "VendorStatus.name" 
FROM   "vendors" AS "Vendor" 
       LEFT OUTER JOIN "vendorstatuses" AS "VendorStatus" 
                    ON "Vendor"."statusid" = "VendorStatus"."id"

版本是：

[Node: 12.14.1, CLI: 5.5.1, ORM: 5.21.3]

标签： node.jspostgresqlsequelize.js

Answer 1

您可以尝试以下代码来获取VendorStatus表的名称属性为status.

const vendors = await models.Vendor.findAll({ attributes: ['id', 'name', 'email'], include: [{ model: models.VendorStatus, attributes: [['name', 'status']] }] });

我希望它有帮助！