1

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.

Bumaza
  • 103
  • 1
  • 5

1 Answers1

1

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.

Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
  • Thanks. It works, but I need to prefetch related objects too. It's necessary for my purpose. Do you have any idea how to efficiently merge this too filters ? – Bumaza Mar 12 '21 at 11:11
  • 1
    @Bumaza: you can still add a `Prefetch` object, simply add the `.prefetch_related` clause after the `.filter(...)`. – Willem Van Onsem Mar 12 '21 at 11:14