How to join 3 tables using Sequelize?

Question

I have 3 tables named:

    customers
columns are: id, customer_full_name

    transaction_details
columns are: id, customer_id, amount, merchant_id

    merchants
columns are: id, merchant_full_name

transaction_details table contains two foreign keys of customer_id and merchant_id. One customer may have multiple transactions. One merchant may have multiple transactions too.

Situation: Merchant logins to the website to view the transaction details belong to this merchant. What I would like to display is a table with the following columns:

a. Transaction ID
b. Customer Name
c. Transaction Amount

My code as below:

  Merchant.findAll({
      where: {
          id:req.session.userId,
      },
      include:[{
        model:TransactionDetails,
        required: false
      }]
    }).then(resultDetails => {
        var results = resultDetails;
});

My code above does not give me the result that I want. How can I fix this ?

Answer 1

What you need is belongsToMany association in case you haven't defined it yet. Here is the example

const Customer = sequelize.define('customer', {
  username: Sequelize.STRING,
});

const Merchant = sequelize.define('merchant', {
  username: Sequelize.STRING,
});
Customer.belongsToMany(Merchant, { through: 'CustomerMerchant' });
Merchant.belongsToMany(Customer, { through: 'CustomerMerchant' });

sequelize.sync({ force: true })
  .then(() => {
    Customer.create({
      username: 'customer1',
      merchants: {
        username: 'merchant1'
      },
    }, { include: [Merchant] }).then((result) => {
      Merchant.findAll({
        include: [{
          model: Customer
        }],
      }).then((result2) => {
        console.log('done', result2);


      })
    })
  });

Now result2 has all the values. Customer data can be accessed at result2[0].dataValues.customers[0].dataValues. CustomerMerchant data is available at result2[0].dataValues.customers[0].CustomerMerchant