我如何将此SQL查询转移到Django orm代码

Question

模型(pk/id自动生成)

class Comments(models.Model):
parent = models.ForeignKey(to="self", null=True)

和SQL查询

SELECT 
*
FROM
comments
WHERE
(
parent_id IN ( 1, 2, 3, 4 ) 
AND
( SELECT COUNT(*) FROM comments AS f WHERE ( f.parent_id = comments.parent_id AND f.id <= comments.id ) )<= 2 
)

Answer 1

我们可以借助Subquery来确定计数:

from django.db.models import Count, OuterRef, Q, Subquery, Value
from django.db.models.functions import Coalesce
Comments.objects.filter(
parent_id__in=[1,2,3,4]
).annotate(
ncomment=Coalesce(Subquery(
Comments.objects.filter(
parent_id=OuterRef('pk'),
pk__lte=OuterRef('pk')
).values('parent_id').annotate(
ncomment=Count('pk')
).values('ncomment').order_by('parent_id')
), Value(0))
).filter(
ncomment__lte=2
)