Search code examples
djangoperformancedjango-querysetdjango-filter

How to filter/annotate prefetched objects in django (more efficiently)?

I would like to filter queryset, with prefetch_related objects. This code works, but I would like to write it more efficiently. Do you have any idea ?

queryset = Song.objects.prefetch_related(
                Prefetch('harmonies', queryset=Harmony.objects.filter(someQuery)))

for s in queryset:
    if s.harmonies.count() > 0:
         songs.append(s.id)
queryset = queryset.filter(id__in=songs)

I tried something like this, but it didn't work.

queryset = queryset.annotate(h_count=Count('harmonies')).exclude(h_count=0)

Thank you , for your help.

Solution

  • You can work with an Exists subquery [Django-doc], so:

    from django.db.models import Exists, OuterRef

queryset = Song.objects.filter(
    Exists(Harmony.objects.filter(someQuery, song_id=OuterRef('pk')))
)

    Here the song_id=… is the ForeignKey from Harmony to song, so that might be slightly different.