#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 [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/054.468d29500601a42674d1df5c30c72119%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
