id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
28107	Can't perform annotation on related table when un-managed model is backed by a DB view	powderflask	Vojtěch Boček	"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."	Bug	closed	Database layer (models, ORM)	dev	Normal	fixed	QuerySet.extra	Simon Charette	Ready for checkin	1	0	0	0	0	0