Prefetch objects with their nested children

I have this comment model that i need to get all the parent comments and their children, then prefetch it to my main queryset, this is what i got so far:

def recursive_comments(cte):
    return AnalysisComment.objects.filter(
        parent__isnull=True
    ).union(
        cte.join(AnalysisComment, parent_id=cte.col.id),
        all=True,
    )

cte = With.recursive(recursive_comments)
comments = cte.join(AnalysisComment, parent__isnull=True).with_cte(cte).select_related('user__profile').distinct()

qs = self.queryset

qs = qs.prefetch_related(
            Prefetch(
                "comments",
                queryset=comments,
                to_attr="parent_comments",
            ),
        )

I also need to use select_related('user__profile') on each comment object. this is my Comment model:

class AnalysisComment(TimeStampMixin):
    """
    Represents comments made on Analysis.
    """
    parent = models.ForeignKey(
        "self", on_delete=models.CASCADE, null=True, blank=True, related_name="children"
    )
    uuid = models.UUIDField(
        verbose_name=_("UUID"),
        max_length=36,
        help_text=_("Unique identifier."),
        default=uuid.uuid4,
    )
    user = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        verbose_name=_("User"),
        on_delete=models.CASCADE,
        related_name="analyses_comments",
    )
    text = models.TextField(_("Text"))
    analysis = models.ForeignKey(
        Analysis,
        verbose_name=_("Analysis"),
        related_name="comments",
        on_delete=models.CASCADE,
    )
    objects = CTEManager()

now the problem is when i call the one of my parent comment children -> qs[0].parent_comments[0].children.all() then it calls queries again. Can you help me how can i fix that?

dimagi / django-cte

Prefetch objects with their nested children #82