Code

Opened 5 years ago

Last modified 3 years ago

#10621 new New feature

Add a way to have an aggregate() result as a queryset

Reported by: mtredinnick Owned by: nobody
Component: Database layer (models, ORM) Version:
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

If one is trying to construct a complex queryset, involving nesting other querysets, there's currently no way to include the results of an aggregate() call. For example, consider

class Item(models.Model):
   name = models.CharField(max_length=10)

If I want to include the number of Items in some other queryset, such as

Foo.objects.filter(items__in=[...]).annotate(num=Count("items").filter(num=item_count)

We can't write item_count (which would be Items.objects.aggregate(Count("id"))) as an inner queryset here, since the aggregate() call returns a dictionary, not a queryset.

The return value of aggregate() is fine, but this ticket is about adding a version that does return a queryset. Right now, there's no way to fake this with annotate(...).values(...) since that always introduces a GROUP BY clause and the whole point is not to group by anything.

Not sure whether adding a parameter to aggregate() is the right idea, or adding something to the annotate() route to specify "no grouping whatsoever". This is all 1.2 timeframe stuff, but it's something to think about.

Attachments (0)

Change History (7)

comment:1 Changed 5 years ago by jacob

  • milestone set to 1.2
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 5 years ago by tobias

Maybe I misunderstand the problem, but in (postgre)SQL there is no way to filter on an aggregate column, e.g.:

django_test=# SELECT "testapp_parent"."id", COUNT("testapp_child"."id") AS "num" 
FROM "testapp_parent" 
LEFT OUTER JOIN "testapp_child" ON ("testapp_parent"."id" = "testapp_child"."parent_id") 
WHERE "num" = 0 
GROUP BY "testapp_parent"."id";
ERROR:  column "num" does not exist
LINE 1: ...parent"."id" = "testapp_child"."parent_id") WHERE "num" = 0 ...

You can ORDER BY aggregate columns (so perhaps this idea is still useful) but I'm not sure it'll work with .filter(num=item_count)

comment:3 Changed 5 years ago by tobias

Hmm, my statements above are patently false (you can do that with HAVING). Forget I ever said anything.

comment:4 Changed 4 years ago by ubernostrum

  • milestone 1.2 deleted

1.2 is feature-frozen, moving this feature request off the milestone.

comment:5 Changed 3 years ago by SmileyChris

  • Severity set to Normal
  • Type set to New feature

comment:6 Changed 2 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:7 Changed 2 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from nobody to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.