django_cte_recursive,py

from django.db import models


class User(models.Model):
    id = models.AutoField(primary_key=True)
    parent = models.ForeignKey("self", null=True, blank=True, on_delete=models.CASCADE)

    def get_tree_users_recursive(self) -> "models.QuerySet[User]":
        return User.objects.extra(
            where=[
                f"""
id in (
    WITH RECURSIVE cte AS (
        SELECT id FROM `{User._meta.db_table}` WHERE id = %s
        UNION ALL
        SELECT t.id FROM `{User._meta.db_table}` t
        INNER JOIN cte ON t.parent_id = cte.id
    )
    SELECT id FROM cte
)
"""
            ],
            params=[self.id],
        )

    def get_parent_users_recursive(self) -> "models.QuerySet[User]":
        return User.objects.extra(
            where=[
                f"""
id in (
    WITH RECURSIVE cte AS (
        SELECT id, parent_id, 0 __level FROM `{User._meta.db_table}` WHERE id = %s
        UNION ALL
        SELECT t.id, t.parent_id, (__level + 1) __level FROM `{User._meta.db_table}` t
        INNER JOIN cte ON cte.parent_id = t.id
    )
    SELECT id FROM cte order by __level
)
"""
            ],
            params=[self.id],
        )