Sequelize referencing to wrong foreign key

Question

There are two tables which are Transaction and Transaction Details. The recordId is a foreign key of Transaction Details.

I am trying to find a Transaction by recordId and then include the Transaction Details. A Transaction can have many Transaction Details. Here are my codes:

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

And then I'm querying a findOne and it looks like this:

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

But when I sent the request on Postman, the JSON data looked like this:

{
"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"
        }
    ]
}

}

Can someone help me regarding this? I already spent a day trying to figure this out.

Answer 1

Short answer, you need to pass sourceKey into hasMany method.

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

Long answer, here is an example:

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();
  }
})();

The execution result of above code:

{ 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' } ]

Check the data record in the database:

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 version: "sequelize": "^5.21.3"

source code: https://github.com/mrdulin/node-sequelize-examples/tree/master/src/examples/stackoverflow/60446814

Answer 2

You need to define Sequelize association as mentioned below -

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

I hope it helps!