tortoise / tortoise/tortoise-orm

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

Open
#1,525 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Python
Stars
5.6k
Forks
516
Avg merge
2d 21h
Merged PRs (30d)
9

Description

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;

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the custom GroupConcatFunction and GroupConcat definitions and the annotate call shown in the issue; compare their generated SQL with the target GROUP_CONCAT query using SEPARATOR '*'. Done means establishing how the separator and default value can be configured for the grouped query, with the resulting SQL matching the requested form.

Written by the indexing model from the issue text.

Assessment

Tech stack
mysql, python
Domain
database
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.