[Fixed]-Django subquery and annotations with OuterRef

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

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_”)