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]

Answer 1

您可以尝试以下代码来获取 VendorStatus 表的 name 属性为 status 。

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

我希望它有帮助!