Sequelize ORM中联接表的条件 [英] Where condition for joined table in Sequelize ORM

问题描述

我想通过sequelize ORM获得这样的查询:

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')

问题是后遗症不允许我在where子句中引用"B"或"C"表.以下代码

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
    }]
] 

给我

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
    }]
] 

甚至不是有效的查询:

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?

推荐答案

在$$

A.findAll({
    where: {
        $or: [
            {'$B.userId$' : 100},
            {'$C.userId$' : 100}
        ]
    },
    include: [{
        model: B,
        required: false

    }, {
        model: C,
        required: false
    }]
}); 

这篇关于Sequelize ORM中联接表的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！