Quantcast

[Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Django
#28107: Can't perform annotation on related table when relation between tables not
on primary key
-------------------------------------+-------------------------------------
               Reporter:             |          Owner:  nobody
  powderflask                        |
                   Type:             |         Status:  new
  Uncategorized                      |
              Component:  Database   |        Version:  1.11
  layer (models, ORM)                |
               Severity:  Normal     |       Keywords:  QuerySet.extra
           Triage Stage:             |      Has patch:  0
  Unreviewed                         |
    Needs documentation:  0          |    Needs tests:  0
Patch needs improvement:  0          |  Easy pickings:  0
                  UI/UX:  0          |
-------------------------------------+-------------------------------------
 I'm working with a legacy DB (ArcSDE database -- really ugly) -- have been
 able to accommodate its oddities without resorting much to raw SQL, but
 django upgrade (1.8 --> 1.11) caused a previously working annotation to
 fail:
 {{{ psycopg2.ProgrammingError: :column ... must appear in the GROUP BY
 clause or be used in an aggregate function }}}

 **DB:  PostgreSQL 9.3.16 **   (i.e., this is not same issue as #26758 )
 Python3.6, Django1.11

 The annotation simply counts the number of related records from a related
 'attachments' table:
 {{{ qs.annotate(num_attachments=Count('attachments')) }}}

 The root cause appears to be that the relation between the model and
 attachments tables uses a unique field ''other than the main model's
 primary key'' (I know -- told you it was ugly - ArcSDE does not really
 support relations, except they implement attachments with this odd ball **
 sigh **).
 The change in behaviour seems to manifest from #19259 (I believe django1.8
 added all fields to the groupby clause).
 Since django now includes only the primary key in the groupby clause,
 postgresql won't do this aggregation across a relation that uses a non-pk
 field.

 I suspect there is a general issue here that aggregations on a related-
 table won't work in postgresql unless the relation is on the primary key
 field (so, yeah, basically this issue applies to almost no one, right...).

 Seems likely there is a better solution to this, but after a half-day of
 search / effort, I resorted to the following:
 {{{
 qs.extra(select={'num_attachments':
             'SELECT COUNT("attachmentid") FROM {attach_table} WHERE
 {attach_table}.rel_globalid = {model_table}.globalid'.format(
                     model_table  = qs.model._meta.db_table,
                     attach_table =
 qs.model.AttachmentModel._meta.db_table,
             )},)
 }}}

 This works and achieves my goal -- to annotate model with the number of
 related attachments.
 Since the {{{ model.attachments.count() }}} query works just fine, I'm
 considering eliminating this annotation and replacing with a property on
 the model class, what's one more query...  I'm sure there must be an
 smoother way, but it eludes me...

 Since the docs suggested to open a ticket for queries that could not be
 resolved without resorting to extra(), here it is, for whatever its worth.
 Hope this hasn't been a complete waste of time for you.

--
Ticket URL: <https://code.djangoproject.com/ticket/28107>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/054.468d29500601a42674d1df5c30c72119%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Django
#28107: Can't perform annotation on related table when relation between tables not
on primary key
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Uncategorized        |                   Status:  new
    Component:  Database layer       |                  Version:  1.11
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  QuerySet.extra       |             Triage Stage:
                                     |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

 * cc: Simon Charette (added)


Comment:

 It's possible that's an edge case with the handling of `to_field`.

 Would it be possible for your to provide all the models involved in the
 queryset you're generating as it's really hard to figure out if the issue
 actually lies in the _GROUP BY_ logic without a set of models to reproduce
 the issue.

--
Ticket URL: <https://code.djangoproject.com/ticket/28107#comment:1>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.d50b46520164c539e5c61f25492dc82d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Django
In reply to this post by Django
#28107: Can't perform annotation on related table when relation between tables not
on primary key
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Uncategorized        |                   Status:  new
    Component:  Database layer       |                  Version:  1.11
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:
     Keywords:  QuerySet.extra       |             Triage Stage:
                                     |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------

Comment (by powderflask):

 The DB and relations are just nasty to set it all up.   I'm pretty sure I
 know what's needed to reproduce this -- I'll try to get something simpler
 to break in the same way, no sense in you banging your head against the
 ArcSDE wall.

 BTW - edge case is a very polite term for what this really is...   After
 writing this up, I decided to sacrifice the extra query to get the
 attachment.count() to buy a world of ponies.  My code base loves me for it

--
Ticket URL: <https://code.djangoproject.com/ticket/28107#comment:2>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.fe0692ce58ca679a69949688fa11b85d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [Django] #28107: Can't perform annotation on related table when relation between tables not on primary key

Django
In reply to this post by Django
#28107: Can't perform annotation on related table when relation between tables not
on primary key
-------------------------------------+-------------------------------------
     Reporter:  powderflask          |                    Owner:  nobody
         Type:  Uncategorized        |                   Status:  closed
    Component:  Database layer       |                  Version:  1.11
  (models, ORM)                      |               Resolution:
     Severity:  Normal               |  worksforme
     Keywords:  QuerySet.extra       |             Triage Stage:
                                     |  Unreviewed
    Has patch:  0                    |      Needs documentation:  0
  Needs tests:  0                    |  Patch needs improvement:  0
Easy pickings:  0                    |                    UI/UX:  0
-------------------------------------+-------------------------------------
Changes (by powderflask):

 * status:  new => closed
 * resolution:   => worksforme


Comment:

 I tried.  I cannot reproduce this with a simpler test case.  My original
 suspicion is completely wrong - it works just fine.

 I can only reproduce this in the insanely complex DB -- I will continue to
 try to track down the cause, but for now I'd say this issue is too
 undefined to work on.  Sorry for the trouble, I'm closing this for now.

--
Ticket URL: <https://code.djangoproject.com/ticket/28107#comment:3>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.15aa678edcbaa190730cdf40d750a76b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Loading...