Supporting aggregate expressions in QuerySet.update()

Django Internals ORM
1

Hi all,

I’d like to start a discussion about allowing aggregate expressions over related objects in QuerySet.update().

The problem

Denormalized counters and totals are a common pattern, and refreshing them in bulk currently fails:

python

Author.objects.update(book_count=Count("book"))
# FieldError: Aggregate functions are not allowed in this query

Current workaround

python

from django.db.models import OuterRef, Subquery, Count

counts = (
    Book.objects.filter(author=OuterRef("pk"))
    .order_by()
    .values("author")
    .annotate(c=Count("pk"))
    .values("c")
)
Author.objects.update(book_count=Subquery(counts))

This works, but it’s verbose and hard to discover. The .order_by() and the values().annotate().values() sequence are easy to get wrong. Missing rows also produce NULL rather than 0, so users often need Coalesce as well.

Proposal for discussion

Allow update() to accept aggregates over related objects and have Django compile them into the equivalent correlated subquery because adding a subquery is like a universal to all databases option. I’m not attached to this specific approach. A documented helper or a docs section on the Subquery pattern could be a lighter alternative.

Open questions

  1. What should the semantics be? My reading is per-row aggregation over related rows, but that needs to be stated explicitly.
  2. How should empty sets behave? Count naturally gives 0, while Sum and Avg give NULL. Should Django coalesce, or leave it to the user?
  3. Scope: aggregating over a different table works on all backends via a correlated subquery. Self-referential aggregates (the same table in the subquery) are restricted on MySQL and would need separate handling, so I’d suggest leaving them out of the initial scope.
  4. How would this interact with the existing restriction on F() expressions that span joins in update()?

Motivation
This came up when I needed to update a column from an aggregate over related rows. The Subquery approach works, but it took me a while to find and get right, which makes me think other users struggle with it too.

If there’s interest, I’m happy to work on a prototype or a docs patch.

Thanks,
Abhay

2

Welcome @jadaun-abhay !

This does look like an intriguing idea. As a new feature, it should be documented over in the “New Features repo”.

3

Hi Ken,

Thanks for the welcome and the pointer.

I should have mentioned that a Trac ticket for this already exists ([ticket #25643 https://code.djangoproject.com/ticket/25643\\]), and I’ve opened a PR against it (Fixed #25643 -- Added support for using aggregate and joined fields in update query via annotations and direct foreign key aggregation usage. by jadaun-abhay · Pull Request #21993 · django/django · GitHub). The PR currently has CI failures that I’m working through and will be happy if someone guides me because i am new to the Open-Source environment.

Given that, would you recommend I still open an issue in django/new-features, or is continuing on the existing ticket and PR the better path? I’m happy to write up the proposal there if that’s preferred.

Thanks again,
Abhay

4

Yes, knowing that this is much farther along puts it into a different category. No, there’s no need to write up the proposal. An accepted ticket is sufficient.

5

I’ve left a note on the PR about fixing the failing tests and adding the PR to the review queue. If you run stuck feel free to leave a note here, on the PR or in Discord.

For the design and implementation questions, I think the Track ticket is the best place, any interested persons have already added themselves to the discussion there.

1 Like
6

Thanks, Ken, that’s helpful to know. I’ll continue on the ticket and PR, and I’ll get the CI failures sorted out first.

7

Thanks, that’s helpful. I’ll fix the failing tests on the PR, and I’ll keep design and implementation discussion on the Trac ticket.

1 Like