#28107: Can't perform annotation on related table when un-managed model is
backed
by a DB view
-------------------------------------+-------------------------------------
Reporter: powderflask | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by powderflask:
Old description:
> 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.
New description:
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 an unmanaged model
(backed by a DB View) 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...).
UPDATE: The root cause is actually that Postgresql treats Views
differently than Tables w.r.t. what is required in the group by clause.
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#comment:11>
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/069.3288c5c19768c0aab950351ee3ab7409%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
