tortoise / tortoise/tortoise-orm
Filtering two Q expressions across two ManyToMany relationships between the same tables
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
Filtering two Q expressions across two ManyToMany relationships between the same tables returns incorrect results.
To Reproduce
class User(Model):
username = fields.TextField()
class Task(Model):
reviewers = fields.ManyToManyField(
"User",
related_name="task_reviewers",
null=True,
through="task_reviewers",
)
assignees = fields.ManyToManyField(
"User",
related_name="task_assignees",
null=True,
through="task_assignees",
models.Task.filter(Q(assignees__username='username')).values_list("id").sql()
'SELECT "task"."id" "0"
FROM "task"
LEFT OUTER JOIN "task_assignees" ON "task"."id"="task_assignees"."task_id"
LEFT OUTER JOIN "user" ON "task_assignees"."user_id"="user"."id"
WHERE "user"."username"=\'username\''
models.Task.filter(Q(assignees__username='username') | Q(reviewers__username='username')).values_list("id").sql()
'SELECT "task"."id" "0"
FROM "task"
LEFT OUTER JOIN "task_assignees" ON "task"."id"="task_assignees"."task_id"
LEFT OUTER JOIN "user" ON "task_assignees"."user_id"="user"."id"
LEFT OUTER JOIN "task_reviewers" ON "task"."id"="task_reviewers"."task_id"
WHERE "user"."username"=\'username\' OR "user"."username"=\'username\''
This query is only selecting users on the task_assignees through table.
Expected behavior
Should query users correctly.
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
Reproduce the issue with the shown User and Task models, then trace how Q expressions, ManyToMany relationships, filter(), values_list(), and sql() produce the joins. Compare the generated SQL for assignees and reviewers; done means each relationship is queried correctly and the combined filter returns the expected task IDs.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100