tortoise / tortoise-orm

Familiar asyncio ORM for python, built with relations in mind
https://tortoise.github.io
Apache License 2.0
4.58k stars 378 forks source link

how to use group_concat with group by in tortoise??? #1525

Open yodew opened 9 months ago

yodew commented 9 months ago
from pypika.terms import Function
from tortoise import Tortoise
from tortoise.expressions import Aggregate
class GroupConcatFunction(Function):
    def __init__(self, term, *default_values, **kwargs):
        super(GroupConcatFunction, self).__init__("GROUP_CONCAT", term, *default_values, **kwargs)

class GroupConcat(Aggregate):
    database_func = GroupConcatFunction

await UserItemScore.annotate(sum_score=Sum("score"), max_modified_at=Max("modified_at"),
                                          group_item_name=GroupConcat("exam_items__name"))

this code is above, I want to custom the format of group_concat, but failed. I want to realize the below sql,witch I can use SEPARATOR and change the default value, like '*' or '|' , to join target field values

SELECT
    students_id,
    u.subject_id,
    school_id,
    SUM(u.score) score,
    MAX(u.modified_at) modified_at,
    GROUP_CONCAT(e.name SEPARATOR '*') exam_itme_name
FROM
    user_item_score u
    LEFT JOIN exam_items e ON u.exam_items_id = e.uid
WHERE
    score_year = '' AND score_type = 2 AND u.deleted = 0
GROUP BY students_id , u.subject_id , school_id;