sql - Wrong GROUP BY field in Django annotate query -

the original problem caused quite awkward cycling models reference:

# -> b ->  class a:     b = models.foreignkey('b', null=true, blank=true)  class b:     = models.foreignkey('a') 

now, when i'm trying annotate query, uses group a's id left outer join ( t3.id in example below) instead of a.id.

example:

a.objects.select_related('b', 'b__a').annotate(reviews=count('reviews')) 

generated sql:

select      `a`.`id`,     `b`.`id`,     t3.`id`,     `a`         left outer join     `b` on (`a`.`b_id` = `b`.`id`)         left outer join     `a` t3 on (`b`.`a_id` = t3.`id`)     `a`.`id` in (1, 2, 3, 4, 5) group t3.`id` order null; 

i know can next things:

1. change model not cycling reference (unfortunately can't right now)
2. can use .extra() instead of annotations (i'd try avoid it)
3. remove .select_related() call (can't due performance issues)

upd: using group t3.id exclude results, a.b == none

the best solution me specifying correct field in group clause, don't know how. possible? there other way fix problem? thanks.

opened django compiler:

def collapse_group_by(self, expressions, having):     # if db can group primary key, group primary key of     # query's main model. note postgresql group clause must     # include primary key of every table, mysql enough     # have main table's primary key. mysql form     # implemented.     # mysqlism: however, columns in having clause must added     # group by.     if self.connection.features.allows_group_by_pk:         # logic here is: if main model's primary key in         # query, set new_expressions field. if happens,         # add having expressions group by.         pk = none         expr in expressions:             if (expr.output_field.primary_key ,                     getattr(expr.output_field, 'model') == self.query.model):                 pk = expr                 # here breakpoint required         if pk:             expressions = [pk] + [expr expr in expressions if expr in having]     return expressions 

so, collapse_group_by function doesn't stop looking pk it's found already, that's why grouping done t3.id instead of a.id (and have missing results). fix problem, breakpoint required inside loop (marked in comments).

upd: issue fixed in django 1.8.2 version https://code.djangoproject.com/ticket/24748