duplicated queries

[V1ce]

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.

[James Schneider]

Did you modify your models before sending them out? You don't show any foreign key relationships, but your queries indicate otherwise.

-James

--
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 django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
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/2cb84db9-34ee-4dee-996d-4199a8eb8773%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[V1ce]

Yes sorry, deleted it ^^

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)

    categorie = models.ForeignKey("Categorie")

    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)

    souscategorie = models.ForeignKey('SousCategorie')

    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)

    theme = models.ForeignKey('Theme')

[V1ce]

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.

[James Schneider]

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

--

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 django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
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.

[James Schneider]

###########

# 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

Sorry, I was re-reading the answer and thought I removed all of the comments about hitting the database from your template code. This is the original comment you put in, not one I inserted. I don't believe the template will incur any further database hits if the pre-fetching works.

-James

[V1ce]

That's works really thank you for your time, learned a very good thing today, 1 week im on this X) . Already look a little to Prefetch() function but haven't understand how to use it in my case^^

I have replaced my {% with currscat=categ.getscateg %} by {% with currscat=categ.souscategorie_set.all %} like in your example, that's work "_set.all" return me only "visible" child without more request but i don't understand how that's work, it is supposed to return me all objects, it don't include a filter, Prefetch() function do this?

Thanks for your additionnals advices i'll look at this.

[James Schneider]

That's works really thank you for your time, learned a very good thing today, 1 week im on this X) . Already look a little to Prefetch() function but haven't understand how to use it in my case^^

Don't feel that bad. Prefetch() stumped me for a long time until I saw a post about it on this list a year or so back, and the Django dev team added examples to the documentation which clarified even more.

 

I have replaced my {% with currscat=categ.getscateg %} by {% with currscat=categ.souscategorie_set.all %} like in your example, that's work "_set.all" return me only "visible" child without more request but i don't understand how that's work, it is supposed to return me all objects, it don't include a filter, Prefetch() function do this?

The filter to only retrieve the 'visible' related objects is built into the Prefetch() objects as part of the queryset:

pf_souscategorie = Prefetch('souscategorie_set', SousCategorie.objects.filter(visible=True)) # prefetch only visible SousCategorie's

Here, the PF object is configured to use the query SousCategorie.objects.filter(visible=True) when it pre-fetches all of the related SousCategorie objects. 

When you access the set that was pre-fetched (using the PF object), the .all() will only gather the rows that were pre-fetched and cached in the queryset, and not execute a typical .all() query against the DB (which is the point of pre-fetching, not to run queries later). Since we limited the original query with a PF object, any subsequent categ.souscategorie_set.all() calls use the pre-fetched rows, and hence are filtered.

If you wanted to see all of the objects and have the souscategorie_set.all() work as it does typically (fetching all of the related rows instead of just the visible ones), you can do this instead:

# Note that we do not need the pf_* objects that were created before.

categs = Categorie.objects.filter(visible=True).prefetch_related('souscategorie_set__theme_set__soustheme_set')

That should keep the number of queries the same, but return all of the *_set objects, not just the visible ones (although it will still filter out only the visible Categorie objects, remove the visible=True if you want everything).

 

-James