[Answer]-Django: Annotation not working?

by

2👍

✅

I think this behaviour is a bug in Django’s object-relationship mapping. If you look at the SQL that Django generates for your query, then you’ll see something like this:

>>> q1 = (Products.objects.annotate(num_ratings = Count('ratingentries'))
...       .filter(num_ratings__gt = 10))
>>> q2 = (Products.objects.annotate(num_ratings = Count('ratingentries'))
...       .exclude(num_ratings__gt = 10))
>>> print(str((q1 | q2).query))
SELECT `myapp_products`.`id`, COUNT(`myapp_ratingentries`.`id`) AS
`num_ratings` FROM `myapp_products` LEFT OUTER JOIN `myapp_ratingentries` ON
(`myapp_products`.`id` = `myapp_ratingentries`.`product_id`) GROUP BY
`myapp_products`.`id` HAVING COUNT(`myapp_ratingentries`.`id`) > 10
ORDER BY NULL

Note that the condition from q1 is included in the HAVING clause of the query, but the condition from q2 has been lost.

You can work around the problem by building your query like this:

>>> q = Q(num_products__gt = 10) | ~Q(num_products__gt = 10)
>>> q3 = Products.objects.annotate(num_ratings = Count('ratingentries')).filter(q)
>>> print(str(q3.query))
SELECT `myapp_products`.`id`, COUNT(`myapp_ratingentries`.`id`) AS
`num_ratings` FROM `myapp_products` LEFT OUTER JOIN `myapp_ratingentries` ON
(`myapp_products`.`id` = `myapp_ratingentries`.`product_id`) GROUP BY
`myapp_products`.`id` HAVING (COUNT(`myapp_ratingentries`.`id`) > 10 OR NOT
(COUNT(`myapp_ratingentries`.`id`) > 10 )) ORDER BY NULL

Note that both conditions are now included in the HAVING clause.

I suggest that you report this to the Django developers as a bug. (If it can’t be fixed, then at least it should be documented.)

👤Gareth Rees

-1👍

Querysets don’t support bitwise inclusion. Instead of raising an error, Django treats it as a logical OR and returns the first that evaluates True. Since they’re both valid querysets, the first one is always returned.

If you want to actually combine two querysets, you either need to convert them to lists and then extend one with the other, or use something like itertools.chain, but you’ll end up with a generator that can’t be used for anything but iteration. Either way, combining querysets will disallow any further operation on those querysets.

👤Chris Pratt

Leave a comment