在单个查询中阅读类别和文章数 [英] Reading categories and number of articles in a single query

问题描述

我在MongoDb中有一个数据库，其中包含两个集合：类别和文章。
我正在NodeJ上使用Mongoose连接到数据库并读取类别。我想计算类别的文章数，而无需发出额外的请求/查询，因此，如果可以在数据库级别解决此问题，那将是完美的选择。

I have a database in MongoDb that contains two collections: 'categories' and 'articles'. I'm using Mongoose on NodeJs to connect to the database and read the categories. I want to calculate the number of articles for a category without making an additional request/query, so it would be perfect if I could solve this at the database level.

An 类别集合中的项如下：

An item from the 'categories' collection looks like:

{
'_id' : ObjectId("..."),
'feed_id' : 1,
'name': 'Blog posts'
}

'articles'集合中的项目如下：

An item from the 'articles' collection looks like:

{
'_id' : ObjectId("..."),
'feed_id' : 1,
'title': 'Article title',
'published' : '12/09/2012',
...
}

因此类别和文章使用 feed_id字段。

so the categories and articles are linked using the 'feed_id' field.

我想导出所有类别以及相应数量的文章：

I would like to export all categories together with a corresponding number of articles:

{
'_id' : ObjectId("..."),
'feed_id' : 1,
'name': 'Blog posts',
'no_articles': 4
}

我不确定我应该这样做：

I'm not sure how exactly I should do this:

1）在类别集合中创建 no_articles字段吗？如果是，我希望对此进行更新

1) Create a 'no_articles' field in the categories collection? If yes, I would like this to be updated automatically when a document is inserted or deleted from the articles collection.

2）在阅读类别时将文章汇总为 no_articles？

我读了一些有关MapReduce和组的信息，但并不太了解是否可以将它们用于特定任务。

I read something about MapReduce and group, but didn't quite understand if it's possible to use them for this particular task.

推荐答案

这是传统关系数据库真正发挥作用的用例之一。

This is one of use cases where traditional relational databases really shine.

在mongodb中使用一个查询是不可能做到这一点的。您提到的 no_articles 字段是解决方法。此方法的通用名称（无论如何在Rails员工中）是： Counter Cache Column 。我对猫鼬不是很熟悉，所以我不知道它是否会为您维护该领域。 MongoDB本身当然不会这样做。但是，要自己维护它并不需要很多工作，只需要准确即可。

It is impossible to do that with one query in mongodb. The "no_articles field" you mentioned is the way to go. Common name (among Rails people, anyway) for this approach is: Counter Cache Column. I am not very familiar with Mongoose, so I don't know whether it will maintain that field for you or not. MongoDB itself certainly won't do it. But maintaining it yourself isn't a lot of work, you just need to be accurate.

我建议您在阅读类别时不要计算文章。这是 N + 1查询 问题和计数器缓存列可防止出现此问题。

I advise against counting articles when you read categories. This is a classic example of N+1 query problem and counter cache column is there to prevent it.

这篇关于在单个查询中阅读类别和文章数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！