Where condition for joined table in Sequelize ORM
32,477
Solution 1
Wrap the columns which reference joined tables in $$
A.findAll({
where: {
$or: [
{'$B.userId$' : 100},
{'$C.userId$' : 100}
]
},
include: [{
model: B,
required: false
}, {
model: C,
required: false
}]
});
Solution 2
Add the where condition in the include, along with join.
{
model: C,
where: {
id: 1
}
}
Author by
fourslashw
Updated on September 18, 2020Comments
-
fourslashw over 3 years
I want to get query like this with sequelize ORM:
SELECT "A".*, FROM "A" LEFT OUTER JOIN "B" ON "A"."bId" = "B"."id" LEFT OUTER JOIN "C" ON "A"."cId" = "C"."id" WHERE ("B"."userId" = '100' OR "C"."userId" = '100')
The problem is that sequelise not letting me to reference "B" or "C" table in where clause. Following code
A.findAll({ include: [{ model: B, where: { userId: 100 }, required: false }, { model: C, where: { userId: 100 }, required: false }] ]
gives me
SELECT "A".*, FROM "A" LEFT OUTER JOIN "B" ON "A"."bId" = "B"."id" AND "B"."userId" = 100 LEFT OUTER JOIN "C" ON "A"."cId" = "C"."id" AND "C"."userId" = 100
which is completely different query, and result of
A.findAll({ where: { $or: [ {'"B"."userId"' : 100}, {'"C"."userId"' : 100} ] }, include: [{ model: B, required: false }, { model: C, required: false }] ]
is no even a valid query:
SELECT "A".*, FROM "A" LEFT OUTER JOIN "B" ON "A"."bId" = "B"."id" LEFT OUTER JOIN "C" ON "A"."cId" = "C"."id" WHERE ("A"."B.userId" = '100' OR "A"."C.userId" = '100')
Is first query even possible with sequelize, or I should just stick to raw queries?