# How you can perform SQL LEFT JOIN's using Django?

Basically I want a count of every Records 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'm not sure how to pull off this using Django.

This is exactly what I've to date, it really works perfectly, except there is no comment count. Can someone point me within the correct direction to make joins such as 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)


django 1.1 have support for aggregate queries, you are able to grab the final version through the svn trunk. The doc has already been up-to-date

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

If you are not using trunk Django (and therefore can't make use of the new aggregation stuff) you are able to accomplish this having a subselect passed towards the extra() QuerySet argument. The Django documentation for added() uses what you are attempting to achieve for example (underneath the choose heading) here.

With no aggregation branch you could do this the next which returns a listing of 2tuples:

  from project.blog.models import Entry, Comment

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


Only use this inside your templates:

{% for entry in latest_entry_list %}
Entry: {{entry.0}}
Comment count: {{entry.1}}
{% endif %}