Query groupBy and count by populated field

Questions and Answers Database-SQL
1
System Information
  • Strapi Version: 4.3.4
  • Operating System: Windows
  • Database: postgres
  • Node Version: 15.8.0
  • NPM Version: 7.5.1
  • Yarn Version: 1.22.19

I have a content type called “Event” and other “City”, I created a relation that Event has one City. And now I’m trying to know, cities that have events.

To do that I’m creating a new endpoint called /cities/with-events and I would like to return only cities, that have events, and desiredly the number of events for each city.

But, how can I do that? In other system than strapi. I think a solution is to query events doing group by city, and also counting each city.

How can I do that in strapi?

1 Like
2

/api/cities?populate[events][count]=true → Something like this would get all cities and the count of events per city

1 Like
3

Hello rocketscience,

Thank you for sharing out the solution of this famous question!!

This is working good, and its returning events count the following way :

"events": {
     "data": {
            "attributes": {
                  "count": 7
             }
      }
}

My question is how could we sort by the count value ?

Thank you again for your share!

4

Would this work?

/api/cities?populate[events][count]=true&sort=events.count:asc or desc for descending

5

Hello back @rocketscience

Thank you for your feedback, I’ve alreadt tried this and did not work !