Django MPTT get descendants subquery

Question

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:

Django==3.2.19
django-mptt==0.11.0

@exprator Your revised solution raises `'OuterRef' object has no attribute 'model'` error. I suspect it is because the expected first argument of `get_queryset_descendants()` is a queryset of Model1. I'll edit to add the versions I use. — Tenshiro, Jun 29 '23 at 02:35

score 0 · Accepted Answer · answered Jul 17 '23 at 09:57

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),
)

Django MPTT get descendants subquery

1 Answers1