How to perform SQL LEFT JOIN's using Django?

Question

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)

score 6 · Accepted Answer · answered Jan 16 '09 at 15:28

6

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/

answered Jan 16 '09 at 15:28

Tiago

9,457
5
39
35

So there's currently no other way to handle something like this for the exception of dropping into raw SQL? – Ty. Jan 16 '09 at 15:48
I think that this is what you want: http://docs.djangoproject.com/en/dev/topics/db/aggregation/#generating-aggregates-for-each-item-in-a-queryset isn´t it? – Tiago Jan 16 '09 at 15:51
Yes, It's just a pain that I have to updrade to a development version. Thanks for your help. – Ty. Jan 16 '09 at 17:22
If you really prefer to stick with 1.0 for now, you can use this method until you switch to 1.1: http://stackoverflow.com/questions/327807/django-equivalent-for-count-and-group-by#327987 – Carl Meyer Jan 17 '09 at 04:27

score 2 · Answer 2 · answered Jan 17 '09 at 18:02

If you're not using trunk Django (and hence can't use the new aggregation stuff) you can achieve this with a subselect passed to the extra() QuerySet argument. The Django documentation for extra() uses what you're trying to achieve as an example (under the select heading) here.

score 1 · Answer 3 · answered Jan 17 '09 at 17:53

Without the aggregation branch you could do the following which returns a list 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,
)

Just use this in your templates:

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

How to perform SQL LEFT JOIN's using Django?

3 Answers3