1

Whenever I use Annotate with Case/When logic to return a boolean, the resulting queryset is almost twice as long.

Model :

class Message(models.Model):
    readers = models.ManyToManyField(Compte, related_name='messages')

Message.objects.count() // return 495

Then :

qs = Message.objects.annotate(read=Case(
         When(readers=Compte.objects.first(), then=Value(True),
         default=Value(False),
         outputfield=BooleanField()))

qs.count() // return 940

What am I missing here ? Django 2.2.9 / PostgreSQL

Benoit CHAUVET
  • 11
  • 2
  • 1
    It's because each `Message` records could have multiple `Compte` records related through M2M so your result is duplicated with same messages but different related `Compte`. – Charnel Mar 25 '20 at 10:38
  • Not sure I understand, but my goal is to annotate the ```Queryset``` to know for each ```Message``` if a ```Compte``` is among the ```Message.readers```. Can you point me to the right direction ? – Benoit CHAUVET Mar 25 '20 at 10:43
  • 2
    @BenoitCHAUVET: it will make a join with the m2m table, and thus introduce duplicates. – Willem Van Onsem Mar 25 '20 at 10:46

1 Answers1

1

The reason this fails is because it will make a JOIN with the junction table and then annotate each of the combinations with True or False.

You might want to use Exists here:

from django.db.models import Exists, OuterRef

compte_first = Compte.objects.first()

subquery = Message.readers.model.filter(
    message_id=OuterRef('pk'),
    compte_id=compte_first.pk
)

Message.objects.annotate(read=Exists(subquery))
Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
  • Thanks for your answers @Willem @Charnel, I should have taken a look at the raw SQL to see my mistake. Now my annotate works fine ! ```Message.objects.annotate(read=Exists(Message.objects.filter(id=OuterRef('pk'), readers=self.request.user.compte)))``` – Benoit CHAUVET Mar 25 '20 at 12:01