tortoise-orm
tortoise-orm copied to clipboard
how to use group_concat with group by in tortoise???
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;
for new version.
class GroupConcat(Function):
class PikaGroupConcat(DistinctOptionFunction):
def __init__(
self, term: Term | str, alias: str | None = None, is_distinct: bool = True
):
super().__init__( # type: ignore
"GROUP_CONCAT", F(term) if isinstance(term, str) else term, alias=alias
)
self._distinct = is_distinct
database_func = PikaGroupConcat