#10060: Multiple table annotation failure
-------------------------------------+-------------------------------------
Reporter: svsharma@… | Owner:
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by powderflask):
Replying to [comment:9 bendavis78]:
In [comment:16 comment16] bendavis78 recommends this query to solve the
issue:
> {{{
> #!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
> }}}
For others who might arrive here looking for a reasonable way to work
around this issue, the following worked well for me. I added a custom
Queryset method to the Manager class, and used a
[https://docs.djangoproject.com/en/1.9/ref/models/expressions/#raw-sql-
expressions RawSQL()] expression to create the annotations. This at
least encapsulates the SQL code and allows the annotation to be integrated
with a normal django queryset. Here's a sample for the example given
above:
{{{
#!python
def annotate_sum_for_user(user_related_modelClass, field_name,
annotation_name):
raw_query = """
SELECT SUM({field}) FROM {model} AS model
WHERE model.user_id = user.id
""".format(
field = field_name,
model = user_related_modelClass._meta.db_table,
)
annotation = {annotation_name: RawSQL(raw_query, [])}
return self.annotate(**annotation)
}}}
Usage for above query on presumed User model:
{{{
#!python
users = models.User.objects\
.annotate_sum_for_user(PointEarning, 'points', 'points_earned')\
.annotate_sum_for_user(PointExpense, 'points', 'points_spent')
}}}
Hope someone finds this useful, and a word of HUGE thanks to those who
worked to get a statement of this issue and a link to this thread into the
django documentation -- probably saved me hours.
--
Ticket URL: <https://code.djangoproject.com/ticket/10060#comment:57>
Django <https://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 unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/081.fdaf7a380c0bf5becc80ad0748b280fc%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
