#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-cooke | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by david-cooke:
Old 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
New 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 although
thats needed for the case statement.
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#comment:1>
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/069.fa3aef212ea6b6b8c8302b7684cd1c9f%40djangoproject.com.
