On Sun, Jul 12, 2009 at 11:01 PM, Mat<matburn...@gmail.com> wrote:
>
> 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.
In short - you can't.
Django's ORM doesn't work in terms of LEFT OUTER JOINS - it's an
object based model. As such, questions like this don't really make
much sense - Django doesn't expose a LEFT OUTER JOIN capability, so
there isn't an entry point to "attach a clause to the join".
A slightly lower level answer to your question is that filter()
clauses all resolve to WHERE or HAVING clauses. There isn't any
programatic control over join behavior.
This means that there are some queries that can't be expressed (or
can't be expressed easily). That's ok, though - we already have a
solution that can express every possible SQL query - it's called SQL.
There will always be a class of queries for which "just use raw SQL"
is the answer - and given that you already know the raw SQL you want,
you haven't got much to do other than plug the query into a cursor.
Django's ORM isn't trying to replace SQL - it aims to be an elegant
way of representing lots of common query types. On top of that,
annotations and aggregates are a very recent addition to the
capabilities of the ORM; they are a complex thing to express cleanly,
and the capabilities of aggregates are evolving.
That said - I agree that your use case is fairly obvious; any
suggestions on how this kind of query could be cleanly represented in
Django's ORM syntax are welcome.
Yours,
Russ Magee %-)
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
