If I have a Blog model object in Django, and it has many Comment objects, then what's the most efficient way of getting hold of the comment count alongside a list of blog entries? I was thinking I'd have to fall back on raw SQL, but there's an easier way.

Inefficient

The most inefficient way is to use the ORM directly:

{{BlogEntry.blogcomment_set.count}} comments

This will cause a query to fire for each row in the results

Django

The best way is to augment the object using the ORM:

lBlogEntries = BlogEntry.objects.all().extra(select=
  {'comment_count': 'SELECT count(*) FROM blog_blogcomment WHERE blog_blogcomment.blog_entry_id=blog_blogentry.id'},)

and then in the template

{{BlogEntry.comment_count}} comments

This adds extra clauses into the SQL so only one SQL statement is executed to get the list and counts.