javascript - Sequelize 引用错误的外键

Question

有两个表，分别是 Transaction 和 Transaction Details。 recordId 是 Transaction Details 的外键。
我正在尝试通过 recordId 查找交易，然后包含交易详细信息。一个交易可以有许多交易详情。这是我的代码:

db.models.Transaction.hasMany(db.models.TransactionDetails, {
    foreignKey: 'recordId'
});

然后我查询 findOne ，它看起来像这样:

db.models.Transaction.findOne({
            where: {
                recordId: req.query.recordid
            } ,
            include: [{
                model: db.models.TransactionDetails
            }]
            
        })

但是当我在 Postman 上发送请求时，JSON 数据如下所示:

{
"error": false,
"message": {
    "id": "8151",
    "recordId": "6688",
    "transactionNo": "1563804469415",
    "cashierId": "4",
    "payType": "cash",
    "terminalNo": "0012346",
    "amount": 40,
    "discount": 0,
    "cardNo": "none",
    "transDate": "2019-07-22T14:23:26.000Z",
    "createdAt": "2019-07-22T14:20:19.679Z",
    "updatedAt": "2019-07-22T14:20:19.679Z",
    "pt-transactions-details": [
        {
            "id": "38048",
            "recordId": "8151", //this is wrong, the recordId must be the same as above which is 6688
            "transId": "3731",
            "productId": "539",
            "quantity": "1",
            "amount": 60,
            "terminal": "002789",
            "createdAt": "2019-09-13T01:22:48.349Z",
            "updatedAt": "2019-09-13T01:22:48.349Z"
        },
        {
            "id": "9921",
            "recordId": "8151", //this is wrong, the recordId must be the same as above which is 6688
            "transId": "3985",
            "productId": "1061",
            "quantity": "2",
            "amount": 100,
            "terminal": "0012346",
            "createdAt": "2019-07-05T03:44:49.406Z",
            "updatedAt": "2019-07-05T03:44:49.406Z"
        },
        {
            "id": "68848",
            "recordId": "8151", //this is wrong, the recordId must be the same as above which is 6688
            "transId": "5358",
            "productId": "1128",
            "quantity": "1",
            "amount": 160,
            "terminal": "171412",
            "createdAt": "2019-10-15T13:00:03.864Z",
            "updatedAt": "2019-10-15T13:00:03.864Z"
        }
    ]
}

}
有人可以帮我解决这个问题吗？我已经花了一天的时间试图弄清楚这一点。

Answer 1

简短的回答，您需要将 sourceKey 传递给 hasMany 方法。

Transaction.hasMany(TransactionDetail, { foreignKey: 'recordId', sourceKey: 'recordId' });

长答案，这是一个例子:
index.ts :

import { sequelize } from '../../db';
import { Model, DataTypes } from 'sequelize';
import assert from 'assert';

class Transaction extends Model {}
Transaction.init(
  {
    recordId: {
      unique: true,
      type: DataTypes.STRING,
    },
  },
  { sequelize, modelName: 'transactions' },
);

class TransactionDetail extends Model {}
TransactionDetail.init(
  {
    amount: DataTypes.INTEGER,
  },
  { sequelize, modelName: 'transaction_details' },
);

Transaction.hasMany(TransactionDetail, { foreignKey: 'recordId', sourceKey: 'recordId' });

(async function test() {
  try {
    await sequelize.sync({ force: true });
    await Transaction.create(
      { recordId: '6688', transaction_details: [{ amount: 60 }, { amount: 100 }, { amount: 160 }] },
      { include: [TransactionDetail] },
    );

    const rval = await Transaction.findOne({ where: { recordId: '6688' }, include: [TransactionDetail] });
    console.log(rval.dataValues);
    assert.equal(rval.transaction_details.length, 3, 'transaction details count should equal 3');
    const transactionDetailsDataValues = rval.transaction_details.map((d) => d.dataValues);
    console.log('transactionDetailsDataValues: ', transactionDetailsDataValues);
  } catch (error) {
    console.log(error);
  } finally {
    await sequelize.close();
  }
})();

上面代码的执行结果:

{ id: 1,
  recordId: '6688',
  transaction_details:
   [ transaction_details {
       dataValues: [Object],
       _previousDataValues: [Object],
       _changed: {},
       _modelOptions: [Object],
       _options: [Object],
       isNewRecord: false },
     transaction_details {
       dataValues: [Object],
       _previousDataValues: [Object],
       _changed: {},
       _modelOptions: [Object],
       _options: [Object],
       isNewRecord: false },
     transaction_details {
       dataValues: [Object],
       _previousDataValues: [Object],
       _changed: {},
       _modelOptions: [Object],
       _options: [Object],
       isNewRecord: false } ] }
transactionDetailsDataValues:  [ { id: 1, amount: 60, recordId: '6688' },
  { id: 2, amount: 100, recordId: '6688' },
  { id: 3, amount: 160, recordId: '6688' } ]

查看数据库中的数据记录:

node-sequelize-examples=# select * from "transactions";
 id | recordId
----+----------
  1 | 6688
(1 row)

node-sequelize-examples=# select * from "transaction_details";
 id | amount | recordId
----+--------+----------
  1 |     60 | 6688
  2 |    100 | 6688
  3 |    160 | 6688
(3 rows)

Sequelize 版本: "sequelize": "^5.21.3"
源代码: https://github.com/mrdulin/node-sequelize-examples/tree/master/src/examples/stackoverflow/60446814