[Django] #31988: Annotate Case(When()) duplicate objects

[Django]

#31988: Annotate Case(When()) duplicate objects
-------------------------------------+-------------------------------------
Reporter: Vladimir | Owner: nobody
Kuznetsov |
Type: Bug | Status: new
Component: Database | Version: 3.1
layer (models, ORM) | Keywords: duplicate annotate
Severity: Normal | case when
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.

--
Ticket URL: <https://code.djangoproject.com/ticket/31988>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#31988: Annotate Case(When()) duplicate objects.
-------------------------------------+-------------------------------------
Reporter: Vladimir Kuznetsov | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: duplicate annotate | Triage Stage:
case when | Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* status: new => closed
* resolution: => duplicate

Comment:

This is an expected behavior, see
[https://code.djangoproject.com/ticket/29271#comment:3 comment]. Duplicate
of #29271.

--
Ticket URL: <https://code.djangoproject.com/ticket/31988#comment:1>