tortoise / tortoise/tortoise-orm
Add exists filter syntax
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 5.6k
- Forks
- 516
- Avg merge
- 2d 21h
- Merged PRs (30d)
- 9
Description
Is your feature request related to a problem? Please describe.
When querying for the existence of a one-to-many related model with certain values it one of its fields, the .distinct() method is required to prevent duplicates. This is not the most efficient query.
Contrived example: I have to do this to find all phone numbers of users that work at the north pole without duplicates:
PhoneNumber.filter(user__company__location='North Pole').distinct()
According to ChatGPT,
SELECT m.*
FROM model m
WHERE EXISTS (
SELECT 1
FROM user u
JOIN company c ON u.company_id = c.id
WHERE u.id = m.user_id
AND c.location = 'North Pole'
);
is preferable over
SELECT DISTINCT m.*
FROM model m
LEFT OUTER JOIN user u ON m.user_id = u.id
LEFT OUTER JOIN company c ON u.company_id = c.id
WHERE c.location = 'North Pole';
Why this is better (copied from ChatGPT):
EXISTSchecks for existence rather than joining, so no duplicates.- Often more efficient, since the DB can stop searching after the first match.
- Cleaner semantics: “Does this related row exist?” rather than “Give me all the related rows, then deduplicate.”
Describe the solution you'd like
PhoneNumber.filter(user__company__exists__location='North Pole')
Describe alternatives you've considered
Could be variations on the placement of __exists in the query. I realize the exact semantics could be a bit unclear.
Additional context
Related #385
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 related issue #385 and compare the proposed filter syntax with the EXISTS and JOIN SQL examples in this issue. Clarify where __exists belongs and what its semantics should be; done means an agreed syntax that generates existence-based filtering without requiring distinct().
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python, sql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 42/100