id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
30719	Unable to use OuterRef(Expression(...))	Matthew Schinckel	nobody	"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?"	New feature	closed	Database layer (models, ORM)	dev	Normal	worksforme	Subquery, OuterRef, Expression		Unreviewed	0	0	0	0	1	0