CODEWITHSUNDEEP
pythondjangoannotations
zhuyxn
zhuyxn

Reputation: 7081

Django: Annotation not working?

I have two models, Product, which has a one-to-many relationship with a RatingEntry:

>>> product_entries = models.Products.objects.all()
>>> annotated = product_entries.annotate(Count("ratingentry"))
>>> len(annotated)
210
>>> a = annotated.filter(ratingentry__count__lte = 10)
>>> b = annotated.filter(ratingentry__count__gt = 10)
>>> len(a)
10
>>> len(b)
200
>>> len(a | b)
10 //should be 210

If I change a and b to lists, and concatenate them, the length works out to 210.

Any idea what's going on here?

Upvotes: 0

Views: 1129

Answers (2)

Gareth Rees
Gareth Rees

Reputation: 65854

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.)

Upvotes: 2

Chris Pratt
Chris Pratt

Reputation: 239250

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.

Upvotes: -1

Related Questions