在MongoDB中查询和排序多对多关系 [英] Query and Sort in MongoDB for a many-to-many relationship

问题描述

假设我有用户，帖子，喜欢。

我的目标是能够在MongoDB中设计一个数据库结构，以便我可以快速查询对于用户喜欢的所有帖子，并按以下列出的多种方式对它们进行排序/过滤（不同时 - 考虑可以更改搜索结果排序顺序的下拉菜单）

My goal is to be able to design a db structure in MongoDB such that I can quickly query for all the posts a user has liked AND sort/filter them in the multiple ways listed below (not at the same time - think a dropdown that lets you change the sort order of your search results)

1. 喜欢帖子的顺序


2. 通过各种帖子过滤和排序 - 例如标题，发布回复数，帖子创建时间等。

1. Order in which posts were liked
2. Filter and order by various post attributes - such as title, number of post responses, when the post was created, etc

假设帖子数量为100,000

Suppose the number of posts is in the order of 100,000 and each post will have on the order of 100-1000 likes

我想到的可能的解决方案：

Possible solutions I've thought of:

1） likes 嵌入在 posts 中。

这样可以轻松处理＃2，因为您只需要一个 likes.user_id 其他帖子属性你需要。这也很快，因为你只需要运行一个查询。

This allows #2 to be dealt with easily because you just have an index over likes.user_id and over whatever other post attributes you need. This is also fast, because you only need to run one query.

但是，这使得当用户喜欢某事（AFAIK）时不可能进行排序。

However, this makes it impossible to sort by when a user liked something (AFAIK).

2） likes 是一个单独的集合，属性 post_id code> account_id 。

2) likes are a separate collection with attributes post_id, account_id.

这样可以轻松处理＃1，因为只需按_id进行排序。但是，除非您复制&缓存 post 属性到 like 文档中，变得不可能处理＃2。这是可能的，但真的不理想。此外，查询速度较慢。您需要运行两个查询 - 一个查询如集合，然后使用$ in查询 post 查询： [post_ids]。

This allows #1 to be dealt with easily since you can just sort by _id. However, unless you duplicate & cache post attributes into the like document, it becomes impossible to handle #2. This is possible but really not ideal. Additionally, this is slower to query. You'd need to run two queries - one to query the like collection, then a post query using $in: [post_ids].

有没有其他解决方案/设计我应该考虑？我在这些提议的解决方案中缺少什么？

Are there any other solutions/designs I should consider? Am I missing anything in these proposed solutions?

推荐答案

我会使用＃2的非规格化版本。有像文档：

I would use a denormalized version of #2. Have a like document:

{
    "_id" : ObjectId(...),
    "account_id" : 1234,
    "post_id" : 4321,
    "ts" : ISODate(...),
    // additional info about post needed for basic display
    "post_title" : "The 10 Worst-Kept Secrets of Cheesemongers"
    // etc.
}

使用索引 {account_id：1，ts：1} 有效地找到像为特定用户按照时间排序的文档。

With an index on { "account_id" : 1, "ts" : 1 }, you can efficiently find like documents for a specific user ordered by like time.

db.likes.find({ "account_id" : 1234 }).sort({ "ts" : -1 })

如果你把这篇文章的基本信息放到 c $ c>文档，您不需要检索帖子文档，例如，用户点击链接以显示整个帖子。

If you put the basic info about the post into the like document, you don't need to retrieve the post document until, say, the user clicks on a link to be shown the entire post.

如果一些喜欢 -embedded的信息更改，它需要在每个如中更改。这可能是什么也可能是笨重的，这取决于你选择嵌入和帖子被修改后，他们有很多喜欢的频率。

The tradeoff is that, if some like-embedded information about a post changes, it needs to be changed in every like. This could be nothing or it could be cumbersome, depending on what you choose to embed and how often posts are modified after they have a lot of likes.

这篇关于在MongoDB中查询和排序多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！