Beginning Django: performing sub-selects and other complex queries

Question

Hello,

I want to execute a semi-complex query in Django. For example I want something that is like this:

SELECT 
b.*,
(SELECT count(id) FROM comments c WHERE c.blog_id = b.id) AS number_of_comments
FROM blog b 
WHERE 1

From my PHP background, Code Igniter and Zend Framework has "query builders". Where you can built an SQL-query using the methods in the framework. Is this something like in Django?

What would be the best way to build and execute complex queries in Django? Is there a recommended way / best-practice to do these kinds of queries?

UPDATE:

I got it working with little changes thanks to mherren's code below. Here is the updated version of the code.

In my views.py I have this:

def index(request):
    blog_posts = Blog.objects.all().annotate(Count('comment')).order_by('-pub_date')[:5]

    return render_to_response('blog/index.html', 
    {'blog_posts': blog_posts})

In my template file (index.html) I have this:

Welcome...
{% if blog_posts %}
    <ul>
    {% for post in blog_posts %}
        <li>
            <b>
            <a href="/blog/post/{{ post.id }}">{{ post.title }}</a>
            </b> ({{ post.pub_date }})<br/>
            {{ post.content }}<br/>
            {{ post.comment__count }} comment(s)<br/>
            by: {{ post.author }}<br/><br/>
        </li>
    {% endfor %}
    </ul>
{% else %}
    <p>No posts are available.</p>
{% endif %}

Hope this also helps out the others. Thanks for everything guys!

Answer 1

You can do this using aggregation described here.

Something the likes of:

class Post(models.Model):
    title = models.CharField(max_length=200)
    body = models.TextField()
    #additional fields here...

class Comment(models.Model):
    post = models.ForeignKeyField(Post)
    #additional fields here...

...

from django.db.models import Count
from project.application.models import Post, Comment

post_list = Post.objects.annotate(Count('comment_set'))
for p in post_list:
    print p.comment_set__count

Answer 2

Don't try to produce SQL queries literally in Django. You want to count of all comments on blog entries. As a simple loop, this is simple.

for b in Blog.objects.all():
    c = b.comment_set.count()

That's the kind of code you use in Django instead of a complex SQL query.

This fetches all blog objects and their associated counts, allowing you to present any relevant fields from the blog objects.

Please read http://docs.djangoproject.com/en/dev/topics/db/queries/#topics-db-queries completely for the way to query data in Django.