Tortoise-orm: Fix joined annotations for `value` and `value_list` — was: Grouped aggregations

[ozturkberkay]

Is your feature request related to a problem? Please describe.
I would like to group the table by a field and apply aggregations. Example SQL:

SELECT SUM(`column_1`)
FROM `table`
WHERE `column_2` = 'something'
GROUP BY `column_3`

Describe the solution you'd like
I'm not familiar with the code-base of this project yet, so I won't be able to propose an implementation.

Describe alternatives you've considered
Django's solution looks nice:

Author.objects.values('name').annotate(average_rating=Avg('book__rating'))

Additional context
If grouped aggregations already exists, I couldn't find it in the documentation.

[grigi]

authors = await Author.all().annotate(average_rating=Avg('books__rating'))

And then you can access average_rating directly on the object:

authors[0].average_rating

This is the generated SQL, which looks perfectly fine to me:

SELECT "author"."name","author"."id",AVG("book"."rating") "average_rating" 
FROM "author"
LEFT OUTER JOIN "book" ON "author"."id"="book"."author_id"
GROUP BY "author"."id"

But testing this, it seems to not work with values, when I do a

authors = await Author.all().annotate(average_rating=Avg('books__rating')).values('id', 'name', 'average_rating')

I get the wrong data, in fact it misses the join entirely. I'll mark this as a bug to "Fix joined annotations for value and value_list"

[ozturkberkay]

Thanks for the reply. Can we specify the field name to group by (instead of the pk)? An example would be to group the users by their gender and get the average age for each group.

[grigi]

Right now we are missing explicit group-by. could you please create a new ticket with an example of what you want to support? Thanks in advance.

The fix for values() & values_list() should be available in next release.

[grigi]

Fixed in 0.16.2 & 0.15.20. Documentation update still pending.