Opened 5 years ago
Last modified 5 years ago
#31535 closed Bug
Calling annotate with a case statement that references a recursive many to many relationship on the same model as the queryset will create duplicates — at Initial Version
| Reported by: | David Cooke | Owned by: | nobody |
|---|---|---|---|
| Component: | Database layer (models, ORM) | Version: | dev |
| Severity: | Normal | Keywords: | |
| Cc: | Triage Stage: | Unreviewed | |
| Has patch: | no | Needs documentation: | no |
| Needs tests: | no | Patch needs improvement: | no |
| Easy pickings: | no | UI/UX: | no |
Description
So I have a Challenge model that has an unlocks field which is a recursive, asymmetric many to many field with a reverse name of unlocked_by. I call annotate on it to add an "unlocked" field which checks if the challenges unlocked_by contains any ids in a list of solved challenges, doing this inserts all the solved challenges into the queryset that annotate is being called on. There's an example of this here: https://github.com/david-cooke/djangobug/blob/master/bug/tests.py
The sql being run is
SELECT "bug_challenge"."id", "bug_challenge"."name", CASE WHEN "bug_challenge_unlocks"."from_challenge_id" IN (5, 1) THEN True ELSE False END AS "unlocked" FROM "bug_challenge" LEFT OUTER JOIN "bug_challenge_unlocks" ON ("bug_challenge"."id" = "bug_challenge_unlocks"."to_challenge_id")
so it seems like the left outer join is adding the duplicates.
It is possible to work around this by calling value_list(flat=True).distinct('pk') however isn't compatible with more complicated examples such as https://github.com/ractf/core/blob/master/challenge/views.py#L50