[Answered ]-Django Subquery with Scalar Value

by

1👍

Subscription.objects.annotate(
    max_expiraton_date=Max('transaction__expiration_date')
).filter(
    status=1,
    recurrent__isnull=False,  # [inner] join with recurrent
    transaction__status__in=['OK', 'Complete'],
    max_expiraton_date=date_value
)

This produces other SQL query, but obtains the same Subscription objects.

👤Vladimir Danilov

1👍

You can (as of Django 1.11) annotate on a subquery, and slice it to ensure you only get the “first” result. You can then filter on that subquery annotations, by comparing to the value you want.

from django.db.models.expressions import Subquery, OuterRef

expiration_date = Transaction.objects.filter(
    company=OuterRef('company'),
    status__in=['OK', 'Complete'],
).order_by('-expiration_date').values('expiration_date')[:1]

Subscription.objects.filter(status=1).annotate(
    expiration_date=Subquery(expiration_date),
).filter(expiration_date__lte=THE_DATE)

However…

Currently that can result in really poor performance: your database will evaluate the subquery twice (once in the where clause, from the filter, and again in the select clause, from the annotation). There is work underway to resolve this, but it’s not currently complete.

👤Matthew Schinckel

Leave a comment