Unable to use OuterRef(Expression(...))

[Matthew Schinckel]

Whilst refactoring some code today, I noticed I had the following code in a queryset method:

staff = Person.objects.filter(
    first_name__iexact=OuterRef('_first_name'),
    ...
)

return self.annotate(
    _first_name=Func(F('data'), Value('FirstName'), function='JSONB_EXTRACT_PATH_TEXT', output_field=models.TextField()
).annotate(
    candidates=Array(staff.values('pk'))
)

It's an annoyance to have to annotate on the expression to the outer queryset, and then reference it in the inner queryset. It's also forcing the database to do more work, because the SQL that is generated will result in the expression being evaluated multiple times.

Instead, it will be much nicer to write:

staff = Person.objects.filter(
    first_name__iexact=OuterRef(
        Func(OuterRef('data'), Value('FirstName'), function='JSONB_EXTRACT_PATH_TEXT', output_field=models.TextField()
    )
)

return self.annotate(candidates=Array(staff.values('pk')))

(Note the second use of OuterRef there because we are referring to the data from the outer queryset).

I believe the only change that is required to enable this is in OuterRef.resolve_expression (and I have a working prototype on my 1.11 codebase).

Does this seem like a sane change to the functionality?

[Simon Charette]

Something seems off here, the OuterRef should not be nested. Did you mean

staff = Person.objects.filter(
    first_name__iexact=Func(
        OuterRef('data'), Value('FirstName'), function='JSONB_EXTRACT_PATH_TEXT', output_field=models.TextField()
    )
)
return self.annotate(candidates=Array(staff.values('pk')))

If that's the case then yes I believe we should add support for that, I was under the impression it was already supported.

[Matthew Schinckel]

Hmm. I didn't think to use that syntax, let me test it.

[Matthew Schinckel]

Yeah, that does indeed work.

I'll close this (and the PR), but I still have an itchy feeling that there is a use-case for having an expression inside the OuterRef - but maybe it's always that the Expression itself would just have the OuterRef inside it...

[Simon Charette]

I'll close this (and the PR), but I still have an itchy feeling that there is a use-case for having an expression inside the OuterRef - but maybe it's always that the Expression itself would just have the OuterRef inside it...

I think this should not happen; we don't allow expressions in F either since it's just an unresolved reference to a column. In a sense OuterRef is really just a way to express F for an outer query.