distinct does not apply to aggregated querysets
|Reported by:||micolous||Owned by:|
|Component:||Database layer (models, ORM)||Version:||1.6|
|Has patch:||no||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
Description (last modified by ramiro)
When applying .distinct() to a QuerySet, performing an aggregation using .aggregate() on that will only ensure that the aggregation result is distinct. As a result, this prevents you from doing a query where you want the input to the aggregation to have distinct rows.
Take the example models:
class Category(Model): label = CharField(max_length=128) class Food(Model): name = CharField(max_length=128) categories = ManyToManyField(Category) kilojoules_per_kg = PositiveIntegerField()
If you then did a query on which you selected a couple of Categories as foreign keys (eg: "Pizza Toppings" and "Fruits"), you'd end up with a list of Foods that would contain duplicates where a food item fits in multiple categories (eg: "Apple", "Orange", "Pineapple", "Pineapple", "Bacon" and "Cheese" ...).
If you then wanted to then find the average number of kilojoules per kilogram for items in those groups and eliminate duplicates (Pineapple), the documentation would lead you to do this:
However instead of counting Pineapple once, Pineapple would be counted twice, as the distinct() call would only apply to the result of the aggregation, not to the input of it.
>>> qs.distinct().aggregate(Avg('kilojoules_per_kg')) == qs.aggregate(Avg('kilojoules_per_kg')) True
Instead, .distinct() before .aggregate() should instead cause a subquery to be executed to filter the results before passing it to the aggregation functions. There doesn't seem a way to presently execute a query like this using the Django ORM.
.distinct() after an aggregation should cause the results of the aggregation to be filtered so only distinct records are returned (which is the current behavior).
I've managed to confirm this behavior in Django 1.2.3 and 1.3.
Change History (9)
comment:1 Changed 4 years ago by micolous
- Needs documentation unset
- Needs tests unset
- Patch needs improvement unset
comment:2 Changed 4 years ago by ramiro
- Component changed from Database layer (models, ORM) to ORM aggregation
- Triage Stage changed from Unreviewed to Accepted
comment:6 Changed 3 years ago by akaariai
- Component changed from ORM aggregation to Database layer (models, ORM)
comment:8 Changed 6 months ago by mikalai
- Owner changed from nobody to mikalai
- Status changed from new to assigned