0

I have a model ChatMessage that has a field sender which is a ForeignKey to User model.

I'm trying to annotate a number of all the ChatMessage objects that haven't been read (eg. have seen_at__isnull=True).

For a given user, there is only one sent message with seen_at__isnull=True but Django returns 11.

User.objects.select_related(...).annotate(
            sent_unread_messages=Count('sent_chat_messages',
                                       filter=Q(sent_chat_messages__seen_at__isnull=True))).get(pk=1234).sent_unread_messages

do you know where is the problem?

EDIT:

class ChatMessageManager(models.Manager):
    def get_queryset(self) -> models.QuerySet:
        return super().get_queryset().select_related('sender', 'recipient')

    def as_sender_or_recipient(self, user) -> models.QuerySet:
        return self.get_queryset().filter(Q(sender=user) | Q(recipient=user))

class ChatMessage(BaseModel):
    objects = ChatMessageManager()
    sender = models.ForeignKey('users.User', verbose_name='Odosielateľ', null=True, blank=True,
                               on_delete=models.SET_NULL, related_name='sent_chat_messages')

    recipient = models.ForeignKey('users.User', verbose_name='Adresát', null=True, blank=True,
                                  on_delete=models.SET_NULL, related_name='received_chat_messages')

    content = models.TextField('Obsah')
    attachment = models.FileField('Príloha', null=True, blank=True)
    attachment_filename = models.CharField('Názov prílohy', null=True, blank=True, max_length=128)
    meta = models.JSONField(verbose_name='Meta', null=True, blank=True, help_text='must be JSON')
    seen_at = models.DateTimeField('Prečítané o', null=True, blank=True)



class CustomUserManager(UserManager):
    def get_queryset(self):
        return super().get_queryset().select_related('staff_profile', 'client_profile').annotate(
            sent_unread_messages=Count('sent_chat_messages',
                                       filter=Q(sent_chat_messages__seen_at__isnull=True))).annotate(
            received_unread_messages=Count('received_chat_messages',
                                           filter=Q(received_chat_messages__seen_at__isnull=True))).annotate(
            sent_latest_message=Subquery(
                ChatMessage.objects.filter(sender=OuterRef('pk')).order_by('-created').values('content')[:1])).annotate(
            sent_latest_message_dt=Subquery(
                ChatMessage.objects.filter(sender=OuterRef('pk')).order_by('-created').values('created')[:1])).annotate(
            received_latest_message=Subquery(
                ChatMessage.objects.filter(recipient=OuterRef('pk')).order_by('-created').values('content')[
                :1])).annotate(
            received_latest_message_dt=Subquery(
                ChatMessage.objects.filter(recipient=OuterRef('pk')).order_by('-created').values('created')[:1]))
Milano
  • 18,048
  • 37
  • 153
  • 353
  • Can you share your models and the rest of your query? How do you know that there should only be one seen message, what does `ChatMessage.objects.filter(sender_id=1234, seen_at__isnull=True).count()` return? – Iain Shelvington May 29 '22 at 13:42
  • @IainShelvington I've added ChatMessage model and User manager. – Milano May 29 '22 at 13:45
  • That's a lot of annotations on your default manager, if you remove all of them and just do the one annotation in your example do you get the correct result? – Iain Shelvington May 29 '22 at 13:51
  • Nope, it's the same. Moreover, if I change `Count('sent_chat_messages',filter...)` to `Count('received_chat_messages',filter...)` I get the same result (11). – Milano May 29 '22 at 13:52
  • @IainShelvington omg, the solution was pretty easy - `.annotate( sent_unread_messages=Count('sent_chat_messages', filter=Q(sent_chat_messages__seen_at__isnull=True), distinct=True))` – Milano May 29 '22 at 14:01

1 Answers1

0

can you try using .distinct() method (which removes the duplicate elements from a queryset) when getting the messages ?

gorkem
  • 53
  • 4