Join on specific columns using Sequelize.js

Question

I am trying to do join some tables on specific columns using Sequelize.js.

So far, my code is something like:

table_1.findall({
  include: [{
    model: table_2
    attributes: ['id', 'another_id']
    include: [{
      model: table_3
      required: true
      attributes: ['time']
    }]
  }]
})

where each table's primary key is 'id'.

This seems to be equivalent to the following SQL (I am showing SELECT * for brevity, since that is not the focus of this question):

SELECT *
FROM table_1 as t1
LEFT OUTER JOIN table_2 as t2 ON t1.id = t2.t1_id
INNER JOIN table_3 as t3 ON t2.id = t3.t2_id

and I want to have something like:

SELECT *
FROM table_1 as t1
LEFT OUTER JOIN table_2 as t2 ON t1.id = t2.t1_id
INNER JOIN table_3 as t3 ON t2.another_id = t3.t2_id

Is there a way to force the join between t2 and t3 to use something either than the primary key of t2?

I have found the [options.include[].on] in the Sequelize documentation, but do not know what the syntax is for suppling my own ON condition.

joshua.paling · Accepted Answer · 2017-05-04 09:57:03Z

5

You want to define the special case foreign key name in the associations themselves, defined in the classMethods section of your models. Here's an example of a table that joins to the users table twice, with specially named foreign keys:

classMethods: {
  associate(models) {
    this.belongsTo(models.user, {
      foreignKey: 'created_by_user_id',
      as: 'created_by',
    });
    this.belongsTo(models.user, {
      foreignKey: 'updated_by_user_id',
      as: 'updated_by',
    });
  },
},

Then, in your findAll, there's no need to do anything special - sequelize will know what foreign key column to use automatically.

answered May 4, 2017 at 9:57

joshua.paling

14k4 gold badges47 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jaskaran Singh · Accepted Answer · 2020-11-06 14:08:28Z

1

You can also mention like this in you code/controller file:

const storeModel = model.store;
const bookedTicketModel = model.booked_ticket;
bookedTicketModel.belongsTo (storeModel, {foreignKey: 'storeId'});
storeModel.hasMany (bookedTicketModel, {foreignKey: 'id'});

answered Nov 6, 2020 at 14:08

Jaskaran Singh

2,58826 silver badges42 bronze badges

Collectives™ on Stack Overflow

Join on specific columns using Sequelize.js

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related