Annotate Case(When()) duplicate objects.

[Vladimir Kuznetsov]

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.

[Mariusz Felisiak]

This is an expected behavior, see comment. Duplicate of #29271.