How to build witt django-mptt a query joining to any descendant of mptt hierarchy element?

Question

I want to build with django-mptt a hierarchy of vegetables classes (Fruit -> Berries -> Strawberry -> Douglas Strawberry) being the last level the variety. Retailer stocks are often related to the leaf level, the variety (Golden Apple, Douglas Strawberry...), but consumers are used to ask about upper level (Apple, Pear, Orange, Lettuce...). So a useful query wold be "select all the retailers having a stock product of 'Strawberry' or any children.

Let me illustrate it with some example code:

class Vegetable(MPTTModel) :
    name = TextField(...)
    group = TreeForeignKey('self',
        related_name='subgroups',
        null=True, default=None)
    class MPTTMeta:
        parent_attr = 'group'

    def getRetailers(self) :
        # Here the missing code

class Retailer(Model) :
    name = TextField(...)
    vegetables = ManyToMany(Vegetable, null=True)

fruits = Vegetable(name='Fruit')
pears = Vegetable(name='Pear', group=fruits)
apples = Vegetable(name='Apple', group=fruits)
goldenApples = Vegetable(name='Golden Apple', group=apples)
royalApples = Vegetable(name='Royal Apple', group=apples)
for o in fruits, pears, apples, goldenApples, royalApples : o.save()

toni = Retailer(name='Toni')
pere = Retailer(name='Pere')
benet = Retailer(name='Benet')
mall = Retailer(name='CityMall')
for o in toni, pere, benet, mall : o.save()

toni.vegetables.add(pears)
pere.vegetables.add(goldenApple)
pere.vegetables.add(royalApple)
benet.vegetables.add(goldenApple)
mall.vegetables.add(apples)

# The following query set should return Pere, Benet and CityMall but not Toni.
Vegetable.get(name='Apple').getRetailers()

So how should I build such a query with the Django API?

Answer 1

You can get a query set of all MPTTModel descendants with get_descendants:

Creates a QuerySet containing descendants of this model instance, in tree order.

If include_self is True, the QuerySet will also include this model instance.

So for a particular vegetable:

# get retailers where...
retailers = Retailers.objects.filter(
    # ...one or more of it's vegetables are in the list of
    # all things descendant of 'vegetable', including itself.
    vegetables__in=vegetable.get_descendants(include_self=True)
).distinct()