Django: delete auxiliary annotation from queryset

Question

I am building a complicated queryset over a complicated datamodel. The queryset must return a selection of model objects A with an annotation ann.

For computing ann, I need an auxiliary annotation aux, but no aux is allowed to exist in the final results of the queryset.

qs = A.objects.filter(...)  # complicated
qs = qs.annotate(aux=...)  # complicated
qs = qs.annotate(ann=Case(
    When(condition=Q(aux=0), 
         then Value('some')),
    When(condition=Q(aux_gt=0),
         then Value('other'))))

How to return qs with ann but without aux?

(Alternatively: If aux is a count and ann discriminates zero and non-zero aux, is there a better way to approach the whole problem?)

Cameron Lee · Accepted Answer · 2021-10-07 18:44:47Z

12

Django 3.2 introduced a feature to solve specifically this:

https://docs.djangoproject.com/en/3.2/ref/models/querysets/#alias

alias() works like annotate() except only for other queryset methods such as annotate(): it doesn't stay in the final output.

qs = A.objects.filter(...)
qs = qs.alias(aux=...)  # using alias here instead of annotate
qs = qs.annotate(ann=Case(
When(condition=Q(aux=0), 
     then Value('some')),
When(condition=Q(aux_gt=0),
     then Value('other'))))

answered Oct 7, 2021 at 18:44

Cameron Lee

1,09310 silver badges13 bronze badges

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

Comments

Lutz Prechelt · Accepted Answer · 2018-08-16 09:37:27Z

Ugly hack:

A simple, if highly unelegant method is to rename aux to be called ann as well. That way, the second annotation overwrites the first and the first will no longer exist in the output.

qs = A.objects.filter(...)  # complicated
qs = qs.annotate(ann=...)  # complicated
qs = qs.annotate(ann=Case(
    When(condition=Q(ann=0), 
         then Value('some')),
    When(condition=Q(ann_gt=0),
         then Value('other'))))

This appears to work. Tested with Django 1.11 on SQlite and PostgreSQL.

But there must be a better way, pleeeease.

why not use values? and how the extra field break your code?

imposeren · Accepted Answer · 2023-10-04 12:39:03Z

It's not documented, but there is QuerySet().query.annotations dictionary which you can manipulate to remove annotations from the results:

qs = A.objects.filter(...)  # complicated
qs = qs.annotate(aux=...)  # complicated
qs = qs.annotate(ann=Case(
    When(condition=Q(aux=0), 
         then Value('some')),
    When(condition=Q(aux_gt=0),
         then Value('other'))))

qs.query.annotations.pop('aux')

It works both for "auxilary annotations" that you mentioned and for filtering. E.g. you can remove all the annotations in the end if you just wanted to filter things on them:

qs = A.objects.filter(...)  # complicated
qs = qs.annotate(aux=...)  # complicated
qs = qs.annotate(ann=Case(
    When(condition=Q(aux=0), 
         then Value('some')),
    When(condition=Q(aux_gt=0),
         then Value('other'))))
qs = qs.filter(ann='some')
qs.query.annotations.pop('aux', None).pop('ann', None)

But if you can afford to use newer django (3.2+), then just use qs.alias() as suggested in another answer: https://stackoverflow.com/a/69486332/952437

Collectives™ on Stack Overflow

Django: delete auxiliary annotation from queryset

3 Answers 3

Comments

Ugly hack:

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Ugly hack:

1 Comment

Comments

Related