CODEWITHSUNDEEP
djangodjango-orm
Phil Gyford
Phil Gyford

Reputation: 14594

Removing a superfluous INNER JOIN from a Django query

I have models like this describing music albums, tracks on them, and individual listens to particular tracks:

class Album(models.Model):
    name = models.CharField(max_length=255)

class Track(models.Model):
    name = models.CharField(max_length=255)

class Listen(models.Model):
    track = models.ForeignKey('Track', related_name='listens', db_index=True)
    album = models.ForeignKey('Album', related_name='listens', db_index=True, blank=True)

To get all the Tracks on an Album, ordered by the number of times they've been heard, I can do:

Track.objects \
    .annotate( listen_count=models.Count('listens', distinct=True) ) \
    .filter(listens__album=1294) \
    .order_by('-listen_count')

This gets the results correctly but it seems inefficient. A simplified version of the resulting query is:

SELECT track.id,
       track.name,
       COUNT(DISTINCT listen.id) AS listen_count
FROM track
LEFT OUTER JOIN listen ON (track.id = listen.track_id)
INNER JOIN listen T3 ON (track.id = T3.track_id)
WHERE T3.album_id = 1294
GROUP BY track.id, track.name
ORDER BY listen_count DESC

I can get the same results by losing that INNER JOIN:

SELECT track.id,
       track.name,
       COUNT(DISTINCT listen.id) AS listen_count
FROM track
LEFT OUTER JOIN listen ON (track.id = listen.track_id)
WHERE listen.album_id = 1294
GROUP BY track.id, track.name
ORDER BY listen_count DESC

That uses one less index and is about half the speed. But I can't work out how to get the Django ORM to do this. (I'm currently using SQLite, if that makes a difference, although will be using Postgresql later.)

Upvotes: 2

Views: 488

Answers (1)

Todor
Todor

Reputation: 16020

If you .filter fist and .annotate after, your JOIN will be reused

>>> qs = (Track.objects
...     .filter(listens__album=1294)
...     .annotate(listen_count=models.Count('listens', distinct=True))
...     .order_by('-listen_count')
... )

will lead to 

SELECT 
    "music_track"."id",
    "music_track"."name",
    COUNT(DISTINCT "music_listen"."id") AS "listen_count" 
FROM "music_track" 
INNER JOIN "music_listen" ON ("music_track"."id" = "music_listen"."track_id") 
 WHERE "music_listen"."album_id" = 1294 
GROUP BY "music_track"."id", "music_track"."name" 
ORDER BY "listen_count" DESC

Upvotes: 4

Related Questions