Filtering then Iterating through nested django models

Question

I've got some nested models

class Category(models.Model):
    name = models.CharField()

class FacetQuestion(models.Model):
    category    = models.ForeignKey(Category)
    description = models.CharField()

class FacetAnswer(models.Model):
    question = models.ForeignKey(FacetQuestion)
    answer   = models.CharField()
    subject  = models.SmallIntegerField(default=1)

I can efficiently get all the answers/questions/categories at least in the view:

def detail(request, id):
    facets= models.Category.objects.
    filter(facetquestion__facetanswer__subject='test').
    select_related()
    return render_to_response('test.tpl',
                              dict(facets=facets,
                                   STATIC_URL = settings.STATIC_URL,
                                   MEDIA_URL  = settings.MEDIA_URL),
                              context_instance=RequestContext(request))

But when I loop though them in a template, I get everything (not just what I filtered on), because the template makes additional queries for all questions and all answers. So the template code is definitely bogus:

{% for category in answers %}
        {% for q in category.facetquestion_set.all %}
            {% for a in q.facetanswer_set.all %}
            {% endfor %}
        {% endfor %}
{% endfor %}

What's a good pattern for showing a nested set of models in logical order?

Category
  Question
    Answer (subject=test)
    Answer (subject=test)
  Question
    Answer (subject=test)
Category
  Question
    Answer (subject=test)

I am using django 1.3. I asked a similar question here, but that one did not include filtering by subject.

Answer 1

I do not know how to solve your problem, but I think I can tell you where you went wrong.

With this line

answers = models.Category.objects.filter(facetquestion__facetanswer__subject=2)

you are selecting all Categories that have one or more questions which have on or more answers that have a subject == 2.

When you iterate over the selected categories like this:

{% for category in answers %}
    {% for q in category.facetquestion_set.all %}
        {% for a in q.facetanswer_set.all %}

in each loop, you issue another query that fetches all questions in the category (category.facetquestion_set.all) and all answers to that question (q.facetanswer_set.all). Regardless of filters.

You can use this snippet to print the SQL in DEBUG mode and get an idea of what is going on behind the scenes: http://djangosnippets.org/snippets/93/

...and in this case, it will print lots of SELECT, I guess.