V4 entityservice: query to select highest value per category

Questions and Answers Strapi Backend
1

Hi Sifus,

I am having a hard time with my entityservice query. My Strapi manages game scores and I want to extract the highscore list. I have no issue to query all scores ever saved but I only want the maximum value for each user.

Assume following dataset :

  • Name: Ben, Score: 10
  • Name: Ben, Score: 15
  • Name: Christian, Score 3
  • Name: Adam, Score: 3
  • Name: Ben, Score: 13
  • Name: Adam, Score 18

Above list is managed to pull with: (disclaimer I manage more than one game, so additional there is a game ID I use to filter my results, don’t worry about that: it works great :slight_smile: )

const ranking = await strapi.entityService.findMany('api::score.score', {
	fields: ['score'],
	populate: { game: {fields: ['id','name']}, user: {fields: ['id','username']} },
	filters: {
		game: details.game.id,
		}
	});

My expected outcome however should be just:

  1. Name: Adam, Score 18
  2. Name: Ben, Score 15
  3. Name: Christian, Score 3

So instead of getting all the scores of Adam, Ben and Christian I only want their highest ever score (each).
If I sort the query I still need to pull all of them, just to eventually reach Christians top score.
I couldn’t find any such filter to only limit it to the max of each name.
Only way I can think of to pull all results and check the array in JS. However since I expect a lot of player and scores I would much prefer to only pull one score for each user (and even paginate that, but that is a topic for another day)

Some pointer would me much appreciated. I’m running out of ideas what keyword I should google or lookup in the strapi docu…

System Information
  • Strapi Version: 4.10.5
  • Operating System: linux (dockerized)
  • Database: mysql
  • Node Version: V16.20.0