0

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?

zhuyxn
  • 6,671
  • 9
  • 38
  • 44

2 Answers2

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
  • 64,967
  • 9
  • 133
  • 163
  • This explanation makes sense, I'll try to make sure its not some silly error on my part, and make a report of this. – zhuyxn Sep 27 '12 at 23:51
-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
  • 232,153
  • 36
  • 385
  • 444
  • 2
    I don't think that's right, I've combined QuerySets in the past using this operator, and it worked out. As an example: >>> a = models.Product.objects.filter(kind = "clothes") >>> b = models.Product.objects.filter(kind = "technology") >>> len(a) 90 >>> len(b) 113 >>> len(a | b) 203 >>> – zhuyxn Sep 27 '12 at 20:12
  • You can do `a = Q(ratingentry__count__lte=10);b = Q(ratingentry__count__gt=10);annotated.filter(a|b)` – dokkaebi Sep 27 '12 at 20:15