Can I filter on multiple contrains with django aggregate functionlity?

Question

How can I count records with multiple constraints using django's aggregate functionality?

Using django trunk I'm trying to replace a convoluted database-specific SQL statement with django aggregates. As an example, say I have a database structured with tables for blogs running on many domains (think .co.uk, .com, .etc), each taking many comments:

domains <- blog -> comment

The following SQL counts comments on a per-domain basis:

SELECT D.id, COUNT(O.id) as CommentCount FROM domain AS D
LEFT OUTER JOIN blog AS B ON D.blog_id = B.id
LEFT OUTER JOIN comment AS C ON B.id = C.blog_id
GROUP BY D.id

This is easily replicated with:

Domain.objects.annotate(Count('blogs__comments'))

---

Taking this a step further, I'd like to be able to add one or more constraints and replicate the following SQL:

SELECT D.id, COUNT(O.id) as CommentCount FROM domain AS D
LEFT OUTER JOIN blog AS B ON D.blog_id = B.id
LEFT OUTER JOIN comment AS C ON B.id = C.blog_id
    AND C.active = True
GROUP BY D.id

This is much more difficult to replicate as django seems including to filter on the whole shaboodle with a WHERE clause:

Domain.objects.filter(blogs__comments__active=True)
              .annotate(Count('blogs__comments'))

SQL comes out something like this:

SELECT ..., COUNT(comment.id) AS blog__comments__count FROM domain
LEFT OUTER JOIN blog ON domain.blog_id = blog.id
LEFT OUTER JOIN comment ON blog.id = comment.blog_id
WHERE comment.active = True
GROUP BY domain.id
ORDER BY NULL

How can I persuade django to pop the extra constraint on the appropriate LEFT OUTER JOIN? This is important as I want to include a count for those blogs with no comments.

Answer 1

I don't know how to do this using the Django query language, but you could always run a raw SQL query. In case you don't already know how to do that, here's an example:

from django.db import connection

def some_method(request, some_parameter):
    cursor = connection.cursor()
    cursor.execute('SELECT * FROM table WHERE somevar=%s', [some_parameter])
    rows = cursor.fetchall()

More detail is available in the Django book online: http://www.djangobook.com/en/2.0/chapter05/

Look for the section "The “Dumb” Way to Do Database Queries in Views". If you don't want to use the "dumb" way, I'm not sure what your options are.