#10060: Multiple table annotation failure
----------------------------------------------+-----------------------------
Reporter: [email protected] | Owner:
Status: new | Milestone: 1.1
Component: ORM aggregation | Version: SVN
Resolution: | Keywords:
Stage: Accepted | Has_patch: 0
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
----------------------------------------------+-----------------------------
Comment (by bendavis78):
From what I've researched, joining on subqueries would be faster than
subqueries in the SELECT clause. So instead of this:
{{{
#!sql
SELECT
u.email,
(SELECT SUM(points) FROM point_earning WHERE user_id=u.id) AS
points_earned,
(SELECT SUM(points) FROM point_expense WHERE user_id=u.id) AS
points_spent
FROM
"user" u
}}}
...we would want this:
{{{
#!sql
SELECT
u.email,
a.points AS points_earned,
b.points AS points_spent
FROM
"user" u
LEFT OUTER JOIN (SELECT user_id, SUM(points) AS points FROM
point_earning GROUP BY user_id) a ON a.user_id=u.id
LEFT OUTER JOIN (SELECT user_id, SUM(points) AS points FROM
point_expense GROUP BY user_id) b ON b.user_id=u.id
ORDER BY u.id
}}}
What this does, essentially, is move the aggregate function into a derived
table which we join onto the main table. I would imagine a solution to
this bug would detect if annotations are being used across multiple
relations, and if so, would adjust the join and select clauses as
necessary. I may attempt to write a patch for this soon, but it seems
like it would be tough, so there's no guarantees :-p
Thoughts?
--
Ticket URL: <http://code.djangoproject.com/ticket/10060#comment:9>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
