Oh Django, how do I love thee? Let me count the joins ...

Jul 25, 2008 Django

Let's say I have three simple models - news articles, their categories, and the table that defines which articles are in which categories:

class Article(models.Model):
    title = models.CharField(max_length=40, unique=True)
    body = models.TextField()

class Category(models.Model):
    name = models.CharField(max_length=40, unique=True)

class ArticleCategory(models.Model):
    article = models.ForeignKey(Article)
    category = models.ForeignKey(Category)
To get a list of articles in a specific category, I'd need a SQL select that looks something like this:
    SELECT * 
    FROM myapp_article 
    JOIN myapp_articlecategory ON (myapp_article.id = myapp_articlecategory.article_id)
    JOIN myapp_category ON (myapp_category.id = myapp_articlecategory.category_id)
    WHERE myapp_category.id = 2
Instead, using Django's QuerySet filter method and double underscore joining syntax, the same select looks like this in my view:
            article_list = Article.objects.filter(articlecategory__category=category_id)
Django's QuerySet methods magically trace back through the ForeignKey relationships to join on the appropriate ids. Well, to be fair, there's slightly more to it than that - here's all it takes to return a filtered (or not) list of articles:
def list_articles(request, category=None):
    template_name = 'list_articles.html'
    category_list = Category.objects.all().order_by('name') # this is for the list of category names to select from in the template

    if request.method == 'POST':
        category_id = request.POST['category']
        try:
            # here are the joins
            article_list = Article.objects.filter(articlecategory__category=category_id)
        except ObjectDoesNotExist:
            article_list = None
    else:
        # if there's no category_id passed in, just return the whole list
        article_list = Article.objects.all().order_by('-created_at')

    return render_to_response(template_name, { 'article_list': article_list, 'category_list': category_list, }, context_instance=RequestContext(request))
And here's a condensed version of the template:
{% block title %}Article List{% endblock %}

{% block content %}

<form method="post" action="">
{% if category_list %}
    <select name="category">
    <option value="">-- Select a Category --</option>
    {% for category in category_list %}
        <option value="{{ category.id }}">{{ category.title }}</option>
    {% endfor %}
    </select>
{% endif %}
<input type="submit" value="Filter" />
</form>

{% if article_list %}
    <ul>
    {% for article in article_list %}
        <li>{{ article.id }} - {{ article.title }}</li>
    {% endfor %}
    </ul>
{% else %}
    <p>No articles available in that category</p>
{% endif %}

{% endblock %}