#31535: Calling annotate with a case statement that references a recursive many
to
many relationship on the same model as the queryset will create duplicates
-------------------------------------+-------------------------------------
Reporter: david- | Owner: nobody
cooke |
Type: Bug | Status: new
Component: Database | Version: master
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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
--
Ticket URL: <https://code.djangoproject.com/ticket/31535>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/054.ee99900bf7e80be51263b69c863dd008%40djangoproject.com.
