Relation field count & ordering by relation count

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.

So what’s the best approach to be able to easily:

  1. get number of likes related to the Article
  2. sort articles by the number of likes

Thanks!

4 Likes

you can count the number of related entities like this:
image

but I don’t know how to sort :frowning:

1 Like

can u teach me how to do add this into my code?

Use this inside a controller or service

Is there a good solution for this? @KadlinoBIT, I’m just curious what you ended up doing.

Have you found a solution? i have a similar problem

1 Like

Hello,

Did you find any solution for the sort issue ?

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.

Here’s my knex query:

Hope this helps :slight_smile:

Here’s my strapi query afterwards:
image