英文:
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 aGROUP BY id
that seems hard to lose. - a subquery with
Model.objects.annotate(m=Min('depth')).filter(depth=F('m'))
will give aGROUP BY id
and include them
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.
英文:
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 aGROUP BY id
that seems hard to loose. - a subquery with
Model.objects.annotate(m=Min('depth')).filter(depth=F('m'))
will give aGROUP BY id
, and include them
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.
答案1
得分: 0
使用annotate而不是aggregate将保持查询集为惰性。
英文:
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.
答案2
得分: 0
根据 @Brad 的建议,这似乎可以作为替代方法:
MyModel.objects.filter(
depth=MyModel.objects.order_by('depth').values('depth')[:1]
)
它本质上变成了这个 SQL 查询:
SELECT * FROM table WHERE depth = (SELECT depth FROM table ORDER BY depth LIMIT 1)
英文:
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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论