node.js - Sequelize 内连接无法获得正确值

Question

我正在使用 Sequelize 查询三个模型。它们是公司、产品和订阅。
公司表:

Company_ID int primary_key
Copmany_Name varchar(20)
Email varchar(20)

订阅表:

Subscription_ID int primary_key
Company_ID int foreign key of company table
Product_ID int foreign key of product table
Subscription_order int

产品表:

Product_ID int primary_key
Product_Name varchar(20)

我的两种模型的代码:
订阅模式:

module.exports = (sequelize, DataTypes) =>
    sequelize.define(
        "Subscription",
        {
            subscriptionId: {
                type: DataTypes.INTEGER,
                primaryKey: true,
                allowNull: false,
                field: "Subscription_ID",
            },
            companyId: {
                type: DataTypes.INTEGER,
                field: "Company_ID",
                references: {
                    model: "Company",
                    key: "companyId",
                },
            },
            productId: {
                type: DataTypes.INTEGER,
                field: "Product_ID",
                references: {
                    model: "Product",
                    key: "productId",
                },
            },
            orderId: {
                type: DataTypes.INTEGER,
                field: "Subscription_order",
            },
        },
        ...

公司型号:

module.exports = (sequelize, DataTypes) =>
    sequelize.define(
        "Company",
        {
            companyId: {
                type: DataTypes.INTEGER,
                primaryKey: true,
                allowNull: false,
                field: "Company_ID",
            },
            companyName: {
                type: DataTypes.STRING(50),
                field: "Company_Name",
            },
            email: {
                type: DataTypes.STRING(50),
                field: "Email",
            },
        },
        ...

产品型号:

module.exports = (sequelize, DataTypes) =>
    sequelize.define(
        "Product",
        {
            productId: {
                type: DataTypes.INTEGER,
                primaryKey: true,
                allowNull: false,
                field: "Product_ID",
            },
            productName: {
                type: DataTypes.STRING(100),
                field: "Product_Name",
            },
            ...

我设置的关系是:

Subscription.hasMany(Company, { foreignKey: "companyId", as: "companies" })
Company.belongsTo(Subscription, { foreignKey: "companyId" })
Subscription.hasMany(Product, { foreignKey: "productId", as: "products" })
Product.belongsTo(Subscription, { foreignKey: "productId" })

在我的 Controller 上:

const subscription = await Subscription.findAll({
            where: {
                companyId,
            },
            include: {
                model: Company,
            },
        })
res.status(200).send({ status: 0, data: subscription })

我可以获得公司详细信息，例如

{
    "status": 0,
    "data": {
        "companyId": 2,
        "companyName": "testCompany",
        "email": "test@gmail.com",
        }
}

当我测试分开阅读公司详细信息和订阅详细信息时，订阅详细信息本身，但是，我始终无法显示加入的公司详细信息:

...
{
    "subscriptionId": 2,
    "companyId": 2,
    "productId": 5,
    "orderId": 4,
    "products": [
        {
            "productId": 2,
            "productName": "testProduct"
        }
    ],
    "companies": [
        {
            "companyId": 1,
            "companyName": "testcompany",
            "email": "testcompany@gmail.com",
        }
    ]
},
...

我期望的是这样的，我使用 SQL 查询来做的事情:

select * FROM Subscription subscription
INNER JOIN Company company on company.Company_ID = subscription.Company_ID
INNER JOIN Products products on products.Product_ID = subscription.Product_ID
where subscription.Company_ID= 2

这将重新运行如下，其中产品 ID 和公司 ID 相同:

...
{
    "subscriptionId": 2,
    "companyId": 2,
    "productId": 5,
    "orderId": 4,
    "products": [
        {
            "productId": 5,
            "productName": "testProduct"
        }
    ],
    "companies": [
        {
            "companyId": 2,
            "companyName": "testcompany",
            "email": "testcompany@gmail.com",
        }
    ]
},
...

我不知道为什么我的 Sequelize 包含的单个部分具有不同的公司 ID 和产品 ID。我的代码有什么问题？
我输出日志，它说

FROM
    [Subscription] AS [Subscription]
INNER JOIN [Products] AS [products] ON
    [Subscription].[Subscription_ID] = [products].[Product_ID]
LEFT OUTER JOIN [staging].[Dim_Company] AS [companies] ON
    [Subscription].[Subscription_ID] = [companies].[Company_ID]

为什么它使用subscription_id？

Answer 1

我错过了关系上的 sourceKey，正确的方法是

Subscription.hasMany(Company, { sourceKey: "companyId", foreignKey: "companyId", as: "companies" })
Subscription.hasMany(Product, { sourceKey: "productId", foreignKey: "productId", as: "products" })

没有sourceKey，sequelize默认会使用主键加入。