序列化查询包含与逻辑

Question

我有三个表：Article、Tag和ArticleTag。通过ArticleTag，一篇文章可以有多个标签，一个标签可以属于多篇文章。Tag有一个name属性。

我想查询所有有标签tag1和(tag2或tag3)和(tag4或tag5)的文章。以下查询不起作用。

Article.findAll({
    where: {},
    include: [
        { 
            model: Tag,
            where: {
                [Op.and]: [
                  { name: 'tag1' },
                  { name: { [Op.or]: ['tag2', 'tag3'] } },
                  { name: { [Op.or]: ['tag4', 'tag5'] } },
                ] 
            }
        }
    ]
})

Answer 1

我认为having和group_by会给你带来预期的结果(请务必阅读下面代码中的注释)：

Article.findAll({
    where: {},
    include: [
        { 
            model: Tag,
            where: {
                [Op.or]: [ { name: 'tag1' }, { name: 'tag2' }] // change from and to or 
            }
        }
    ]
    group : ['articles.id','tags.id'] , // might need to change table name as per yours
    having : {
        db.sequelize.where(db.sequelize.fn('count', db.sequelize.fn('DISTINCT', db.sequelize.col('articles.id'))),2)                    
    }
});

对于更新的问题，只需在以下位置更改或阻止：

[Op.and]: [
    { name: 'tag1' },
    { name: { [Op.or]: ['tag2', 'tag3'] } },
    { name: { [Op.or]: ['tag4', 'tag5'] } },
]