How OuterRef works in django?

Question

Im not able to understand how really OuterRef in django ORM works. For example:

    Table.objects.filter(field=OuterRef("pk"))

What does this mean? What role does field and pk play here and how it affects the final queryset? Someone please elaborate. Thanks in advance.

Answer 1

The thing to understand is that the behavior of OuterRef depends of the context where the query is used.

Taking the example you have:

Table.objects.filter(field=OuterRef("pk"))

You can read this as "filter the rows of table Table where Table.field is equal to the primary key of the outer query context".

Alone it doesn't translate to a complete SQL query, but here is what it means in a SQL query context:

SELECT pk, ... FROM ... WHERE ...(SELECT * FROM Table WHERE field = pk)
^^^^^^^^^^^^^^^^^^                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
outer query                       this is the part that corresponds to
                                  Table.objects.filter(field=OuterRef("pk"))

Usually you will either use it in a context that works on a subset of rows (with an operator like Exists, for example) or you will add a limit 1 in order to have a single-result to use in a comparison to some field.

In Django ORM, you would do Table.objects.filter(field=OuterRef("pk"))[:1] to get a single result for the subquery.