python - 强制为列表时 Django 查询集结果的长度会发生变化

Question

因此，我有一个相当复杂的查询集，它按 isoweek 汇总数据:

>>> MyThing.objects.all().count()
30000

>>> qs = MyThing.objects.all().order_by('date').annotate(
    dw=DateWeek('date'), # uses WEEK function
    dy=ExtractYear('date')
).values(
    'dy','dw','group_id'
).annotate(
    sum_count=Sum('count')
).values_list('dw', 'dy', 'group_id', 'sum_count')

>>> qs.count()
2000

到目前为止一切顺利。问题是当我将此查询集强制到列表中时:

>>> len(list(qs))
30000

为什么会发生这种情况？当我直接 count() 时，如何获取查询集声称拥有的分组值列表？

Answer 1

要解决此问题，请删除 .order_by('date')。尽管它不包含在输出中，但数据库后端仍在每一行都考虑它，导致行数像这样膨胀。

如果您想对输出进行排序，请在添加这些注释后.order_by('dy', 'dw')。

您还可以添加不带参数的 .order_by() 来清除之前设置的任何排序，例如来自 Model 类定义的默认排序。

🦆

django docs 中解释了此行为的原因。 :

Any fields used in an order_by() call are included in the SQL SELECT columns. This can sometimes lead to unexpected results when used in conjunction with distinct(). If you order by fields from a related model, those fields will be added to the selected columns and they may make otherwise duplicate rows appear to be distinct. Since the extra columns don’t appear in the returned results (they are only there to support ordering), it sometimes looks like non-distinct results are being returned.

Similarly, if you use a values() query to restrict the columns selected, the columns used in any order_by() (or default model ordering) will still be involved and may affect uniqueness of the results.

The moral here is that if you are using distinct() be careful about ordering by related models. Similarly, when using distinct() and values() together, be careful when ordering by fields not in the values() call.