How to annotate a django queryset with a function of prefetched subquery

Question

I would like to have a field status on a queryset that is derived from a function of a prefetched set of feedback values.

Example of feedback:

feedback = [
    {'action': 0, 'user': 1},
    {'action': 1, 'user': 13},
]

If I were writing this on a Serializer I would do this:

    def get_status(self, obj):
        # Squash all feedback into single status value
        fb = obj.feedback.all()
        vals = [f.action for f in fb]
        if len(vals) == 0:
            return 0  # Unconfirmed
        if sum(vals) == 0:
            return 1 # Confirmed
        return 2  # Rejected

However I'd like to move this logic down into my view's queryset to enable ordering on the field.

queryset = Foo.objects\
        .prefetch_related('feedback')\
        .annotate(status="???")

I'd like to know what set of available query expressions could mimic the logic of the python function get_status above.

You can not call a function in a an .annotate(..) since the database layer does not know about functions. You can try to convert the above to a expression. You by the way do not need .prefetch_related(..) for that. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented May 17, 2020 at 19:15

willeM_ Van Onsem · Accepted Answer · 2020-05-17 19:19:09Z

2

You can not call a function in a an .annotate(..) since the database layer does not know about functions. You can try to convert the above to an expression. You do not need .prefetch_related(..) for that. Here you can make use of Case [Django-doc] and When [Django-doc]:

from django.db.models import Case, Count, IntegerField, Sum, Value, When

queryset = Foo.objects.annotate(
    nfeedback=Count('feedback'),
    sumfeedback=Sum('feedback__action')
).annotate(
    status=Case(
       When(nfeedback=0, then=Value(0)),
       When(sumfeedback=0, then=Value(1)),
       default=Value(2),
       output_field=IntegerField()
    )
)

answered May 17, 2020 at 19:19

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ian Danforth Over a year ago

This solved my issue. (Your accurate and speedy answer is much appreciated) For clarity, if I don't use prefetch_related(), will this not incur DB hits on a per row basis?

willeM_ Van Onsem Over a year ago

@IanDanforth: not for the annotation, since then Django will make a JOIN. The .select_related(..) and .prefetch_related(..) should be used when fetching related model data to the Python/Django layer. But this will make a JOIN and aggregates in the database, so it will result in one query.

Collectives™ on Stack Overflow

How to annotate a django queryset with a function of prefetched subquery

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related