sequelize.js - Sequelize - 属于使用 targetKey

Question

我正在尝试将交叉引用数据的引用添加到我的转换表之一。但看起来 Sequelize 生成的查询不正确。

以下是我正在使用的表结构:

付款方式:



付款_Dtls:



以下是我的 Sequelize 模型:

付款方式:

const Sequelize = require('sequelize');
const connection = require('../middleware/sql-connection');

const PaymentType = connection.define('PAYMENT_TYPE', {
    'ID': { type: Sequelize.BIGINT, primaryKey: true },
    'CD': { type: Sequelize.STRING, allowNulls: false }
});

module.exports = PaymentType;

付款详情:

const Sequelize = require('sequelize');
const connection = require('../middleware/sql-connection');
const PaymentType = require('./payment-type');

const PaymentDetails = connection.define('PAYMENT_DTLS', {
    'ID': { type: Sequelize.BIGINT, primaryKey: true },
    'PAYMENT_TYPE_ID': { type: Sequelize.BIGINT, allowNull: false },
    'AMOUNT': { type: Sequelize.DECIMAL, allowNull: false },
    'USR_ID': { type: Sequelize.BIGINT, allowNull: false },
    'PAYMENT_DT': { type: Sequelize.DATE, allowNull: false },
    'STS': Sequelize.STRING
});

PaymentDetails.hasOne(PaymentType);
module.exports = PaymentDetails;

当我尝试在 PaymentDetails 查询中包含 PaymentType 时，生成的 SQL 似乎不正确。我的 findall 方法:

exports.payment_details__by_current_user = (req, res, next) => {
    PaymentDetails.findAll({
        include: [{ model: PaymentType }]
    })
        .then(requests => {return res.status(200).json(requests);})
        .catch(err => {return res.status(500).json({ message: err });});
};

以下是生成的 SQL 查询:

SELECT ... FROM [PAYMENT_DTLS] AS [PAYMENT_DTLS] LEFT OUTER JOIN [PAYMENT_TYPE] AS [PAYMENT_TYPE] 
ON [PAYMENT_DTLS].[ID] = [PAYMENT_TYPE].[PAYMENTDTLID];

正如我所期望的那样:

SELECT ... FROM [PAYMENT_DTLS] AS [PAYMENT_DTLS] LEFT OUTER JOIN [PAYMENT_TYPE] AS [PAYMENT_TYPE] 
ON [PAYMENT_DTLS].[PAYMENT_TYPE_ID] = [PAYMENT_TYPE].[ID];

我已经尝试了以下所有关联，但似乎没有任何效果:

选项1:

PaymentDetails.hasOne(PaymentType, { foreignKey: 'PAYMENT_TYPE_ID' });

选项 2:

PaymentType.belongsTo(PaymentDetails, { foreignKey: 'PAYMENT_TYPE_ID' });
PaymentDetails.hasOne(PaymentType,{foreignKey:'ID'});

选项 3:

PaymentType.belongsTo(PaymentDetails, { foreignKey: 'PAYMENT_TYPE_ID' });
PaymentDetails.hasOne(PaymentType, { foreignKey: 'ID', targetKey: 'PAYMENT_TYPE_ID' });

Answer 1

我认为 belongsTo() 应该做你需要的:

PaymentDetails.belongsTo(PaymentType, {foreignKey: 'PAYMENT_TYPE_ID'});

直觉上， hasOne() 似乎更合适……但是我在尝试让 hasOne() 使用自定义键时遇到了很多麻烦。