首页 文章

Sequelize ORM中连接表的条件

提问于
浏览 次
23

我希望通过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')

问题是sequelise不让我在where子句中引用“B”或“C”表 . 以下代码

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

这是完全不同的查询和结果

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

是第一次查询甚至可能与sequelize，或者我应该坚持原始查询？

javascript sql postgresql orm sequelize.js

2 回答

  • -2

    在 $$ 中包装引用连接表的列

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

    }, {
        model: C,
        required: false
    }]
});
    回复于
  • 39

    我'm running into a similar issue as xb1tz and posted that here. But apparently it'没用 . 所以我发布了一个新问题：Sequelize Top level where with eagerly loaded models creates sub query

    回复于

相关问题