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
31988	Annotate Case(When()) duplicate objects.	Vladimir Kuznetsov	nobody	"Hey! I have some simple models here:
{{{
class Token(models.Model):  # User in my project.
    token = models.UUIDField(db_index=True)
    ...


class Question(models.Model):
    is_answered = models.BooleanField(default=False)
    ...


class Like(models.Model):  # Not created for all users automatically if new question created.
    question = models.ForeignKey(Question, on_delete=models.CASCADE, related_name='likes')
    token = models.ForeignKey(Token, on_delete=models.CASCADE)
    like_value = models.IntegerField(default=0)

}}}

And I wanna do filter with annotate (get all unanswered questions and show user's like value):
{{{
x_id = self.context.get('request').META.get('HTTP_X_CLIENT_TOKEN')  # just UUID

questions = Question.objects.filter(
    is_answered=False
).annotate(
    my_like=Case(
        When(Q(likes__token__token=x_id) & Q(likes__like_value=1), then=1),
        When(Q(likes__token__token=x_id) & Q(likes__like_value=-1), then=-1),
        default=Value(0),
        output_field=IntegerField()
    )
)

}}}

The problem clearly shown here:
{{{
>>> print(Question.objects.filter(is_answered=False).count())
4
>>> print(questions.count())
7

}}}

Duplicate happened if other user(s) liked the same question(s) which current user liked. If I call `questions.query` I'll get `LEFT OUTER JOIN` in SQL request.
It can be fixed in this way:
{{{
user_liked_questions = Question.objects.filter(likes__like_value=1, likes__token__token=x_id)
user_disliked_questions = Question.objects.filter(likes__like_value=-1, likes__token__token=x_id)

questions = Question.objects.filter(
    is_answered=False
).annotate(
    my_like=Case(
        When(id__in=user_liked_questions, then=1),
        When(id__in=user_disliked_questions, then=-1),
        default=Value(0),
        output_field=IntegerField()
    )
)

}}}

Insofar as query is lazy this filter converting in one SQL request, not three. But it still not really comfortable and not understandable why first version didn't work in the same way as second one."	Bug	closed	Database layer (models, ORM)	3.1	Normal	duplicate	duplicate annotate case when		Unreviewed	0	0	0	0	0	0