tortoise / tortoise/tortoise-orm

Missing group by clause in SQL query while trying to count grouped and filtered objects.

Open
#1,503 0 comments 1 reaction 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

Describe the bug
I want to know user position to form top of players. So when I'm trying to use this code (count how many users have won more games than specified one, 10 - is the number of wins specified player has):
await Games.filter(is_active=False).group_by('winner_id').annotate(wins=Count('winner_id')).filter(wins__gt=10).count()
this query is produced:
SELECT COUNT(*) FROM "games" WHERE "is_active"=false HAVING COUNT("winner_id")>10
but here is missing GROUP BY clause, that's why invalid output is given.

To Reproduce

class Games(Model):
    id = fields.IntField(pk=True, index=True)

    winner: fields.ForeignKeyNullableRelation[Users] = fields.ForeignKeyField(
        'models.Users', related_name='games_won', null=True
    )

    is_active = fields.BooleanField(default=True)

    class Meta:
        table = 'games'

await Games.filter(is_active=False).group_by('winner_id').annotate(wins=Count('winner_id')).filter(wins__gt=10).count()

Expected behavior
Query to count how many objects there are after grouping by and filtering through annotated field.

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

No file or test is named. Start by reproducing the shown Games query and inspect the ORM's query construction for group_by, annotate, HAVING, and count handling. Done means the generated SQL preserves the grouping and count returns the number of grouped, filtered objects.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, sql
Domain
backend, database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.