title: django best practice query foreign key
body: <p>I am trying to understand the best way to structure queries in django to avoid excessive database hits.<br>
  This is similar to the question: <a href="https://stackoverflow.com/questions/5030131/django-best-practice-with-foreign-key-queries">Django best practice with foreign key queries</a>, but involves greater 'depth' in the queries.</p>
  
  <p>My situation:
  models.py</p>
  
  <pre><code>class Bucket(models.Model):
      categories = models.ManyToManyField('Category')
  
  class Category(models.Model):
      name = models.CharField(max_length=50)
  
  class SubCategory(models.Model):
      category = models.ForeignKey(Category)
  
  class SubSubCategory(models.Model):
      subcat = models.ForeignKey(SubCategory)
  </code></pre>
  
  <p>views.py </p>
  
  <pre><code>def showbucket(request, id):
      bucket = Bucket.objects.prefetch_related('categories',).get(pk=id)
      cats = bucket.categories.prefetch_related('subcategory_set__subsubcategory_set',)
      return render_to_response('showbucket.html', locals(), context_instance=RequestContext(request))
  </code></pre>
  
  <p>and relevant template:</p>
  
  <pre><code>{% for c in cats %}
      {{c}}
      &lt;ul&gt;
      {% for d in c.subcategory_set.all %}
          &lt;li&gt;{{d}}&lt;/li&gt;
          &lt;ul&gt;
          {% for e in d.subsubcategory_set.all %}
              &lt;li&gt;{{e}}&lt;/li&gt;
          {% endfor %}
          &lt;/ul&gt;
      {% endfor %}
      &lt;/ul&gt;
  {% endfor %}
  </code></pre>
  
  <p>Despite the use of prefetch_related(), I seem to be hitting the database each time the top two for statements are evaluated, e.g. {% for c in cats %}, (at least I believe so from reading the debug_toolbar).  Other ways I've tried have ended up with (C x D x E) number of database hits.  Is this something inherently wrong with my use of prefetch, queries, or models?  What is the best way in Django to access database objects with a "depth > 1" so-to-speak?</p>