4

I have two models in Django, one for Songs, one for Albums, an Album has many Songs. I am trying to filter Albums where Songs are valid. For example, at least one Song has to have an audio file in order for the Album to be returned by the filter. I am using Postgres.

I am trying to figure out how to do this logic via a Django QuerySet but i am not certain how to use where exists instead of exists.

The following is the Django orm statement i am trying to get to work:

valid_songs = Song.objects.filter(
    album=OuterRef('pk'),
    audio_file__isnull=False).only("album")

Album.objects.annotate(
    valid_song=Exists(valid_songs)).filter(
valid_song=True).query

This is the query that is generated:

SELECT "api_album"."id", 
       "api_album"."created_at", 
       "api_album"."updated_at", 
       "api_album"."title", 
       "api_album"."artwork_file_id", 
       "api_album"."user_id", 
       "api_album"."description", 
       "api_album"."tags", 
       "api_album"."genres", 
       EXISTS(SELECT U0."id", 
                     U0."album_id" 
              FROM   "api_song" U0 
              WHERE  ( U0."album_id" = ( "api_album"."id" ) 
                       AND U0."audio_file_id" IS NOT NULL )) AS "valid_song" 
FROM   "api_album" 
WHERE  EXISTS(SELECT U0."id", 
                     U0."album_id" 
              FROM   "api_song" U0 
              WHERE  ( U0."album_id" = ( "api_album"."id" ) 
                       AND U0."audio_file_id" IS NOT NULL )) = true 

This is the postgres query plan for the above query generated by Django's QuerySet:

Seq Scan on api_album  (cost=0.00..287.95 rows=60 width=641)
 Filter: (alternatives: SubPlan 3 or hashed SubPlan 4)
 SubPlan 3
   ->  Seq Scan on api_song u0_2  (cost=0.00..1.54 rows=1 width=0)
         Filter: ((audio_file_id IS NOT NULL) AND (album_id = api_album.id))
 SubPlan 4
   ->  Seq Scan on api_song u0_3  (cost=0.00..1.43 rows=10 width=4)
         Filter: (audio_file_id IS NOT NULL)
 SubPlan 1
   ->  Seq Scan on api_song u0  (cost=0.00..1.54 rows=1 width=0)
         Filter: ((audio_file_id IS NOT NULL) AND (album_id = api_album.id))
 SubPlan 2
   ->  Seq Scan on api_song u0_1  (cost=0.00..1.43 rows=10 width=4)
         Filter: (audio_file_id IS NOT NULL)
(14 rows)

However, there is much more efficient query for this

SELECT * 
FROM   "api_album" 
WHERE  EXISTS(SELECT U0."id", 
                     U0."album_id" 
              FROM   "api_song" U0 
              WHERE  ( U0."album_id" = ( "api_album"."id" ) 
                       AND U0."audio_file_id" IS NOT NULL )) 

Hash Semi Join  (cost=1.55..13.26 rows=10 width=640)
 Hash Cond: (api_album.id = u0.album_id)
 ->  Seq Scan on api_album  (cost=0.00..11.20 rows=120 width=640)
 ->  Hash  (cost=1.43..1.43 rows=10 width=4)
       ->  Seq Scan on api_song u0  (cost=0.00..1.43 rows=10 width=4)
             Filter: (audio_file_id IS NOT NULL)
(6 rows)

So my questions are as follows:

  1. What is the difference between where exists vs exists in this scenario and why aren't the same query plans created?
  2. How do I get the Django ORM to generate the more efficient query?

Edit: the django models are as follows:

  class Album(BaseModel):
    title = models.CharField(max_length=255, blank=False)
    artwork_file = models.ForeignKey(
        S3File, null=True, on_delete=models.CASCADE,
        related_name="album_artwork_file")
    user = models.ForeignKey(settings.AUTH_USER_MODEL,
                             related_name="albums",
                             on_delete=models.CASCADE)
    description = models.TextField(blank=True)
    tags = ArrayField(models.CharField(
        max_length=16), default=default_arr)
    genres = ArrayField(models.CharField(
        max_length=16), default=default_arr)



class Song(BaseModel):
    title = models.CharField(max_length=255, blank=False)
    album = models.ForeignKey(Album,
                              related_name="songs",
                              on_delete=models.CASCADE)
    audio_file = models.ForeignKey(
        S3File, null=True, on_delete=models.CASCADE,
        related_name="song_audio_file")

the following DOES not work because if you use a get() on this QuerySet it will throw an exception

Album.objects.filter(songs__audio_file__isnull=False).get(pk=1)
Album.MultipleObjectsReturned: get() returned more than one Album 

The query set is being used with DjangoRest ModelViewSet, where the queryset is used for crud operations, and passed to the Album Serializer. This requires get() to work and return a single value.

class AlbumViewSet(viewsets.ModelViewSet):

    serializer_class = AlbumSerializer

    def get_queryset(self): 

        valid_songs = Song.objects.filter(
            album=OuterRef('pk'),
            audio_file__isnull=False).only('album')

        # Slow query posted above
        return Album.objects.annotate(
            valid_song=Exists(valid_songs)
        ).filter(valid_song=True)

1 Answer 1

1

I'm not sure why you're doing either of these queries. Finding albums where at least one song has an audio file is expressed simply as:

Album.objects.filter(song__audio_file__isnull=False)
Sign up to request clarification or add additional context in comments.

6 Comments

That does not work, as the foreign key to album is on songs.
song is not a field on albums this will give an exception django.core.exceptions.FieldError: Cannot resolve keyword 'song' into field.
see edits, but i think you were trying to suggest Album.objects.filter(songs__audio_file__isnull=False) but this will result in exceptions when used with get()
Ah, you've set the related_name, so you need to use that: songs__audio_file__isnull.
Yes, so why are you using get? You want multiple albums. Use filter.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.