CODEWITHSUNDEEP
djangodjango-models
Edwin Harly
Edwin Harly

Reputation: 439

How do I query count of 2 fields occurences in Django?

I've got a PlayTrack model like this, each time a user plays a track, it means 1 record in PlayTrack

class PlayTrack(models.Model):
    track = models.ForeignKey(Track, on_delete=models.CASCADE)
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    timestamp = models.DateTimeField(auto_now_add=True)
    token = models.CharField(max_length=255, default='default_token')

So, how do I query the play count of each user ?

I've tried PlayTrack.objects.annotate(Count('user'), Count('track')).values('user', 'track__count'), but it gives me something like 

<QuerySet [{'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, {'user': 2, 'track__count': 1}, '...(remaining elements truncated)...']>

What I need is something like

{'user': 2, 'track__count': 3},{'user': 3, 'track__count': 5},{'user': 4, 'track__count': 10},

Upvotes: 1

Views: 35

Answers (1)

JPG
JPG

Reputation: 88499

Try this,

from django.db.models import Count

PlayTrack.objects.values('user').annotate(count=Count('id'))


This is similar to SELECT user,COUNT(id) from PlayTrack GROUP BY user SQL query.By this query you'll get all PlayTrack information grouped by user

-------------------------------------------------------------------------------------------------------------------------------------
I have a second thought, track__count is taking the count of track. So I assume you need to get the result as SELECT user,COUNT(track) from PlayTrack GROUP BY user, which could possible by,

from django.db.models import Count

PlayTrack.objects.values('user').annotate(count=Count('track'))

Upvotes: 2

Related Questions