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" 

Answer 1

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.