Django 1.8. When we use id field in values and annotate operation in django there is ignore another fields in result sql-query

Question

I want to update Django from 1.7.11 to 1.11.18. But I found a problem. Django makes different sql-queries for different Django versions. For example. I have a query:

Account.objects
       .values('id', 'name', invoice__payment__payment_gateway')
       .annotate(
         pay_paid=Sum('invoice__payment__amount'),
         pay_refunded=Sum('invoice__payment__amount_refunded')
       )
       .order_by('-name', )

sql-query for Django 1.7.11:

SELECT `member_account`.`id`, 
       `member_account`.`name`, 
       `member_payment`.`payment_gateway`, 
       SUM(`member_payment`.`amount_refunded`) AS `pay_refunded`, 
       SUM(`member_payment`.`amount`) AS `pay_paid` 
FROM `member_account` 
  LEFT OUTER JOIN `member_invoice` ON ( `member_account`.`id` = `member_invoice`.`account_id` ) 
  LEFT OUTER JOIN `member_payment` ON ( `member_invoice`.`id` = `member_payment`.`invoice_id` ) 
GROUP BY `member_account`.`id`, `member_account`.`name`, `member_payment`.`payment_gateway` 
ORDER BY `member_account`.`name` DESC

Please pay attention to the GROUP BY section. There are 3 fields: id, name, payment_gateway.

But we have following sql-query for Django 1.8.19:

SELECT `member_account`.`id`, 
       `member_account`.`name`, 
       `member_payment`.`payment_gateway`, 
       SUM(`member_payment`.`amount_refunded`) AS `pay_refunded`, 
       SUM(`member_payment`.`amount`) AS `pay_paid` 
FROM `member_account` 
  LEFT OUTER JOIN `member_invoice` ON ( `member_account`.`id` = `member_invoice`.`account_id` ) 
  LEFT OUTER JOIN `member_payment` ON ( `member_invoice`.`id` = `member_payment`.`invoice_id` ) 
GROUP BY `member_account`.`id` 
ORDER BY `member_account`.`name` DESC

And we have only one field in the GROUP BY section. Why do we have only one id field? It is main question.

BUT when I remove id field from values section Django 1.8 makes valid sql-query:

SELECT `member_account`.`name`, 
       `member_payment`.`payment_gateway`, 
       SUM(`member_payment`.`amount_refunded`) AS `pay_refunded`, 
       SUM(`member_payment`.`amount`) AS `pay_paid` 
FROM `member_account` 
  LEFT OUTER JOIN `member_invoice` ON ( `member_account`.`id` = `member_invoice`.`account_id` ) 
  LEFT OUTER JOIN `member_payment` ON ( `member_invoice`.`id` = `member_payment`.`invoice_id` ) 
GROUP BY `member_account`.`name`, `member_payment`.`payment_gateway` 
ORDER BY `member_account`.`name` DESC

score 0 · Answer 1 · answered Feb 25 '19 at 13:57

Recent Django versions seem to omit redundant fields from the group by clause. If you group by a unique field in a table, there is no point in grouping by any further fields from that table. This definitely explains the omission of member_account.name, which is redundant if you have the unique member_account.id in the query.

The same might be true of payment_gateway if you have constraints to the effect that every account shall only have one payment_gateway. If accounts can have several payment gateways, then I see no immediate explanation for why Django considers the field redundant for group_by purposes.

If you show the definitions of the relevant models, we may be able to tell you more.

Django 1.8. When we use id field in values and annotate operation in django there is ignore another fields in result sql-query

1 Answers1