postgresql Sequelize ORM 中连接表的条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36262912/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Where condition for joined table in Sequelize ORM
提问by fourslashw
I want to get query like this with sequelize ORM:
我想使用 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
问题是续集不允许我在 where 子句中引用“B”或“C”表。以下代码
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?
使用 sequelize 甚至可以进行第一次查询，还是我应该坚持使用原始查询？
回答by Jan Aagaard Meier
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
}]
});
