tortoise / tortoise/tortoise-orm
how to use group_concat with group by in tortoise???
Open
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
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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