带有OuterRef的Django子查询和注释

问题描述

在Django 1.11子查询中，将annotate()与 OuterRef 一起使用时遇到问题.示例模型:

I'm having problems using annotate() with OuterRef in Django 1.11 subqueries. Example models:

class A(models.Model):
    name = models.CharField(max_length=50)


class B(models.Model):
    a = models.ForeignKey(A)

现在使用子查询进行查询(这实际上没有任何意义，但可以说明我的问题):

Now a query with a subquery (that does not really make any sense but illustrates my problem):

A.objects.all().annotate(
    s=Subquery(
        B.objects.all().annotate(
            n=OuterRef('name')
        ).values('n')[:1],
        output_field=CharField()
    )
)

这会出现以下错误:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "myapp/models.py", line 25, in a
    n=OuterRef('name')
  File ".virtualenv/lib/python2.7/site-packages/django/db/models/query.py", line 948, in annotate
    if alias in annotations and annotation.contains_aggregate:
AttributeError: 'ResolvedOuterRef' object has no attribute 'contains_aggregate'

是否可以基于OuterRef注释子查询?

Is it not possible to annotate a subquery based on an OuterRef?

为此找到了一种解决方法，现在我可以继续前进，但这并不好.

Found a workaround for this that will allow me to move forward for now, but it's not nice.

class RawCol(Expression):

    def __init__(self, model, field_name, output_field=None):
        field = model._meta.get_field(field_name)
        self.table = model._meta.db_table
        self.column = field.column
        super().__init__(output_field=output_field)

    def as_sql(self, compiler, connection):
        sql = f'"{self.table}"."{self.column}"'
        return sql, []

更改 OuterRef 以使用自定义表达式

Changing OuterRef to use the custom expression

A.objects.all().annotate(
    s=Subquery(
        B.objects.all().annotate(
            n=RawCol(A, 'name')
        ).values('n')[:1],
        output_field=CharField()
    )
)

收益

SELECT "myapp_a"."id",
       "myapp_a"."name",

  (SELECT "myapp_a"."name" AS "n"
   FROM "myapp_b" U0 LIMIT 1) AS "s"
FROM "myapp_a"

推荐答案

这是Django中的一个已知错误，已在3.0中修复.

It is a known bug in Django which has been fixed in 3.0.

有关讨论，请参见 https://code.djangoproject.com/ticket/28621

如果像我一样需要注释该字段，以便可以在随后的子查询中使用它，请记住，您可以像以下那样堆叠 OuterRef :

If you, like me, need to annotate the field such that you can use it in a following subquery, remember that you can stack OuterRef like:

id__in=SubQuery(
    MyModel.objects.filter(
        field=OuterRef(OuterRef(some_outer_field))
    )
)

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