How to implementing a Query for User Article 'Essence' Counts and Sorting

[yooyea]

Overview
I am currently working on implementing a feature for my application that would allow me to query the number of times a user's articles have been marked as 'essence' and sort the results in descending order based on this count. I am seeking guidance or best practices to achieve this functionality within my application.

Background
In my application, users have the ability to publish articles that can be marked as 'essence' by other users or a moderation system. I now require a report or leaderboard that displays which users have the most 'essence' marks on their articles. This will help us identify creators of high-quality content and provide them with the recognition they deserve.

Desired Solution
I am hoping to receive an example or guidance on how to achieve this functionality using PocketBase. Specifically, I need assistance with the following:

Query Construction: How can I construct a query to tally the number of 'essence' marks for articles by each user?
Sorting: How can I ensure that the query results are sorted by the number of 'essence' marks in descending order?

Methods Tried
I have reviewed the documentation for PocketBase but have not found direct guidance on this matter.

[yooyea]

#4619 I found a question similar for statistics, but it did not receive the expected answer

[ganigeorgiev]

There is no universal solution or "direct guidance" as it would depends on your actual data structure and destired access patterns.

I'm not entire sure that I understand what "essense" means and how it is used in the above context, but if we assume that it serves the same purpose as "likes", then one possible approach could be (I haven't tested it, treat it just as suggestion/pseudo-code):

1. Create a new articlesEssences/articlesLikes collection that will contain relation with the user who marked the article as "essense"/"liked". The collection shema could be:

   • "user" - single nonempty relation field to the "users" collection
   • "article" - single nonempty relation field to the "articles" collection
   • add Unique cobined indexes for the "user-article" pair from the "Unique constraints and indexes" section to prevent one user to mark the same article as "essense"/"liked" twice.

2. Create a View collection (ex. articlesStats) that will perform the count aggregation over the "articleEssenses"/"articleLikes" collection. One possible query could be:

   -- articlesStats view query
   SELECT 
       a.id,
       a.id as article, -- optional so that we can have a relation field to the "articles" collection 
       COUNT("*") as totalEssences
   FROM articles a
   LEFT JOIN articlesEssences ae ON ae.article = a.id
   GROUP BY a.id
   ORDER BY totalEssences DESC -- default sort

Then you can query the above articlesStats like any other collection:

const result = await pb.collection("articlesStats").getFullList({
    expand: "article", // optional if you want the "articles" record to be included in the json
})

// fetch the stats only for a specific article
const result = await pb.collection("articlesStats").getOne("ARTICLE_ID")

If you need further help, please provide more details about your actual collections structure (you can export your collections configuration from the Admin UI > Settings > Export collections) and the desired behavior (sample data output can also help).

[yooyea]

Respected Sir, you've already explained it very clearly. Your advice has helped me again. From your interpretation, I have understood what to do. Thank you for your timely response.