The reason that you are seeing multiple hits on the database is due to the
way that your template loops are built. The {% with %} statements are
calling a method on your model, which is returning a fresh queryset for
every iteration of the for loop (which is then compounded by the inner {%
with %} calls and more inner {% for %} loops. The fresh querysets are then
evaluated, causing excessive (and unnecessary) database hits. Any
prefetch_related() action within your view would result in the behavior
you've seen, only adding more database queries to pull data that is never
used since you are requesting fresh querysets on every loop in the template.I believe what you'll need to do is filter and prefetch all of the rows from the various models using Prefetch() objects. Let's see if I can work this out, YMMV, I'm typing this as I go based on the models you sent out almost 24 hours ago: ########### # view code - see https://docs.djangoproject.com/en/1.8/ref/models/querysets/#prefetch-related section regarding the Prefetch object ########### # add this to your imports from django.db.models import Prefetch # Note that these Prefetch() objects act as your filters for the related objects, so we don't need to add them to the Categorie filter pf_souscategorie = Prefetch('souscategorie_set', SousCategorie.objects.filter(visible=True)) # prefetch only visible SousCategorie's pf_theme = Prefetch('souscategorie_set__theme_set', Theme.objects.filter(visible=True)) # prefetch only visible Theme's pf_soustheme = Prefetch('souscategorie_set__theme_set__soustheme_set', SousTheme.objects.filter(visible=True)) # prefetch only visible SousTheme's # Here were filtering on visible Categorie's, related objects are filtered by the Prefetch() objects above categs = Categorie.objects.filter(visible=True).prefetch_related(pf_souscategorie, pf_theme, pf_soustheme) ########### # template code - I removed the blank lines and reduced the indentation for readability in an email. ########### {% for categ in categs %} {% with currscat=categ.souscategorie_set.all %} <li class = "{% if currscat %} has_menu {% endif %}"> {{categ.nom}} {% if currscat %} # This line hit database <ul class = "menu-1"> {% for souscateg in currscat %} {% with currth=souscateg.theme_set.all %} <li class="{%if currth %} has_menu {%endif%}"> {{souscateg.nom}} {% if currth %} # this line hit database <ul class = "menu-2"> .......... {# Assuming you would loop through currth here to get all of the soustheme's, use same strategy as above, otherwise, remove the reference to pf_soustheme above in the view #} </ul> {% endif %} </li> {% endwith %} {% endfor %} </ul> {% endif %} </li> {% endwith %} {% endfor %} Hopefully that works out for you. I did test Prefetch() against multiple levels of reverse relationships in my own project, and it appeared to work as expected. Another related but tangent thought: Your parent and child models (ie Theme and SousTheme) appear to be the same model, but one is considered a 'child' of the other purely by virtue of the FK relationship. You could potentially eliminate the 'Sous' models and use a M2M relationship with 'self' in asymmetric mode to capture the parent/child relationship. It would save you a table and a reverse relationship query in situations like this. It would also allow you to add more levels to the relationship (parents, children, grandchildren, and so on). Just a thought. https://docs.djangoproject.com/en/1.8/ref/models/fields/#django.db.models.ManyToManyField.symmetrical You could also look at implementing an MPTT model, which is often used for hierarchical objects like categories. http://django-mptt.github.io/django-mptt/overview.html#what-is-modified-preorder-tree-traversal as an example. HTH, -James On Sun, Nov 8, 2015 at 3:46 AM, V1ce <[email protected]> wrote: > > I post here my real models for better comprehension (and have cleaned it > this morning with an abstract class) > > class Rubrique(models.Model): > nom = models.CharField(max_length=30) > slug = models.SlugField(max_length=100) > page = models.CharField(max_length=50, null=True) > description = RichTextField(null=True, blank = True) > metaDesc = models.TextField("Balise meta description",null=True, blank > = True) > position = models.PositiveSmallIntegerField("Position",default = > 0,null=True, blank = True) > visible = models.BooleanField("Visible",default = False) > redirect = models.CharField("Redirection",max_length=200, null=True, > blank = True) > created = models.DateTimeField(_('Date de création'),editable=False) > modified = models.DateTimeField(_('Dernière > modification'),editable=False) > > def __str__(self): > return self.nom > > class Meta(object): > abstract = True > ordering = ('position',) > > class Categorie(Rubrique): > develop = models.BooleanField(_('develop?'), default=True, blank=True) > is_clickable = models.BooleanField(_('clickable?'), default=False, > blank=True) > > class Meta(Rubrique.Meta): > pass > > def getscateg(self): > #return self.souscategorie_set.all().filter(visible = True) > return SousCategorie.objects.filter(visible = True, categorie = > self).only('nom','slug','is_clickable','develop') > > > class SousCategorie(Rubrique): > > is_clickable = models.BooleanField(_('clickable?'), default=False, > blank=True) > develop = models.BooleanField(_('develop?'), default=False, blank=True) > categorie = > models.ForeignKey(Categorie,verbose_name='Catégorie',null=True,on_delete=models.SET_NULL) > > class Meta(Rubrique.Meta): > pass > > def gettheme(self): > return Theme.objects.filter(visible = True, souscategorie = > self).only('nom','slug','is_clickable') > #return self.theme_set.all().filter(visible = True) > > > class Theme(Rubrique): > is_clickable = models.BooleanField(_('Clickable?'), default=False, > blank=True) > souscategorie = > models.ForeignKey('SousCategorie',verbose_name='Sous-catégorie',null=True,blank=True,on_delete=models.SET_NULL) > > class Meta(Rubrique.Meta): > pass > > def getstheme(self): > return SousTheme.objects.filter(visible = True, theme = > self).only('nom','slug') > #return self.soustheme_set.all().filter(visible=True) > > class SousTheme(Rubrique): > theme = models.ForeignKey('Theme',verbose_name='Thème', null=True, > blank = True,on_delete=models.SET_NULL) > > class Meta(Rubrique.Meta): > pass > > Actually it is normal if i have duplicate, because at each "Categorie" > loop in template i load different data, but if i can optimize this with 1 > request i think will better, i look at cache funcionnality too for visitor > not have to loads these data at each pages (because this menu is here in > all pages of the website), but its an other thing. > > > Le samedi 7 novembre 2015 17:37:17 UTC+1, V1ce a écrit : >> >> Hi, >> >> I got a lot of duplicated queries (in django debug toolbag) when i load >> my menu tabs , actually it's normal because i don't understand how to use >> database optimization for this, if possible i want some advice to make my >> reflexion better on this. >> >> Models(simplify) : >> >> class Categorie(models.Model): >> nom = models.CharField(max_length=30) >> slug = models.SlugField(max_length=100) >> visible = models.BooleanField("Visible",default = False) >> >> def getscateg(self): >> >> return self.souscategorie_set.all().filter(visible = True) >> >> >> class SousCategorie(models.Model): >> nom = models.CharField(max_length=30) >> slug = models.SlugField(max_length=100) >> visible = models.BooleanField("Visible",default = False) >> >> def gettheme(self): >> >> return self.theme_set.all().filter(visible = True) >> >> >> class Theme(models.Model): >> nom = models.CharField(max_length=100) >> slug = models.SlugField(max_length=100) >> visible = models.BooleanField("Visible",default = False) >> >> def getstheme(self): # Récupère les thèmes en fonction de la >> sous-categ traité >> >> return self.soustheme_set.all().filter(visible=True) >> >> >> class SousTheme(models.Model): >> nom = models.CharField(max_length=100) >> slug = models.SlugField(max_length=100) >> visible = models.BooleanField("Visible",default = False) >> >> >> views : >> >> >> def page(request): >> >> categs = Categorie.objects.filter(visible=True) >> >> return render(request, 'page.html', locals()) >> >> templates (simplify) : >> >> {% for categ in categs %} >> {% with currscat=categ.getscateg %} >> >> <li class = "{% if currscat %} has_menu {% endif %}"> {{categ.nom}} >> # This line hit database >> >> {% if currscat %} # This line hit database >> >> <ul class = "menu-1"> >> >> {% for souscateg in currscat %} >> {% with currth=souscateg.gettheme %} >> >> <li class="{%if currth%} has_menu {%endif%}"> >> {{souscateg.nom}} #This line hit database >> >> >> {% if currth %} # this line hit database >> >> >> <ul class = "menu-2"> >> >> .......... >> >> </ul> >> >> {% endif %} >> >> >> </li> >> >> >> {% endwith %} >> {% endfor %} >> </ul> >> >> {% endif %} >> >> </li> >> >> {% endwith %} >> {% endfor %} >> >> >> I think prefetch_related can help me here but i don't find to reduce >> resquest to database with it in this case... So after my template rendered >> i got like 50 request including 40 duplicate... >> >> i have try something like this in my views but without success, that just >> add more request to database : >> >> categs = >> Categorie.objects.filter(visible=True).prefetch_related('souscategorie_set__theme_set__soustheme_set') >> >> Thanks for your help. >> > -- > You received this message because you are subscribed to the Google Groups > "Django users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/django-users. > To view this discussion on the web visit > https://groups.google.com/d/msgid/django-users/271ec045-3cfc-4de7-8da5-453eca3d692e%40googlegroups.com > <https://groups.google.com/d/msgid/django-users/271ec045-3cfc-4de7-8da5-453eca3d692e%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/django-users. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CA%2Be%2BciVv3rbCaHfvwcrUke7Gx03ByEpSQAAu320R9-OUMUKpfA%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
