pythondjangosubquerympttdescendant

Django MPTT get descendants subquery


I am trying to get the same queryset I would get if I used get_queryset_descendants(queryset, include_self=True) without losing the queryset (keeping it as a subquery). Because of how get_queryset_descendants works, the queryset is being resolved and is not being a part of a subquery.

If I do this, Model.objects.filter(id=1) is not being used as a subquery - I won't be able to use OuterRef("pk")

descendants = Model.tree.get_queryset_descendants(
    Model.objects.filter(id=1),
    include_self=True,
)

print(str(descendants.query))  # id 1 is not part of query

Here's a sample of the models I use

import django
import mptt


class Model1(mptt.models.MPTTModel):
    objects = django.db.models.Manager()
    tree = mptt.managers.TreeManager()


class Model2(django.db.models.Model):
    model1 = django.db.models.ForeignKey(Model1)

I'd like to do something like this:

from django.db.models import (
    Count,
    IntegerField,
    Subquery,
)


model1_with_visible_model2 = Model1.objects.annotate(
    visible_model2_count=Subquery(
        (
            Model2.objects
            .filter(
                model1__id__in=(  # instead of just model1=OuterRef("pk")
                    Model1.tree.get_queryset_descendants(
                        Model1.objects.filter(
                            # id=6347,  # No error
                            id=OuterRef("pk"),  # This is my goal
                        ).all(),
                        include_self=True,
                    )
                ).values_list("id", flat=True),
                is_visible=True,
            )
            .distinct()
            .values("model1")
            .annotate(count=Count("pk"))
            .values("count")
        ),
        output_field=IntegerField(),
    ),
)

I am not using model1=OuterRef("pk") because model2 instances (even though not directly related to the parent model1 instance) should still be counted as long as they are related to a descendant of the parent model1 instance.

Versions I use:


Solution

  • I've studied how get_queryset_descendants() works and found out that I could use the left, right, and tree_id fields to get the same queryset outcome.

    from django.db.models import Exists, OuterRef
    
    model1_with_visible_model2 = Model1.objects.annotate(
        visible_model2_exists=Exists(
            Model2.objects
            .filter(
                model1__id__in=(
                    Model1.objects
                    .filter(
                        tree_id=OuterRef(OuterRef("tree_id")),
                        lft__gte=OuterRef(OuterRef("lft")),
                        rght__lte=OuterRef(OuterRef("rght")),
                    )
                ).values_list("id", flat=True),
                is_visible=True,
            ),
        ).filter(visible_model2_exists=True),
    )