Using django-mptt to get only the categories that have items

Question

Sample structure:

ALL UPPERCASE: Category

Mixed Case: Item

ROOT ├── BOOKS │   ├── FICTION │   │   └── CLASSICS │   └── NON-FICTION ├── CLOTHING └── ELECTRONICS ├── LAPTOPS ├── PHONES │   ├── APPLE │   │   ├── iPhone 6 │   │   ├── iPhone 6 Plus │   │   ├── iPhone 6S │   │   └── iPhone 6S Plus │   ├── MOTOROLA │   │   ├── Moto G4 │   │   ├── Moto G4 Play │   │   ├── Moto G4 Plus │   │   └── Moto X │   └── SAMSUNG └── TABLETS └── APPLE

I'm trying to have my index page only show the "ELECTRONICS" category. The "CLOTHING" category shouldn't show up since it's empty and the "BOOKS" category shouldn't show up either since, although it has child categories, the child categories don't have any items.

Similarly, the "ELECTRONICS" page should only show the "PHONES" category. The "LAPTOPS" category shouldn't show up since it's empty and the "TABLETS" category shouldn't show up either since, although it has a child category, that child category doesn't have any items.

Similarly, the "PHONES" page should only show the "APPLE" and "MOTOROLA" categories since the "SAMSUNG" category is empty.

What I've tried for the index page:

Category.objects.root_nodes().exclude(children__isnull=True)

This successfully excludes the "CLOTHING" category, but it does not exclude the "BOOKS" category. I understand why this doesn't work, but I don't know what to do instead.

Answer 1

I don't think this is possible using just django-mptt manager methods and fields.

You could come up with a query that filters all categories with items (think something like SELECT DISTINCT category_id FROM items) and then find out the set of root nodes of those categories.

The bad news is that probably your query won't perform very well for the use case you described, maybe you can afford having some denormalized field (maybe something like subtree item count) or use a search engine like elasticsearch for this kind of tasks.