How to get extra columns in group by in Django?

Question

I just want to include ID(and other fields) as well in the result of an annotate with values (i.e group by).

I'll explain with an example (below is just an example, please suspend your disbelief on this bad model),

class Book(models.Model):
    name = models.CharField()
    version = models.IntegerField() # Assming its just a number that increments when each version is published like 1...2...3 and so on.
    published_on = models.DateTimeField()
    author = models.ForeignKey(User)

class Text(models.Model):
    book_text = models.ForeignKey(Book)

What I want is the Max version(or editions) of a Book, which I achieve by

Book.objects.values('name', 'author').annotate(Max('version'))


**Generated SQL (MYSQL) query**:
SELECT "name",
       "author",
       Max("version") AS "version__max" 
FROM   "book" 
GROUP  BY "name",
          "author"

But the above result does not contain ID or published_on if I add them to values it groups by them as well, which is not what I want. I want to group by only name and author, But want other fields or at least get an ID.

Expected SQL:
SELECT "id",
       "published_on",
       "name",
       "author",
       Max("version") AS "version__max" 
FROM   "book" 
GROUP  BY "name",
          "author"

Midriaz · Accepted Answer · 2020-05-09T17:38:28.220

2

In SQL you can't select fields that are not in group by statement. You should get id by an additional query, for example:

Book.object.filter(name=name_from_first_query, author=author_from_first_query, version=version_from_first_query

Of course, you must iterate over the results of your group by query.

edited May 09 '20 at 17:38

answered May 08 '20 at 08:01

Midriaz

180
1
8

Yes, you are right, I guess I have to use either python or Subquery... A really good explanation I found why it is recommended to have the same fields in both select and group by is here [link](https://www.valentina-db.com/docs/dokuwiki/v5/doku.php?id=valentina:articles:sql_selecting_from_groups) ...but thanks alot. – neeraj9194 May 09 '20 at 10:16

sandeshdaundkar · Answer 2 · 2020-05-08T13:07:03.467

0

from django.db.models import F

Book.objects.values('name', 'author').annotate(Max('version'), id=F('id'))

While doing group by, in django. The sequence matters. We pass values by which we need to group_by the queryset and inside the annotate function we use F expression to add extra elements required in the result queryset. If we add id in values then it will be taken into account while doing group by. Hope this clears :)

edited May 08 '20 at 13:07

answered May 08 '20 at 07:59

sandeshdaundkar

883
1
7
14

Although this code might solve the problem, a good answer should also explain **what** the code does and **how** it helps. – BDL May 08 '20 at 10:35
So this solution seems good, I also tried it before posting. But somehow it doesn't work...the `ID` gets added to the GROUP BY query and works like ```Book.objects.values('name', 'author','id').annotate(Max('version'))``` – neeraj9194 May 08 '20 at 11:08
While this code may provide a solution to problem, it is highly recommended that you provide additional context regarding why and/or how this code answers the question. Code only answers typically become useless in the long-run because future viewers experiencing similar problems cannot understand the reasoning behind the solution. – E. Zeytinci May 08 '20 at 12:29
Added explaination for better understanding :) upvote appreciated – sandeshdaundkar May 08 '20 at 13:07

score 0 · Answer 3 · answered May 08 '20 at 08:30

0

Book.objects.values('name', 'author').annotate(Max('version'), Max('id'))

answered May 08 '20 at 08:30

Manasa

130
2
10

This solution works but looks hacky but anyway...thanks. – neeraj9194 May 08 '20 at 11:12
While this code may provide a solution to problem, it is highly recommended that you provide additional context regarding why and/or how this code answers the question. Code only answers typically become useless in the long-run because future viewers experiencing similar problems cannot understand the reasoning behind the solution. – E. Zeytinci May 08 '20 at 12:28

How to get extra columns in group by in Django?

3 Answers3