CODEWITHSUNDEEP
pythondjangodjango-querysetdistinct
aakash singh
aakash singh

Reputation: 305

Django get distinct results from queryset based on value

Here, I have my query result 

<QuerySet [{'user__first_name': 'aakash', 'user__id': 1, 'games_played': 1}, {'user__first_name': 'prakash', 'user__id': 2, 'games_played': 2}, {'user__first_name': 'prakash', 'user__id': 2, 'games_played': 2}]>

and my query is,

Games.objects.filter(Match__player__id=1).values('user__first_name', 'user__id').annotate(games_played=Count(Case(When(Q(status=Games.COMPLETE), then='id'))))

So here I want user prakash or user__id=2 to come once.

Upvotes: 1

Views: 175

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476624

You need to add .order_by at the end to force the queryset to "fold":

Games.objects.filter(
    Match__player__id=1
).values(
    'user__first_name', 'user__id', 'accounts__id'
).annotate(
    games_played=Count(Case(When(Q(status=Games.COMPLETE), then='id')))
).order_by('user__first_name', 'user__id', 'accounts__id')

Note however that here the JOINs will act as a "multiplier": you will count the number of ids times the number of matches where the player has id=1.

You probably want add distinct=True to avoid that in the Count(..):

Games.objects.filter(
    Match__player__id=1
).values(
    'user__first_name', 'user__id', 'accounts__id'
).annotate(
    games_played=Count(Case(When(Q(status=Games.COMPLETE), then='id')), distinct=True)
).order_by('user__first_name', 'user__id', 'accounts__id')

Upvotes: 2

Related Questions