Django 1.11注释子查询聚合 [英] Django 1.11 Annotating a Subquery Aggregate

问题描述

这是我目前不知所措的尖端功能，很快就会流血。我想将子查询聚合注释到现有查询集上。在1.11之前执行此操作意味着自定义SQL或修改数据库。 这是此文档的内容，以及其中的示例：

This is a bleeding-edge feature that I'm currently skewered upon and quickly bleeding out. I want to annotate a subquery-aggregate onto an existing queryset. Doing this before 1.11 either meant custom SQL or hammering the database. Here's the documentation for this, and the example from it:

from django.db.models import OuterRef, Subquery, Sum
comments = Comment.objects.filter(post=OuterRef('pk')).values('post')
total_comments = comments.annotate(total=Sum('length')).values('total')
Post.objects.filter(length__gt=Subquery(total_comments))

它们是在总体上进行注释，这对我来说似乎很奇怪，但是无论如何。

They're annotating on the aggregate, which seems weird to me, but whatever.

我正在为此苦苦挣扎，所以我把它煮沸了我有数据的最简单的实际示例。我有停车场，其中包含许多空间。使用 Book→Author 如果这会让您更开心，但是（现在），我只想使用 Subquery *。

I'm struggling with this so I'm boiling it right back to the simplest real-world example I have data for. I have Carparks which contain many Spaces. Use Book→Author if that makes you happier but —for now— I just want to annotate on a count of the related model using Subquery*.

spaces = Space.objects.filter(carpark=OuterRef('pk')).values('carpark')
count_spaces = spaces.annotate(c=Count('*')).values('c')
Carpark.objects.annotate(space_count=Subquery(count_spaces))

这给了我一个可爱的 ProgrammingError：子查询返回的多行用作表达式，在我看来，这个错误是很合理的。子查询返回带注释的总数的空格列表。

This gives me a lovely ProgrammingError: more than one row returned by a subquery used as an expression and in my head, this error makes perfect sense. The subquery is returning a list of spaces with the annotated-on total.

该示例表明，将会发生某种魔术，最后我得到一个数字I可以用。但这不是在这里发生吗？

The example suggested that some sort of magic would happen and I'd end up with a number I could use. But that's not happening here? How do I annotate on aggregate Subquery data?

我建立了一个新的停车场/太空模型，它起作用了。因此，下一步是找出导致SQL崩溃的原因。根据Laurent的建议，我看了一下SQL，并尝试使其更像他们在答案中发布的版本。这是我发现真正问题的地方：

I built a new Carpark/Space model and it worked. So the next step is working out what's poisoning my SQL. On Laurent's advice, I took a look at the SQL and tried to make it more like the version they posted in their answer. And this is where I found the real problem:

SELECT "bookings_carpark".*, (SELECT COUNT(U0."id") AS "c"
FROM "bookings_space" U0
WHERE U0."carpark_id" = ("bookings_carpark"."id")
GROUP BY U0."carpark_id", U0."space"
)
AS "space_count" FROM "bookings_carpark";

我已突出显示它，但这是子查询的 GROUP BY ... U0。空格 。由于某种原因，它们都在重新调整。调查仍在继续。

I've highlighted it but it's that subquery's GROUP BY ... U0."space". It's retuning both for some reason. Investigations continue.

编辑2：好的，仅查看子查询SQL，通过☹

Edit 2: Okay, just looking at the subquery SQL I can see that second group by coming through ☹

In [12]: print(Space.objects_standard.filter().values('carpark').annotate(c=Count('*')).values('c').query)
SELECT COUNT(*) AS "c" FROM "bookings_space" GROUP BY "bookings_space"."carpark_id", "bookings_space"."space" ORDER BY "bookings_space"."carpark_id" ASC, "bookings_space"."space" ASC

编辑3 ：好的！这两种模型都有排序顺序。这些被传送到子查询。正是这些订单使我的查询膨胀并破坏了它。

Edit 3: Okay! Both these models have sort orders. These are being carried through to the subquery. It's these orders that are bloating out my query and breaking it.

我想这可能是Django中的一个错误，但是在这两个模型上都没有删除Meta-order_by， 有什么方法可以在查询时取消排序查询吗？

I guess this might be a bug in Django but short of removing the Meta-order_by on both these models, is there any way I can unsort a query at querytime?

_{* 我知道我可以为这个示例添加一个计数。我使用它的真正目的是要使用更复杂的过滤器计数，但我什至无法使它正常工作。}

_{*I know I could just annotate a Count for this example. My real purpose for using this is a much more complex filter-count but I can't even get this working.}

推荐答案

还可以创建 Subquery 的子类，以更改其输出的SQL。例如，您可以使用：

It's also possible to create a subclass of Subquery, that changes the SQL it outputs. For instance, you can use:

class SQCount(Subquery):
    template = "(SELECT count(*) FROM (%(subquery)s) _count)"
    output_field = models.IntegerField()

然后像原始子查询类一样使用它：

You then use this as you would the original Subquery class:

spaces = Space.objects.filter(carpark=OuterRef('pk')).values('pk')
Carpark.objects.annotate(space_count=SQCount(spaces))

您可以将此技巧（至少在postgres中）与一系列汇总功能结合使用：我经常使用它来构建值数组，或加起来。

You can use this trick (at least in postgres) with a range of aggregating functions: I often use it to build up an array of values, or sum them.

这篇关于Django 1.11注释子查询聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！