Node.js - Sequelize.js nested association check in outer where condition - undefined

Question

Implementation using Sequelize version: "^4.15.0"

I am trying to add a where condition from the 2 level down the associations included.

Model1.find({
    include: [
       {
           model: Model2,
           as: 'model2_alias',
           attributes: [
               'model2_id'
           ],
           include: [{
               model: Model3,
               as: 'model3_alias',
               attributes: [
                   'model3_id'
               ]
           }]
       }
    ],
    where: {
        model3_alias.id: { [Op.in]: [1, 2, 3] }
    }
});

I have tired the method:

where: {
    '$model2_alias.model3_alias.id$': { [Op.in]: [1, 2, 3] }
}

But is gives the error that model2_alias.model3_alias.id is undefined / not found.

The above does work when written as '$model2_alias.id$': { [Op.in]: [1, 2, 3] } so syntax must be write but how to go one more level down.

When executed the formed SQL statement in any MySQL management tool, gives proper result so the where conditions that I am trying to implement isn't wrong either.

Any ideas what am I missing here or this works for one level only. Then what to do for more levels?

Answer 1

You can add where at any level like this -

Model.find({
    where : { 
    // where 1
    },
    include : [{
        model : Model1,
        where : {
        // Where 2
        },
        include : [{
            model : Model2,
            where : {
            // Where 3
            }
        }]
    }]

});

Hope that helps!

Answer 2

Also, you can try

sequelize.where()

e.g.

sequelize.where(
      sequelize.literal('"model2_alias.model3_alias.id"'), { [Op.in]: [1, 2, 3] }
    )

About sub query, it means that you use limit/offset + (1:m relation or have left join). If you have 1:1 relation, just add required: true to include and you'll get inner join instead of left join and sequelize don't create sub query.