Django annotate（）多次导致错误的答案 [英] Django annotate() multiple times causes wrong answers

问题描述

Django对于querysets有一个很棒的新的annotate（）函数。但是，我无法使其在单个查询器中的多个注释正常工作。

例如，

  tour_list = Tour.objects.all（）。annotate（Count（'tourcomment'））.annotate（Count（'history'））
  

旅游可以包含多个tourcomment和历史记录条目。我试图获得这次旅游有多少评论和历史记录。由此产生的

  history__count和tourcomment__count 
  

值将不正确。如果只有一个annotate（）调用该值将是正确的。

似乎有一些乘法效果来自两个 LEFT OUTER JOIN 。例如，如果一个旅游有3个历史和3个评论，9将是两者的计数值。 12个历史记录+ 1个评论= 12两个值。 1个历史记录+ 0个评论= 1个历史记录，0条评论（这一个恰好返回正确的值）。

生成的SQL调用是：

  SELECT`testapp_tour`.`id`，`testapp_tour`.`operator_id`，`testapp_tour`.`name`，`testapp_tour`.`region_id`， `testapp_tour`.`description`，`testapp_tour`````````````````````````````````````````````````````````````````````````````````````````````````````````` `.`modify_date`，`testapp_tour`.`image1`，`testapp_tour````2```````````````````````````````````````````````````````````````````````````````````````````````````````````` `pickup_time`，`testapp_tour`.`dropoff_time`，COUNT（`testapp_tourcomment`.`id`）AS`tourcomment__count`，COUNT（`testapp_history`.`id`）AS`history__count` 
 FROM`testapp_tour` LEFT OUTER JOIN`testapp_tourcomment` ON（`testapp_tour`.`id` =`testapp_tourcomment`.`tour_id`）LEFT OUTER JOIN`testapp_history` ON（`testapp_tour`.`id` =`testapp_history`.`tour_id`）
 GROUP BY`t estapp_tour`.`id` 
 ORDER BY`testapp_tour`.`name` ASC 
  

I尝试将包含单个调用的两个查询器的结果组合到annotate（），但是它不起作用...您不能真正保证该顺序是一样的。它似乎过于复杂和凌乱，所以我一直在寻找更好的东西...

  tour_list = Tour.objects.all （）.filter（operator__user_exact = request.user）.filter（enabled__exact = True）.annotate（Count（'tourcomment'））
 tour_list_historycount = Tour.objects.all（）。filter（enabled__exact = True） （Count（'history'））
 for i，o in enumerate（tour_list）：
 o.history__count = tour_list_historycount [i] .history__count 
  

感谢任何帮助。 Stackoverflow过去已经保存了很多已经回答的问题，但是我还没有找到答案。

解决方案感谢您的评论。那并没有很好的工作，但是它使我走向正确的方向。我终于可以通过添加不同于Count（）调用来解决这个问题：

  Count（'tourcomment'，distinct = True ）
  

Django has the great new annotate() function for querysets. However I can't get it to work properly for multiple annotations in a single queryset.

For example,

tour_list = Tour.objects.all().annotate( Count('tourcomment') ).annotate( Count('history') )

A tour can contain multiple tourcomment and history entries. I'm trying to get how many comments and history entries exist for this tour. The resulting

history__count and tourcomment__count

values will be incorrect. If there's only one annotate() call the value will be correct.

There seems to be some kind of multiplicative effect coming from the two LEFT OUTER JOINs. For example, if a tour has 3 histories and 3 comments, 9 will be the count value for both. 12 histories + 1 comment = 12 for both values. 1 history + 0 comment = 1 history, 0 comments (this one happens to return the correct values).

The resulting SQL call is:

SELECT `testapp_tour`.`id`, `testapp_tour`.`operator_id`, `testapp_tour`.`name`, `testapp_tour`.`region_id`, `testapp_tour`.`description`, `testapp_tour`.`net_price`, `testapp_tour`.`sales_price`, `testapp_tour`.`enabled`, `testapp_tour`.`num_views`, `testapp_tour`.`create_date`, `testapp_tour`.`modify_date`, `testapp_tour`.`image1`, `testapp_tour`.`image2`, `testapp_tour`.`image3`, `testapp_tour`.`image4`, `testapp_tour`.`notes`, `testapp_tour`.`pickup_time`, `testapp_tour`.`dropoff_time`, COUNT(`testapp_tourcomment`.`id`) AS `tourcomment__count`, COUNT(`testapp_history`.`id`) AS `history__count` 
FROM `testapp_tour` LEFT OUTER JOIN `testapp_tourcomment` ON (`testapp_tour`.`id` = `testapp_tourcomment`.`tour_id`) LEFT OUTER JOIN `testapp_history` ON (`testapp_tour`.`id` = `testapp_history`.`tour_id`)
GROUP BY `testapp_tour`.`id`
ORDER BY `testapp_tour`.`name` ASC

I have tried combining the results from two querysets that contain a single call to annotate (), but it doesn't work right... You can't really guarantee that the order will be the same. and it seems overly complicated and messy so I've been looking for something better...

tour_list = Tour.objects.all().filter(operator__user__exact = request.user ).filter(enabled__exact = True).annotate( Count('tourcomment') )
tour_list_historycount = Tour.objects.all().filter( enabled__exact = True ).annotate( Count('history') )
for i,o in enumerate(tour_list):
    o.history__count = tour_list_historycount[i].history__count

Thanks for any help. Stackoverflow has saved my butt in the past with a lot of already-answered questions, but I wasn't able to find an answer to this one yet.

解决方案

Thanks for your comment. That didn't quite work but it steered me in the right direction. I was finally able to solve this by adding distinct to both Count() calls:

Count('tourcomment', distinct=True)

这篇关于Django annotate（）多次导致错误的答案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！