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

Question

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.

Queries are not correlated thus you can compute min by a separate query the do your filtering. And this kind of filter can be called `HAVING` which can be found in SO answers like https://stackoverflow.com/questions/20330879/django-queryset-adding-having-constraint Don't `Min('depth')` your top-level query, you need a separate _subquery_. — Ivan Starostin, Apr 04 '23 at 10:08
> " and include the m value in the main results as that's what annotate does. " For this particular problem, you can use [alias](https://docs.djangoproject.com/en/4.1/ref/models/querysets/#alias) instead. It won't fix the "GROUP BY" issue though — Lotram, Apr 04 '23 at 13:01

score 0 · Answer 1 · answered Apr 12 '23 at 14:38

0

This should work

MyModel.objects.filter(
    depth=MyModel.objects.annotate(min_depth=Min('depth)).values('min_depth')[:1]
)

Using annotate instead of aggregate will keep the queryset lazy.

answered Apr 12 '23 at 14:38

Brad Martsberger

1,747
13
7

That looks pretty close, but `print(qs.query)` reveals that `.annotate()` will add `GROUP BY id` here, so it will just pick the first one. However, `MyModel.objects.filter(depth=MyModel.objects.order_by('depth').values('depth')[:1)` does work then! – vdboor May 01 '23 at 12:40

score 0 · Accepted Answer · answered May 01 '23 at 12:42

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)

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

2 Answers2