Sequelize ORM中联接表的Where条件

Where condition for joined table in Sequelize ORM

本文关键字：Where 条件 ORM Sequelize      更新时间：2023-09-26

我想用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"表。以下代码

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，或者我应该只使用原始查询？

换行引用$$ 中联接表的列

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

在include中添加where条件和join。

    {
       model: C,
       where: {
        id: 1
       }
   }
相关文章：