1

I was trying to use an annotated value to filter a query set using an F expression, but it turns out it it does not behave the way I am expecting.

I am trying to filter objects from a query set qs whose property some_prop is the maximum of that query set. For example:

set(qs.values_list('some_prop', flat=True))

would output:

set([1, 3, 5, 7, 9])

And I am aiming to filter qs by the value 9, which is the maximum. I can easily achieve that using an aggregation:

max_prop = qs.aggregate(max_prop=Max('some_prop'))['max_prop']
qs.filter(some_prop=max_prop)

But I wanted to condense this behavior in a single query, so I used an annotation instead of an aggregation:

qs.annotate(max_prop=Max('some_prop')).filter(some_prop=F('max_prop'))

However this does not show the same behavior. This is actually just filtering by all some_prop values rather than the maximum.

What am I missing? There must be something I am misunderstanding from the annotation and aggregation features.

dabadaba
  • 9,064
  • 21
  • 85
  • 155

1 Answers1

0

Since your annotation does not take values from a one-to-many relationship, for which the max from the set of related objects will be annotated to the object related to the set, the Max (for each object) is taken on only one value - some_prop - and attached to each object in the queryset; which isn't different from accessing/filtering-on some_prop directly:

When an annotate() clause is specified, each object in the QuerySet will be annotated with the specified values.

For a single table, you'll need to aggregate to get the max value first, then filter on that value.

Moses Koledoye
  • 77,341
  • 8
  • 133
  • 139
  • so there is no way I can achieve the behavior I'm after in a single query with `annotate`? – dabadaba Jul 18 '17 at 09:21
  • @dabadaba There is a work around that uses `.values` to group all the objects into one and fetch the final max value: https://stackoverflow.com/questions/9838264/django-record-with-max-element – Moses Koledoye Jul 18 '17 at 09:22