On Thu, Feb 19, 2009 at 2:32 AM, Stefan Tunsch <stun...@gmail.com> wrote:
>
> Hi there!
>
> I'm trying to see what's the best approach in my scenario.
> The problem is how to make a list display different sums and
> calculations based on it's child objects.
>
> My scenario could be the following:
> Two models: Project and Task
> One Project has many Task.
> Task has a boolean "open" field (open,closed) and a due_date.
>
> I want to retrieve a list of projects that includes the total number of
> tasks (that one is easy ;), number of open tasks and number of tasks
> with due_date today.
>
> First of all, I understand that this would not be possible with the new
> Aggregation / Annotation functionality, right? (Maybe I'm mistaken)
Not possible as a single statement. The individual components are
certainly possible.
> I cannot do something like:
> projects = Project.objects.all(),annotate(total=Count('task'),
> open_tasks=Count('task__open=True'),
> due_today=Count('task__due_date=datetime.today()')
Obviously, this syntax isn't legal (as you would have found if you tried that)
> Second, if not, what would be the best way to approach this scenario?
The problem here is that your query, although simple to pose, isn't
that simple to answer in a single SQL statement. You can't just do it
with simple joins, because you need three separate joins on the task
table to filter out the three different row counts. The only way I can
see to answer this question as a single query is to use three
subqueries that compute the counts, and join those subqueries.
The same is essentially true of Django. You could easily pose your
question with as three separate queries:
Project.objects.annotate(total=Count('task'))
Project.objects.filter(task__open=True).annotate(open_tasks=Count('task"))
Project.objects.filter(task__due_date=datetime.today()).annotate(due_today=Count('task"))
However, there isn't a way (that I am aware of) for combining these
standalone queries as sub-queries of a larger query. You could,
however, manually merge the three results sets into a single unified
set. If you choose to take this approach, I would suggest using the
.values() operator to constrain the amount of data that you need to
retrieve and merge.
This broad problem is something that is currently logged as ticket
#10060; the issue is how to deal with multiple joins in query
statements. It's not a simple problem to solve; even catching the
cases where it is potentially a problem is difficult. I hope to be
able to sort something out before we cut version 1.1.
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
-~----------~----~----~----~------~----~------~--~---
