Getting data from three models

I have models:

class Game(models.Model):
    name = models.TextField(max_length=150)

class Team(models.Model):
    name = models.TextField(max_length=150)
    game = models.ForeignKey('Game', verbose_name="Teams")

class Player(models.Model):
    name = models.TextField(max_length=150)
    score = models.PositiveIntegerField(default=0)
    team = models.ForeignKey('Team', verbose_name="Players")

How can I get the 3 best players (the highest score) per game from the database?

Do you have any background doing this on SQL?
You’re probably going to need to take a look into aggregation.

In addition to @leandrodesouzadev’s answer, hopefully you’re using PostgreSQL as your database. If so, also see ArrayAgg and JSONBAgg. You may also need to make yourself familiar with subqueries.

Try to take a look at this Thread .
I have an idea for your issue.
If I understood you well, I suggest using .values() or values_list()

from django.db.models import Count
players_score = Player.objects.values('score').annotate(Count('score')).filter(team__game__name=(an_instance_of_game_model)).order_by('-score') 
score_list = [obj['score'] for obj in players_score]
players_score =
Player.objects.values_list('score', flat=True).annotate(Count('score')).filter(team__game__name=(an_instance_of_game_model)).order_by('-score') 
score_list = [obj for obj in players_score]

By the above query you can get an arranged list of all the scores without duplicated score (I think this is not accurate if you want to know all players that have the same score) (i.e if you have more than one player has the same score) , but if you want to know only the highest score you can use it (score_list[0], score_list[1] and score_list[2])
**Hint: this is an idea to solve your problem, only you that have the ability to take the right decision to use it or not , So test it and by trying and failing you will get your purpose .
Hope the idea help you.

1 Like