Postgresql – How to perform SQL LEFT JOIN’s using Django

databasedjangoperformancepostgresql

Essentially I need a count of each Entries Comments:

SELECT e.*, COUNT(c.id) as comments FROM blog_entry e LEFT JOIN blog_comment c ON e.id = c.entry_id GROUP BY e.id, e.name, e.name_slug, e.date_published, e.category, e.image, e.body, e.is_published, e.views, e.subscription_sent ORDER BY e.date_published DESC LIMIT 15;

But I don't know how to go about this using Django.

This is what I have so far, it works perfectly, except there's no comment count. Can someone point me in the correct direction for making joins like this using Django?

from project.blog.models import Entry, Comment

def index(request):
    latest_entry_list = Entry.objects.filter(is_published=True).order_by('-date_published')[:15]
    return render_to_response('blog/index.html', {'latest_entry_list': latest_entry_list)

Best Solution

django 1.1 have support for aggregate queries, you can grab the last version via the svn trunk. The doc is already updated

http://docs.djangoproject.com/en/dev/topics/db/aggregation/