tortoise-orm icon indicating copy to clipboard operation
tortoise-orm copied to clipboard

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

Open yodew opened this issue 2 years ago • 1 comments

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;

yodew avatar Dec 12 '23 07:12 yodew

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

exherb avatar Apr 27 '25 11:04 exherb