tortoise-orm: Annotate on recursive relation giving error on PostgreSQL

Hello, I’m getting this PG error while trying to do a count on a recursive relationship.

tortoise.exceptions.OperationalError: column “task.estimated_start_date” must appear in the GROUP BY clause or be used in an aggregate function

class Task(BaseModel):
    mother = fields.ForeignKeyField('models.Task', null=True, related_name="children")
#Some code later...
tasks = await Task.filter(mother=None).annotate(children_count=Count("children"))

Am I doing something wrong?

What I’m trying to achieve with my query, is a hierarchical view of the tasks, first I’m getting the “outer” ones, and then checking if they have any children so then I will display a dropdown arrow that will asynchronous load the children and so on.

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 15 (8 by maintainers)

Commits related to this issue

Fix joining with self by reverse-foreign-key for filtering and annotation (#333) — committed to tortoise/tortoise-orm by grigi 4 years ago
Fix joining with self by reverse-foreign-key for filtering and annotation (#333) — committed to tortoise/tortoise-orm by grigi 4 years ago
Many small Function fixes (#333) — committed to tortoise/tortoise-orm by grigi 4 years ago
Fix the aggregates using the wrong side of the join when doing a self-referential aggregate. (#333) — committed to tortoise/tortoise-orm by grigi 4 years ago

Most upvoted comments

Release 0.16.4 os out with the fix, please confirm.

grigi on Apr 3, 2020

It should be fixed in next release. You can test out develop doing a pip install https://github.com/tortoise/tortoise-orm/archive/develop.zip

grigi on Mar 29, 2020

@grigi Yep, just updated dependencies and it works now, thanks for the very quick fix!

mojimi on Mar 27, 2020

Built a test for this, got the same error:

await Employee.filter(name="Root").annotate(num_team_members=Count("team_members"))

The SQL generated looks like:

SELECT "employee"."manager_id","employee"."name","employee"."id",COUNT("employee2"."id") "num_team_members"
FROM "employee"
LEFT OUTER JOIN "employee" "employee2" ON "employee2"."id"="employee2"."manager_id"
WHERE "employee2"."name"='Root'
GROUP BY "employee2"."id"

🤣 The last bit is all confused, instead of differentiating between employee and employee2 everything now has the 2… This is almost identical to the bug we had with filtering on a self-referential entry.

grigi on Mar 25, 2020