0

I have two models Player and Game

class Player(models.Model):
    userId = models.CharField(max_length = 150, primary_key=True)
    username = models.CharField(max_length = 250)
    email = models.CharField(max_length = 150)
    playedGames = models.IntegerField()

class Game(models.Model):
    player = models.ForeignKey(Player, on_delete=models.CASCADE)
    date = models.DateTimeField()
    award = models.IntegerField()

And I make request to get all players with their maximum awards.

wins = Player.objects.annotate(maxAward=Max('game__award')).values().order_by('-maxAward')

But also I want to show 'date' for all selected games (where award is maximum). How should I change my request?

Ahsar Gabaraev
  • 25
  • 5

1 Answers1

1

You could specify necessary fields through child models:

wins = Player.objects.annotate(
    max_award=Max('game__award')
).filter(
    game__award=F('max_award')
).annotate(
    date=F('game__date')
    award=F('game__award')
).values(
    'userId',
    'date',
    'award'
).order_by('-max_award')
funnydman
  • 9,083
  • 4
  • 40
  • 55
  • This request return my all game objects with specified fields. I have 2 players and 5 games, but after specifying 'userId', 'game__date', 'game__award' I get 5 records – Ahsar Gabaraev Jan 24 '20 at 13:29
  • Sorry, I know it's very simple question, but I not found answer. How can I specify fields name ('award' instead of 'game__award' and 'date' instead of 'game__date')? – Ahsar Gabaraev Jan 28 '20 at 07:10
  • @AhsarGabaraev as I understand you want to get values in the resulting dict as `{'date': '2020-10-10'}` instead of `{'game__date': '2020-10-10'}`? – funnydman Jan 28 '20 at 07:15
  • funnydman, There was detected one bug that I can't fix. If a user have 2 or more of the same max Awards with different dates, then I get the same number of records (one for each date). Can you help me to fix this error? I'd like to group them by username, to get just one record for each user with max award (with last date, for ex.) – Ahsar Gabaraev May 29 '20 at 12:53