Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django subquery and annotations with 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()
    )
)

This gives the following error:

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'

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


Update #1

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, []

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

Yields

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

  (SELECT "myapp_a"."name" AS "n"
   FROM "myapp_b" U0 LIMIT 1) AS "s"
FROM "myapp_a"
like image 808
toucan Avatar asked Nov 03 '17 11:11

toucan


People also ask

What is OuterRef Django?

Feb 9, 2018, 12:52:50 PM2/9/18. to Django users. According to the documentation on models. OuterRef: It acts like an F expression except that the check to see if it refers to a valid field isn't made until the outer queryset is resolved.

Does Django ORM support subquery?

¶ Django allows using SQL subqueries.

What is difference between annotate and aggregate in Django?

Unlike aggregate() , annotate() is not a terminal clause. The output of the annotate() clause is a QuerySet ; this QuerySet can be modified using any other QuerySet operation, including filter() , order_by() , or even additional calls to annotate() .


2 Answers

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

See https://code.djangoproject.com/ticket/28621 for the discussion.

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))
    )
)
like image 133
Igor Avatar answered Sep 21 '22 22:09

Igor


One field of one related row of B can be annotated this way for every row of A.

subq = Subquery(B.objects.filter(a=OuterRef('pk')).order_by().values('any_field_of_b')[:1])
qs = A.objects.all().annotate(b_field=subq)

(It was more readable to write it as two commands with a temporary variable. That is a style similar to docs.)

It is compiled to one SQL request:

>>> print(str(qs.suery))
SELECT a.id, a.name,
  (SELECT U0.any_field_of_b FROM b U0 WHERE U0.a_id = (a.id)  LIMIT 1) AS b_field
FROM a

(simplified without "appname_")

like image 29
hynekcer Avatar answered Sep 20 '22 22:09

hynekcer