Friday, 1 December 2023

Recursive Query with Django-CTE

I have a Tree model that i need to prefetch all the childs and user of the comments all at once:

class AnalysisComment(MPTTModel, TimeStampMixin):
    parent = TreeForeignKey(
        "self", on_delete=models.CASCADE, null=True, blank=True, related_name="children"
    )
    user = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        verbose_name=_("User"),
        on_delete=models.CASCADE,
        help_text=_("User who created the blog."),
        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 this is how I'm getting the analyses with comments:

def recursive_comments(cte):
    return (
        AnalysisComment.objects.filter(level=0)
        .union(
            cte.join(AnalysisComment, parent=cte.col.id),
            all=True,
        )
    )

cte = With.recursive(recursive_comments)

comments = cte.join(AnalysisComment, id=cte.col.id).with_cte(cte)

qs = Analysis.objects.prefetch_related(
    "tags",
    "playlists",
    Prefetch("comments", queryset=comments, to_attr="parent_comments"),
)

and i call the childs with obj.child.all() in my serializers. now there are a lot of duplicate objects in the response. and also calls a query for each comment:

enter image description here



from Recursive Query with Django-CTE

No comments:

Post a Comment