Sequelize:多个where子句

2022-01-19 00:00:00 node.js express mysql sequelize.js

我有以下表格:

文章 - 用户 - 标签 - 关注者 - 订阅者

Article - User - Tag - Followers - Suscribes

文章属于用户(fk:文章表中的userId)

Article belongs to User (fk: userId in Article table)

文章可以有很多标签.这是生成的tagarticle表:

Article can have many tag. Here is the generated tagarticle table:

这是关注者表:

还有 Suscribes 表:

And the Suscribes table:

一个用户可以关注多个用户并订阅一个国家(payId)、标签或文章(用于通知).

A user can follow many users and suscribe to a country(payId), a tag or an article(for notifications).

如何查询关注用户的所有文章以及特定用户的订阅国家或标签?

How to query all articles of followed users and suscribed country or tag for a specific user?

推荐答案

我假设你问的是 Sequelize 的查询方式.我不确定我是否正确理解了您的问题.您正在寻找两个查询:

I assume that you ask about Sequelize way of doing the query. I am not sure if I understand your question correctly. You are looking for two queries:

  • 查询关注用户的所有文章，
  • 查询特定用户的订阅国家/标签/文章，

让我从模型之间的关联开始.

Let me start with the associations made between the models.

// in User model definition
User.belongsToMany(User, { as: 'Followers', through: 'Followers', foreignKey: 'userId', otherKey: 'followId' });
User.hasMany(Subscribe, { foreignKey: 'userId' });
User.hasMany(Article, { foreignKey: 'userId' });

通过使用上述关联，我们现在可以查询所有关注用户的文章

With use of above association we are now able to query all articles of followed users

models.User.findByPrimary(1, {
    include: [
        {
            model: models.User,
            as: 'Followers',
            include: [ models.Article ]
        }
    ]
}).then(function(user){
    // here you have user with his followers and their articles
});

上面的查询会产生类似的结果

Above query would generate result similar to

{
    id: 1,
    Followers: [
        {
            id: 4,
            Articles: [
                {
                    id: 1,
                    title: 'article title' // some example field of Article model
                }
            ]
        }
    ]
}

如果要查询特定用户订阅的国家/标签/文章，则必须在Subscribe模型中进行另一个关联

If you want to query country/tag/article subscribed by specific user, you would have to make another associations in Subscribe model

// in Subscribe model definition
Subscribe.belongsTo(Tag, { foreignKey: 'tagId' });
Subscribe.belongsTo(Article, { foreignKey: 'articleId' });
Subscribe.belongsTo(Country, { foreignKey: 'payId' });

现在我们拥有执行您要求的第二个查询所需的所有关联

Now we have all the associations required to perform the second query you asked for

models.User.findByPrimary(1, {
    include: [
        {
            model: models.Subscribe,
            include: [ models.Tag, models.Country, models.Article ]
        }
    ]
}).then(function(user){
    // here you get user with his subscriptions
});

在本例中，您通过 user.Subscribes 获得所有订阅的用户，该用户将具有嵌套属性 Tag、Country 和 <代码>文章.如果用户订阅了 Tag，则 Country 和 Article 在这种情况下都将为 NULL.

In this example you get user with all his subscriptions accessed via user.Subscribes, which will have nested attributes Tag, Country and Article. If user subscribed to Tag, both Country and Article would be NULL in this case.

相关文章