Query DB-objects over more than two models

Using Django
1

At first, I want to say: I like Django. Many of my queries are working, but the Relations over more models killing me.

Before I copy the hole models.py of my Testproject, here is the (workling) SQL-Command. I could do this with rawSQL, but I want to know, if there is any way to get it with Django methods. (I read some API documentations before, the Tutorial I tried first, but I cant find an answer.)

SELCT DISINTCT eatingSpecials.specialDesc
FROM menu INNER JOIN menu_components USING(menuID)
	INNER JOIN component USING(componentID)
	INNER JOIN component_ingredient USING(componentID)
	INNER JOIN ingredient USING(ingredientID)
	INNER JOIN ingredient_specials USING(ingredientID)
	INNER JOIN eatingSpecials USING(specialID)
WHERE menuID=1;

In prosa: Each menu has one or many components (i. e. a soup, a salad, a meat-meal …), which contains out of one or many ingridients (different vegetables, rice, noodles and so on), which correlate with no, one or many incompatiblities (i. e. allergies or religios rules).

The models are working, I can call objects of one model or direct related objects. This is fine. What I want is a list of all menues and all of this incompatibilities.

I get this, if I querie one specific menu-object, call after this all components, iterate them, to call all ingredients, to iterate them too, to get all eatingSpecials, they are related to the menu-object.

views.py

m = menu.objects.get(id=1)
component = m.component.all() # (<<-- context)

template.html (I know, it is not nice at all to put so much logic in template, but in this way it doesn’t look nicer, if I wrote it in a context-dictonary)

{% for c in component %}
	{% for s in c.ingredient.all %}
		{% for e s.eatingSpecials.all %}
			{{ e.specialDesc }}
		{% endfor %}
	{% endfor %}
{% endfor %}

It works, but … It isn’t nice. For a menu with two components and nine ingredients I hit the DB over ten times. For one menu.

Is there any method, I can query more than just the “neighbour”-object with one Django query?

2

Have you tried using select_related or prefetch_related? Each will do an automatic JOIN for you to fetch things in a single query.

I can’t give you precise instructions since you didn’t post the models, but it looks like you would need something like component.objects.prefetch_related("ingredient").

3

I tried select_related, but I think I was too frustrated, to read the upcomming error exactly and read the API description. select_related wont help me, because this all are ManyToManyRelationships.

So I need prefetch_related, so I have to say “Thank you very much” :slight_smile:

Solution for the hole use case:
menu.objects.prefetch_related(‘componentID__ingredientID__specialID’)

This are 4 Database hits, but better than more then 10.
Now I only need to find out, how to call the components, but I try first on my own, before I ask again :wink: