I'm trying to replace a convoluted database-specific SQL statement
with the new aggregates functionality. 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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
