id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
31535	Calling annotate with a case statement that references a recursive many to many relationship creates duplicates.	David Cooke	nobody	"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"	Bug	closed	Database layer (models, ORM)	dev	Normal	invalid			Unreviewed	0	0	0	0	0	0