Django:注释不起作用? [英] Django: Annotation not working?

问题描述

我有两个模型，Product，它与 RatingEntry 是一对多的关系:

<预><代码>>>>product_entries = models.Products.objects.all()>>>注释 = product_entries.annotate(Count("ratingentry"))>>>len(注释)210>>>a = annotated.filter( ratingentry__count__lte = 10)>>>b = annotated.filter(ratingentry__count__gt = 10)>>>连(一)10>>>连(b)200>>>len(a | b)10//应该是210

如果我将 a 和 b 更改为列表，并将它们连接起来，则长度为 210.

知道这里发生了什么吗?

解决方案

我认为这种行为是 Django 对象关系映射中的一个错误.如果您查看 Django 为您的查询生成的 SQL，您将看到如下内容:

<预><代码>>>>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))>>>打印(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按空排序

请注意，q1 中的条件包含在查询的 HAVING 子句中，但 q2 中的条件已丢失.

您可以通过像这样构建查询来解决这个问题:

<预><代码>>>>q = Q(num_products__gt = 10) |~Q(num_products__gt = 10)>>>q3 = Products.objects.annotate(num_ratings = Count('ratingentries')).filter(q)>>>打印(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

请注意，这两个条件现在都包含在 HAVING 子句中.

我建议您将此作为错误报告给 Django 开发人员.(如果无法修复，那么至少应该记录下来.)

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?

解决方案

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

这篇关于Django:注释不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！