Is it possible to count posts/pages by group by a category field?

[Stupidism]

I have a tab switch to show two different categories of articles with the total num shown in tab title.

https://getjerry.com/data-and-studies

Now I'm using two queries to fetch these two counts separately.

countStudies: Articles(
    where: {
      category: { equals: "studies" }
      slug: { not_in: $excludeSlugs }
      noCard: { not_equals: true }
      _status: $_status
    }
    page: $page
    limit: $limit
    sort: "-publishDate"
  ) {
    totalDocs
  }
  countTrendsReports: Articles(
    where: {
      category: { equals: "trends-reports" }
      slug: { not_in: $excludeSlugs }
      noCard: { not_equals: true }
      _status: $_status
    }
    page: $page
    limit: $limit
    sort: "-publishDate"
  ) {
    totalDocs
  }

It works, but I'm wondering if there's a more elegant(one query) or extensible(if I need all categories) way to do this.

[ChrisGV04]

I'm having the exact same situation. The only thing I could think of was if there was some sort of bi-directional field in the relationship between the collections. For example: In the Articles collection there's a category field and on the Category collection there's a articles field with hasMany: true.

Other CMS like Strapi and Directus handle this with the "one-to-one", "one-to-many" and "many-to-many" technique, which automatically maintains both fields I mentioned before as a nice relationship. However, I imagine that it's easier on those CMS because they use SQL databases but Payload uses MongoDB that's NoSQL.

[jacobsfletch]

@Stupidism there's currently no API for this, however, you could wire up your own custom endpoint to consolidate multiple queries. It would look like this: your front-end makes a single request to your Payload server, it uses the Local API to query "studies" and "trend reports" similarly to your example above, then it returns both results back to you in a single response.

@ChrisGV04 I think what you're talking about to is slightly different, although a great idea. This is already some discussion around bidirectional/inversed relationships, check it out!

[ChrisGV04]

@jacobsfletch That's right. The solution I proposed is the way I have seen that some other CMS manage it, but I understand it's a different topic.

However your proposed solution is a good option. I personally use MongoDB aggregations in a custom route to perform these counts.

Great work on Payload btw! My favorite CMS by far

[clhome]

I have a tab switch to show two different categories of articles with the total num shown in tab title.我有一个选项卡开关来显示两个不同类别的文章，总数量显示在选项卡标题中。

https://getjerry.com/data-and-studies

Now I'm using two queries to fetch these two counts separately.现在我使用两个查询来分别获取这两个计数。

countStudies: Articles(
    where: {
      category: { equals: "studies" }
      slug: { not_in: $excludeSlugs }
      noCard: { not_equals: true }
      _status: $_status
    }
    page: $page
    limit: $limit
    sort: "-publishDate"
  ) {
    totalDocs
  }
  countTrendsReports: Articles(
    where: {
      category: { equals: "trends-reports" }
      slug: { not_in: $excludeSlugs }
      noCard: { not_equals: true }
      _status: $_status
    }
    page: $page
    limit: $limit
    sort: "-publishDate"
  ) {
    totalDocs
  }

It works, but I'm wondering if there's a more elegant(one query) or extensible(if I need all categories) way to do this.它有效，但我想知道是否有更优雅（一个查询）或可扩展（如果我需要所有类别）的方式来执行此操作。

hi,I have encountered the same problem. Can you provide a complete example code?thank you！

[ChrisGV04]

Hi! @clhome
In my case, I created a custom Express route inside my server.ts file that gets all of the categories that have posts and a count of the posts that belong to them. I used the MongoDB aggregation pipeline to do all of the processing for me:

// Custom route to get a list of the used categories' ID and a count of their posts.
// If no count is returned, assume that the count is 0.
app.get('/api/categories-count', async (req, res) => {
  try {
    // Get the Mongoose model for the Posts collection created by Payload CMS
    const postModel = payload.collections[PostCollection.slug].Model;

    // Perform an aggregation to fetch an array with the ID of the
    // used categories and a count of the posts in that category
    const aggregation = await postModel.aggregate([
      { $project: { _id: 1, categories: 1 } }, // Only pick the _id and categories fields from the posts
      { $unwind: '$categories' }, // Separate all of the used categories into separate objects to perform count
      { $group: { _id: '$categories', count: { $sum: 1 } } }, // Group and count by category
    ]);

    // 👆🏼 That returns an array like this:
    // [
    //   { _id: 'xxxxxxxxxxxxxxxxxx', count: 12 },
    //   { _id: 'yyyyyyyyyyyyyyyyyy', count: 15 },
    // ];

    return res.send({
      categories: aggregation.map((c) => ({ id: c._id, count: c.count })), // Replace the _id for id
    });
  } catch (error) {}

  res.sendStatus(400);
});

If you're not familiar with the aggregate pipeline, I recommend you take a look to the official MongoDB docs.

That way you can perform a GET request on http://localhost:3000/api/categories-count and it returns an array with this format:

[
  { "id": "xxxxxxxxxx", "count": 10 },
  { "id": "yyyyyyyyyy", "count": 8 },
  ...
]

I hope this is helpful to you!

[clhome]

awesome!!!
it`s very helpful,thank you very much!!!