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.
@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!
@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
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.
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!
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!
awesome!!!
it`s very helpful,thank you very much!!!
Star
Discord
online
Get dedicated engineering support directly from the Payload team.