-
-
Couldn't load subscription status.
- Fork 441
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