Django prefetch_related children of children

Question

I have a model Node that looks something like that:

class Node(models.Model):
    parent = models.ForeignKey('self', related_name='children', on_delete=models.CASCADE)

A Node can have several children, and each of these children can have its own children.

If I do:

def show_child(node):
    for child in node.children.all():
        show_child(child)

root_node = Node.objects.prefetch_related('children').get(pk=my_node_id) # hit database twice, as expected
print("Now testing queries")
root_node.children.all()  # no hit
root_node.children.all()  # no hit
root_node.children.all()  # no hit
root_node.children.all()  # no hit
print("Test 2")
show_child(root_node)  # hit database for every loop except the first

The database gets hit every time I try to access the children of a child.

How could I make it so that it gets the node, its children, the children of its children, etc, in a single database query?

Answer 1

According to the docs you can do this:

Restaurant.objects.prefetch_related('pizzas__toppings')

This will prefetch all pizzas belonging to restaurants, and all toppings belonging to those pizzas. This will result in a total of 3 database queries - one for the restaurants, one for the pizzas, and one for the toppings.

or you can use the Prefetch object to further control the prefetch operation.

from django.db.models import Prefetch

Restaurant.objects.prefetch_related(Prefetch('pizzas__toppings'), queryset=Toppings.objects.order_by('name')))