CODEWITHSUNDEEP
pythonsqldjango
Sam Starling
Sam Starling

Reputation: 5378

Django: Grouping and ordering across foreign keys with conditions

I have some Django models that record people's listening habits (a bit like Last.fm), like so:

class Artist(models.Model):
    name = models.CharField()

class Song(models.Model):
    artist = models.ForeignKey(Artist)
    title = models.CharField()

class SongPlay(models.Model):
    song = models.ForeignKey(Song)
    user = models.ForeignKey(User)
    time = models.DateTimeField()

class User(models.Model):
    # doesn't really matter!

I'd like to have a user page where I can show the top songs that they've listened to in the past month. What's the best way to do this?

The best I've come up with so far is:

SongPlay.past_month
    .filter(user=user)
    .values('song__title', 'song__id', 'song__artist__name')
    .annotate(plays=Count('song'))
    .order_by('-plays')[:20]

Above, past_month is a manager that just filters plays from the last month. Assume that we've already got the correct user object to filter by as well.

I guess my two questions are:

Upvotes: 3

Views: 5317

Answers (2)

agf
agf

Reputation: 176780

You can use the same field in both values and annotate.

You have the primary key of the Song object (you could just use song instead of song__id), so use 

Song.objects.get(id=...)

For your second question, do a separate query with song__artist as the field in values and annotate:

from django.db.models import Count

SongPlay.past_month
    .filter(user=user)
    .values('song__artist')
    .annotate(plays=Count('song__artist'))
    .order_by('-plays')[:20]

Upvotes: 6

BenH
BenH

Reputation: 894

agf has already showed you how to group by song_artist. What I would do to get the actual Song object is store it in memcached, or if the method you are calling is rather simplistic make it a static method or a class method. You might could also initialize a Song object with the data from the query and not actually save it to get access to this method. Might help to know the details of the methods you want to call from the Song object.

Upvotes: 0

Related Questions