Django ORM如何在WHERE语句中执行聚合子查询?

huangapple go评论59阅读模式
英文:

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 lose.
  • 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.

英文:

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.

答案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)

huangapple
  • 本文由 发表于 2023年4月4日 17:53:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/75927965.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定