具有OuterRef的简单子查询 [英] Simple Subquery with OuterRef

问题描述

我正在尝试制作一个使用OuterRef的非常简单的Subquery(并非出于实际目的，只是为了使其正常工作)，但始终会遇到相同的错误.

I am trying to make a very simple Subquery that uses OuterRef (not for practical purposes, just to get it working), but keep running into same error.

posts/models.py代码

from django.db import models

class Tag(models.Model):
    name = models.CharField(max_length=120)
    def __str__(self):
        return self.name

class Post(models.Model):
    title = models.CharField(max_length=120)
    tags = models.ManyToManyField(Tag)
    def __str__(self):
        return self.title

manage.py shell代码

>>> from django.db.models import OuterRef, Subquery
>>> from posts.models import Tag, Post
>>> tag1 = Tag.objects.create(name='tag1')
>>> post1 = Post.objects.create(title='post1')
>>> post1.tags.add(tag1)
>>> Tag.objects.filter(post=post1.pk)
<QuerySet [<Tag: tag1>]>
>>> tags_list = Tag.objects.filter(post=OuterRef('pk'))
>>> Post.objects.annotate(count=Subquery(tags_list.count()))

最后两行应该给我每个Post对象的标签数量.在这里，我不断收到相同的错误:

The last two lines should give me number of tags for each Post object. And here I keep getting the same error:

ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

推荐答案

示例的问题之一是您不能将queryset.count()用作子查询，因为.count()尝试评估查询集并返回计数.

One of the problems with your example is that you cannot use queryset.count() as a subquery, because .count() tries to evaluate the queryset and return the count.

因此，人们可能会认为正确的方法是改为使用Count().也许是这样的:

So one may think that the right approach would be to use Count() instead. Maybe something like this:

Post.objects.annotate(
    count=Count(Tag.objects.filter(post=OuterRef('pk')))
)

这行不通有两个原因:

1. Tag查询集选择所有Tag字段，而Count只能依靠一个字段.因此:Tag.objects.filter(post=OuterRef('pk')).only('pk')是必需的(以选择tag.pk上的计数).

1. The Tag queryset selects all Tag fields, while Count can only count on one field. Thus: Tag.objects.filter(post=OuterRef('pk')).only('pk') is needed (to select counting on tag.pk).

Count本身不是Subquery类，Count是Aggregate.因此，由Count生成的表达式不能识别为Subquery(OuterRef需要子查询)，我们可以使用Subquery来解决.

Count itself is not a Subquery class, Count is an Aggregate. So the expression generated by Count is not recognized as a Subquery (OuterRef requires subquery), we can fix that by using Subquery.

应用针对1)和2)的修复程序会产生:

Apply-ing fixes for 1) and 2) would produce:

Post.objects.annotate(
    count=Count(Subquery(Tag.objects.filter(post=OuterRef('pk')).only('pk')))
)

但是 如果您检查正在生成的查询

However if you inspect the query being produced

SELECT 
    "tests_post"."id",
    "tests_post"."title",
    COUNT((SELECT U0."id" 
            FROM "tests_tag" U0 
            INNER JOIN "tests_post_tags" U1 ON (U0."id" = U1."tag_id") 
            WHERE U1."post_id" = ("tests_post"."id"))
    ) AS "count" 
FROM "tests_post" 
GROUP BY 
    "tests_post"."id",
    "tests_post"."title"

您可能会注意到我们有一个GROUP BY子句.这是因为Count是一个聚合，目前它不影响结果，但在某些情况下可能会影响结果.这就是为什么文档提出了一些不同的方法，即通过values + annotate + values

You may notice that we have a GROUP BY clause. This is because Count is an Aggregate, right now it does not affect the result, but in some other cases it may. Thats why the docs suggest a little bit different approach, where the aggregation is moved into the subquery via a specific combination of values + annotate + values

Post.objects.annotate(
    count=Subquery(
        Tag.objects.filter(post=OuterRef('pk'))
            # The first .values call defines our GROUP BY clause
            # Its important to have a filtration on every field defined here
            # Otherwise you will have more than one group per row!!!
            # This will lead to subqueries to return more than one row!
            # But they are not allowed to do that!
            # In our example we group only by post
            # and we filter by post via OuterRef
            .values('post')
            # Here we say: count how many rows we have per group 
            .annotate(count=Count('pk'))
            # Here we say: return only the count
            .values('count')
    )
)

最后这将产生:

SELECT 
    "tests_post"."id",
    "tests_post"."title",
    (SELECT COUNT(U0."id") AS "count" 
            FROM "tests_tag" U0 
            INNER JOIN "tests_post_tags" U1 ON (U0."id" = U1."tag_id") 
            WHERE U1."post_id" = ("tests_post"."id") 
            GROUP BY U1."post_id"
    ) AS "count" 
FROM "tests_post"

这篇关于具有OuterRef的简单子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！