How can I transfer this SQL query to Django orm code-CodePudding

The model(the pk/id is auto generated)

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

And the SQL query

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 
  )

CodePudding user response：

We can determine the count with the help of a 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
)