#10060: Multiple table annotation failure
-------------------------------------+-------------------------------------
Reporter: svsharma@… | Owner: (none)
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 wilhelmhb):
Replying to [comment:57 powderflask]:
A huge thanks to powderflask: for the ones knowledgeable enough with SQL,
this is the way to go =)
I also tried the solution described here:
[https://stackoverflow.com/questions/48598245/multiple-annotate-with-sum-
and-display-data-in-admin-django/48607830#48607830], alas without
success...
> 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:67>
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.31cb48de32bc71efde0aef2de3b10441%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
