Is there a way to slice a subquery with an OuterRef(field) ?

Using Django Using the ORM
1

Hello everyone !

I am trying to filter a subquery and Slice it by the value of an annotated field.

Solution 1: Failed

Subquery(
MyModel.objects.all()[:OuterRef(‘custom_index’)]
)

As Django ORM doesn’t support this way,

–

Solution 2:
I thought of using RowNumber and filter against it like:

Subquery(
MyModel.objects
.annotate(
row_nb=Window(
expression=RowNumber(),
order_by=F(‘sale_datetime’).asc(),
)
)
.filter(
row_nb__lte=F(‘custom_index’)
)
)

But it is not supported (yet?)

–

Solution 3: Failed
Then I thought of creating an ArrayAgg of the ‘sale_datetime’ and try to extract the nth index with a custom Expression such as:

annotate_array=ArrayAgg(‘content__sale_datetime’)
annotate_val=ArrayIndex(‘annotate_array’, F(‘custom_index’))

class ArrayIndex(Func):
function = ‘ARRAY’
template = “%(function)s[%(expressions)s]”

def __init__(self, expression, index, **extra):
    output_field = extra.pop('output_field', None)
    super().__init__(expression, index, output_field=output_field, **extra)

But it’s raising some django.db.utils.ProgrammingError and I’m not proficient enough make it operate, nor can find enough documentation and examples on the matter…

–

Solution 4: Last and less efficient way
Populate a “row_nb” when creating / updating the items one by one and filter against it.

–

If anyone already had the same issue or has an alternative to handle this queryset “on the fly”, don’t hesitate to comment !

Thank you for your time guies.

2

Are you trying to slice it by an annotated field in the Subquery or as a reference to the outer query? If the latter, then you would want to try using OuterRef.

Also, it might help if you outlined what your desired results are from this. I’m having a difficult time reading through these attempted solutions and trying to figure out what the objective is.

3

Hi !

Indeed trying to refer to an OuterRef (but the orm doesn’t support it anyways).

I am trying to filter a subquery but filtered by ‘nth’ rows,

nth being an annotated field of the main query.

Example:
I have an Invoice model, and an InvoiceContent (with a foreign key to Invoice, related_name=‘content’).

There are 300 InvoiceContent elements related to the invoice,
I need to filter the first nth [OuterRef(‘bonus_to_reach’), let’s say 120]

Invoice.objects.annotate(
bonus_to_reach=Subquery(…), <= IntegerField

nb_validated=Subquery(
  InvoiceContent.objects
  .only('pk')
  .annotate(
  	row_nb=Window(
  		expression=RowNumber(),
  		order_by=F('sale_datetime').asc(),
  	)
  )
  .filter(
  	invoice_id=OuterRef('pk'),
  	type='VALIDATION',
  	row_nb__lte=OuterRef('bonus_to_reach'),
  )
  .order_by()
    .values('pk')
    .annotate(count=Func(F('pk'), function='Count'))
    .values('count')

)
)

That would be ideal, but impossible to filter RowNumber() annotated field with OuterRef.

I also tried to annotate the row_nb “manually” with a nested Subquery calculated all invoice_content row_nb before the existing one by sale_datetime.

It “works” but comes with serious performance issues :-/

Sorry if I’m not being clear enough, don’t hesitate to ask me to clarify.

Thank you for your time.

4

Hello there,

It would be great to know what kind of SQL you are trying to generate and which backends you are using. I assume Postgres based on the ARRAY usage?

Solution 1: Failed; As Django ORM doesn’t support this way,

I wasn’t aware that some backends supported non-constant expressions in the limit clause and the ORM effectively doesn’t support that. I don’t think it would be that hard to add though.

Solution 2: Failed; But it is not supported (yet?)

It should be on Django 4.2+ which is the minimal supported Django version.

Impossible to help you without a traceback.

Solution 3: Failed

Very hard to help you without the exact SQL being generated as well as the full traceback.