On Mon, Jul 13, 2009 at 9:53 AM, Russell Keith-Magee <freakboy3...@gmail.com
> wrote:
>
> 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 %-)
>
> >
>
Unless I've missed something the behavior you've described is what the
default behavior should be in Django. That is to say that adding an
annotation shouldn't affect the results you see, which it clearly does in
this case. This is similar to an issue that came up when dealing with
generic relations, so we will need a comprehensive solution to this issue
AFAICT. http://code.djangoproject.com/ticket/10870
Alex
--
"I disapprove of what you say, but I will defend to the death your right to
say it." -- Voltaire
"The people's good is the highest law." -- Cicero
"Code can always be simpler than you think, but never as simple as you want"
-- Me
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
