CODEWITHSUNDEEP
djangoorm
Jess
Jess

Reputation: 429

How to update a queryset that has been annotated?

I have the following models:

class Work(models.Model):
    visible = models.BooleanField(default=False)

class Book(models.Model):
    work = models.ForeignKey('Work')

I am attempting to update some rows like so:

qs=Work.objects.all()
qs.annotate(Count('book')).filter(Q(book__count__gt=1)).update(visible=False)

However, this is giving an error:

DatabaseError: subquery has too many columns LINE 1: ...SET "visible" = false WHERE "app_work"."id" IN (SELECT...

If I remove the update clause, the query runs with no problems and returns what I am expecting.

It looks like this error happens for queries with an annotate followed by an update. Is there some other way to write this?

Upvotes: 13

Views: 5087

Answers (4)

söze
söze

Reputation: 570

To add to Oli's answer: If you need your annotations for the update then do the filters first and store the result in a variable and then call filter with no arguments on that queryset to access the update function like so:

q = X.objects.filter(annotated_val=5, annotated_name='Nima')
q.query.annotations.clear()
q.filter().update(field=900)

Upvotes: 3

Oli
Oli

Reputation: 239978

You can also clear the annotations off a queryset quite simply:

qs.query.annotations.clear()
qs.update(..)

And this means you're only firing off one query, not one into another, but don't use this if your query relies on an annotation to filter. This is great for stripping out database-generated concatenations, and the utility rubbish that I occasionally add into model's default queries... but the example in the question is a perfect example of where this would not work.

Upvotes: 7

Fraser Harris
Fraser Harris

Reputation: 1952

I've duplicated this issue & believe its a bug with the Django ORM. @acjay answer is a good workaround. Bug report: https://code.djangoproject.com/ticket/25171

Fix released in Django 2 alpha: https://code.djangoproject.com/ticket/19513

Upvotes: 1

acjay
acjay

Reputation: 36641

Without making a toy database to be able to duplicate your issue and try out solutions, I can at least suggest the approach in Django: Getting complement of queryset as one possible approach.

Try this approach:

qs.annotate(Count('book')).filter(Q(book__count__gt=1))
Work.objects.filter(pk__in=qs.values_list('pk', flat=True)).update(visible=False)

Upvotes: 12

Related Questions