Possible to WHERE on nested includes in Sequelize?

user_5262262 0
Viewed 3077

I've got a problem that I've been stuck on, to no avail - seemingly similar in nature to Where condition for joined table in Sequelize ORM, except that I'd like to query on a previous join. Perhaps code will explain my problem. Happy to provide any extra info.

Models:

A.hasMany(B);
B.belongsTo(A);
B.hasMany(C);
C.belongsTo(B);

This is what I'd like to be able to achieve with Sequelize:

SELECT *
FROM `A`AS `A`
LEFT OUTER JOIN `B` AS `B` ON `A`.`id` = `B`.`a_id`
LEFT OUTER JOIN `C` AS `B->C` ON `B`.`id` = `B->C`.`b_id`
    AND (`B`.`b_columnName` = `B->C`.`c_columnName`);

This is how I imagine this working: (instead it will create a raw query (2 raw queries, for A-B/C) with AND ( `C`.`columnName` = '$B.columnName$')) on the join (second arg is a string). Have tried sequelize.col, sequelize.where(sequelize.col..., etc..)

A.findOne({
    where: { id: myId },
    include: [{
        model: B,
        include: [{
            model: C,
            where: { $C.c_columnName$: $B.b_columnName$ }
        }]
    }]
});
sequelize.js orm sql javascript
1 Answers

Use the Op.col query operator to find columns that match other columns in your query. If you are only joining a single table you can pass an object instead of an array to make it more concise.

const Op = Sequelize.Op;

const result = await A.findOne({
  include: {
    model: B,
    include: {
      model: C,
      where: {
        c_columnName: {
          [Op.col]: 'B.b_columnName',
        },
      }
    },
  },
});
user_14271610
Related