How can I use prefetch_related in self related model

Question

I have Menu model which has ForeignKey named parent related with itself.

If parent is None it means this menu is parent menu if it shows another Menu object it means it is submenu for its parent(many-to-one relation)

Here is my problem, I want to get all menus with its submenus using prefetch_related, How can I do it?

Note: I do not want to get submenus going database each time in for menu

Here is my model class

class Menu(models.Model):
    title = models.CharField(max_length=30)
    language = models.ForeignKey(Language)
    parent = models.ForeignKey("self", default=None, blank=True,
                               null=True, related_name="submenus")
    

Here is my query

pm2 = Menu.objects.filter(parent=None, language__code=language, menutype=menutype)
          .prefetch_related("submenus").order_by("order")
for p in pm2:
    print(p.title)
    print(p.submenus)

When I print the submenus the result is app.Menu.None

An

Your model doesn't mention submenus at all, so I would expect .prefetch_related("submenus") to give an error. Will your submenus have their own submenus, or will your menus only have two levels? If you have multiple levels, then you may want to look at django-mptt. – Breathy

django-mptt is hopelessly convoluted to work with. I would suggest using materialized paths instead (e.g. communities.bmc.com/docs/DOC-9902) – Cystoscope

Was this issue ever resolved? – Marillin

@Marillin It wasn't an issue at all. Please check my answer to see why. – Emmons

queryset = Menu.objects.filter(parent=None, language__code=language, menutype=menutype).order_by("order").prefetch_related('submenus', 'submenus__submenus', 'submenus__submenus__submenus', "submenus__submenus__submenus__submenus") – Dynamometry

Answer 1

You don't need to use prefetch_related since this is used for many-to-many relationships, instead, you can use select_related.

So your query would be

pm2 = Menu.objects.filter(
    parent=None, language__code=language, menutype=menutype
).select_related(
    "submenus"
).order_by(
    "order"
)

Comedietta

Sorey for late response. In here i think i need to use prefetch_related because i want to get submenus and submenus is the reverse ForeignKeys "parent" field. – Scalable

Answer 2

Since you are working with a tree (by using an Adjacency List), then neither select_related nor prefetch_related will work correctly if you have more than a single level of parent-child relationships. Instead, you will need something that materialises the tree.

https://pypi.org/project/django-closure-tree/ provides a framework for building Postgres views that will handle this type of construct, and gives you some tools for making querying this simpler.

The actual code behind this is surprisingly simple: https://schinckel.net/2016/01/27/django-trees-via-closure-view/

Sassafras

Answer 3

When I print the submenus the result is app.Menu.None

This is happening because the OP is printing print(p.submenus) which is just a RelatedManager. It can be verified by checking the type of p.submenus:

# this is what it prints in django 1.11
>>> type(p.submenus)                                                                                                                                                                                                               
django.db.models.fields.related_descriptors.create_reverse_many_to_one_manager.<locals>.RelatedManager

To access a queryset from the m2m relation, the correct way is to use .all():

print(p.submenus.all())

Emmons

did u answer this question? i think you post wrong question – Dynamometry

@Dynamometry Yes, I have answered the question. I am sorry but I don't follow what you mean by i think you post wrong question. – Emmons

Answer 4

The reason you are seeing app.Menu.None is because you are trying to print a related manager object submenus directly. You can't print a manager object directly, you need to iterate over it to access the related objects (in this case, the submenus).

pm2 = Menu.objects.filter(parent=None, language__code=language, menutype=menutype).prefetch_related("submenus").order_by("order")
for p in pm2:
    print(p.title)
    for submenu in p.submenus.all():  # Iterate over the related objects
        print(submenu.title)  # Print the title of each submenu

In the modified code, p.submenus.all() retrieves all the submenus related to a particular menu p. The all() function is used with a manager to retrieve all objects. Since submenus is a related manager (as specified by the related_name argument in the ForeignKey field), you use all() to retrieve all related submenu objects.

The prefetch_related("submenus") ensures that Django fetches all related submenus in a single query rather than hitting the database for each individual submenu, improving performance when you have many menus.

Cienfuegos

Answer 5

For Example u have 4Level self u need code like this :

queryset = Menu.objects.filter(parent=None, language__code=language, menutype=menutype).order_by("order").prefetch_related('submenus', 'submenus__submenus', 'submenus__submenus__submenus', "submenus__submenus__submenus__submenus")

Dynamometry

That looks like a terrible idea. What if there are 5 levels? – Smectic