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.

我正在为此苦苦挣扎，所以我将它重新煮回到我有数据的最简单的现实世界示例.我有包含许多 Space 的 Carparks.使用 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.

这个例子表明会发生某种魔法，我最终会得到一个我可以使用的数字.但这不是在这里发生的吗?如何对聚合子查询数据进行注释?

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."space".由于某种原因，它正在重新调整两者.调查仍在继续.

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

然后像使用原始 Subquery 类一样使用它:

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屋！