how to prefetch parents of a child on mptt tree with django prefetch_related?

Question

Say, I've got a product instance. Product instance linked to 4th level child category. If I only want to get root category and 4th level child category the query below is enough to fetch data with minimum database queries:

Product.objects.filter(active=True).prefetch_related('category__root',
                                                     'category')

If I have to reach parents of this product's category and using get_ancestors() method for this, nearly three times mode database query happening.

If I write query like below instead using get_ancestors() method, database queries stays low.

Product.objects.filter(active=True).prefetch_related(
    'category__root',
    'category', 
    'category__parent',
    'category__parent__parent',
    'category__parent__parent__parent',
    'category__parent__parent__parent__parent')

But this query is not effective when level of depth is unknown. Is there a way to prefetch parents dynamically in the query above?

Jolo

Answer 1

Old question, but I'll try to give it a go.

This will require an extra query though. (But that's better than the possible hundreds. - if not more.)

Some explanation;

First: We will need to determine how many levels deep the categories are for the active products.

To avoid the extra query every time, you could cache the following code at startup if categories are static.

max_level = Category.objects.filter(product_set__active=True)\ # Reverse lookup on product
    .values('level')\
    .aggregate(
        max_level=models.Max('level')
    )['max_level']

Second: We will need to create the prefetch string based on the levels. The maximum amount of levels is equal to the maximum amount of parents.

level 0 = no parents
level 1 = 1 parent
level 2 = 2 parents (nested)
level 3 = 3 parents (nested)

This means that we can easily loop over the range of the levels, and append the parent (string) to a list.

prefetch_string = 'category'
prefetch_list = []
for i in range(max_level):
    prefetch_string += '__parent'
    prefetch_list.append(prefetch_string)

Third: We pass in the prefetch_list, also unpacking it.

Product.objects.filter(active=True).prefetch_related(
    'category__root',
    'category', 
    *prefetch_list) # unpack the list into args.

---

We can then easily refactor this into a single dynamic function.

def get_mptt_prefetch(field_name, lookup_name='__parent', related_model_qs=None): 
    max_level = related_model_qs\
            .values('level')\
            .aggregate(
                max_level=models.Max('level')
            )['max_level']
    prefetch_list = []
    prefetch_string = field_name
    for i in range(max_level):
        prefetch_string += lookup_name
        prefetch_list.append(prefetch_string)
    return prefetch_list

And then you can get the prefetch list with:

prefetch_list = get_mptt_prefetch(
    'category',
    related_model_qs=Category.objects.filter(product_set__active=True), # To only get categories which contain active products.
)

https://django-mptt.readthedocs.io/en/latest/technical_details.html#level

Spanjian