CODEWITHSUNDEEP
djangodjango-queryset
K.H.
K.H.

Reputation: 1462

Django custom prefetch and conditional annotate

I am using custom prefetch object to get only some related objects, ex:

unreleased_prefetch = Prefetch("chants", Chant.objects.with_audio())
teams = Team.objects.public().prefetch_related(unreleased_prefetch)

This works well, but I also want to know count of these objects and filter by these. I am happy that I can at the moment use queryset as parameter to Prefetch object (as I heavily use custom QuerySets/Managers).

Is there way how I can reuse this query, that I pass to Prefetch object same way with conditional annotate?

So far my conditional annotate is quite ugly and looks like this (it does same thing as my original chant with_audio custom query/filter):

.annotate(
    unreleased_count=Count(Case(
        When(chants__has_audio_versions=True, chants__has_audio=True, chants__flag_reject=False,
             chants__active=False, then=1),
        output_field=IntegerField()))
).filter(unreleased_count__gt=0)

It works, but is quite ugly and has duplicated logic. Is there way to pass queryset to When same way I can pass it to prefetch to avoid duplications?

Upvotes: 0

Views: 1796

Answers (1)

BitParser
BitParser

Reputation: 3968

Not saying this is the best practice or anything, but wanted to provide a potential way of dealing with such a situation.

Let's say you have a ChantQuerySet class:

class ChantQuerySet(models.QuerySet):
    def with_audio(self):
        return self.filter(chants__has_audio_versions=True, chants__has_audio=True,
                           chants__flag_reject=False, chants__active=False)

Which you use as a manager doing something like below, probably:

class Chant(models.Model):
    # ...
    objects = ChantQuerySet.as_manager()

I would suggest storing the filter in the QuerySet:

from django.db.models import Q

class ChantQuerySet(models.QuerySet):
    @property
    def with_audio_filter(self):
        return Q(chants__has_audio_versions=True, chants__has_audio=True,
                 chants__flag_reject=False, chants__active=False)

    def with_audio(self):
        return self.filter(self.with_audio_filter)

This gives you the ability to do this:

Chant.objects.annotate(
    unreleased_count=Count(Case(
        When(ChantQuerySet.with_audio_filter, then=1),
        output_field=IntegerField()))
).filter(unreleased_count__gt=0)

Now you are able to change the filter only in one place, should you need to do so, without having to change it everywhere. To me it makes sense to store this filter in the QuerySet and personally I see nothing wrong with that, but that's just me.

One thing that I'd change though, is to either make the with_audio_filter property cached, or store it in a field in the constructor when initializing ChantQuerySet.

Upvotes: 1

Related Questions