Hi,
scenario - there is an Article content type. Each article has a relation to the Likes table (1-N - Article belongs to many Likes). When I query for articles, I want to get the number of likes for each article. Furthermore, I want to be able to sort by the count of likes (thus querying “The most liked…”).
This is a super common scenario I guess, but I don’t see an easy way in Strapi to achieve this. Even in v4, there’s no count metadata for relation fields.
I did not find any built-in solution, but if you are willing to dive in deeper here is my solution with a few workarounds:
I used knex, which helps you built your own database queries. You can access it via strapi.db.connection. With this I queried the entity ids in the correct order based on their score (in my case it was comment count and votes added together). Then used these ids with the built-in strapi query, so I don’t have to populate everything on my own with knex.
And as a final step I had to order the final result coming from the strapi query manually.