How to get extra columns in group by in Django?

user_2303855 0
Viewed 714

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"
django-annotate django-orm django-models django python
3 Answers

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.

user_129310830 
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 :)

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