Search code examples
pythondjangodjango-orm

How can the Django ORM perform an aggregate subquery in a WHERE statement?

I'm trying to construct the following query similar to this the Django ORM:

SELECT * FROM table WHERE depth = (SELECT MIN(depth) FROM table)

How can this be written in Django ORM notations? So far it seems hard to use an aggregate like this, because QuerySet.aggregate() isn't lazy evaluated, but executes directly.

I'm aware that this basic example could be written as Model.objects.filter(depth=Model.objects.aggregate(m=Min('depth'))['m']) but then it does not evaluate lazily, and needs 2 separate queries. For my more complex case, I definitely need a lazily evaluated queryset.

FYI, things I've tried and failed:

  • a subquery with Model.objects.order_by().values().annotate(m=Min('depth').values('m')) will result in a GROUP BY id that seems hard to loose.
  • a subquery with Model.objects.annotate(m=Min('depth')).filter(depth=F('m')) will give a GROUP BY id, and include the m value in the main results as that's what annotate does.

My current workaround is using QuerySet.extra(where=[...]) but I'd much rather like to see the ORM generate that code.

Solution

  • Based on @Brad's suggestion, this does seem to work as alternative:

    MyModel.objects.filter(
    depth=MyModel.objects.order_by('depth').values('depth')[:1]
)

    It essentially becomes this SQL:

    SELECT * FROM table WHERE depth = (SELECT depth FROM table ORDER BY depth LIMIT 1)