Limiting a SubQuery based on instance's OuterRef

[Nick Gashkov]

Hello there!

Is it possible to limit a SubQuery to a dynamic value of an instance in a QuerySet? I.e. I'm looking for something like:

queryset = model.objects.filter(field=OuterRef('field')).values('another_field')[:OuterRef('number')]

Sincerely,

Nick Gashkov

[Simon Charette]

Hello Nick,

It isn't possible to reference columns in a LIMIT clause AFAIK.

I'd suggest you use a RowNumber[0] window function ordered by your number column in a

subquery and filter on the annotated value in the outer query.

Cheers,

Simon

[0] https://docs.djangoproject.com/en/2.1/ref/models/database-functions/#rownumber

[Nick Gashkov]

Simon,

Thank you for fast reply! I've tried to rewrite a query using RowNumber()...

inner_subquery = queryset.objects.filter(date__gte=today, day_type='working_day')
inner_subquery = inner_subquery.annotate(row_number=Window(expression=RowNumber(), order_by=F('number').asc()))
inner_subquery = inner_subquery.filter(pk=OuterRef('pk'))
inner_subquery = inner_subquery.values('row_number')[:1]

outer_subquery = queryset.objects.annotate(row_number=Subquery(inner_subquery))
outer_subquery = outer_subquery.filter(row_number=OuterRef('number'))
outer_subquery = outer_subquery.values('value')[:1]

queryset = queryset.annotate(result=Subquery(outer_subquery))

...but stumbled upon a following exception:

Expression contains mixed types. You must set output_field.

Am I doing something wrong?