# how to filter the inbox messages that belong to a user?

**URL:** <https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887>\
**Category:** Using the ORM\
**Created:** [February 16, 2023, 3:00pm UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887 "2023-02-16T15:00:49Z")\
**Posts on this page:** 17\
**Page:** 2

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 17, 2023, 1:55am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/21 "2023-02-17T01:55:51Z")

</div>

Nope, not your bad, _my_ bad.

From the docs on [OuterRef](https://docs.djangoproject.com/en/4.1/ref/models/expressions/#django.db.models.OuterRef)

> It acts like an [`F`](https://docs.djangoproject.com/en/4.1/ref/models/expressions/#django.db.models.F) expression except that the check to see if it refers to a valid field isn’t made until the outer queryset is resolved.

I made a guess as to what the problem was and missed.

I’m also trying to adapt on-the-fly to the query you wrote - it doesn’t quite match what I had created but I thought I could make it work.

I’m not done here - I’m going to have another post for you in about 10 minutes.

---

<div class="post-metadata">

**Author:** ![desphixs](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/desphixs/32/6411_2.png) [@desphixs](https://forum.djangoproject.com/u/desphixs)\
**Post date:** [February 17, 2023, 1:58am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/22 "2023-02-17T01:58:00Z")

</div>

> [@KenWhitesell](#):
>
> I’m not done here - I’m going to have another post for you in about 10 minutes.

Okay

This is my full API View incase you want to make any refrence to it

```auto
class MyInbox(generics.ListCreateAPIView):
    serializer_class = MessageSerializer

    def get_queryset(self):
        user_id = self.kwargs['user_id']

        return User.objects.filter(
            Q(sender__reciever=user_id)|Q(reciever__sender=user_id)
            ).distinct().annotate(
            last_msg=Subquery(
                ChatMessage.objects.filter(
                sender__in=[F(OuterRef('id')), user_id], reciever__in=[F(OuterRef('id')), user_id]
                )
            ).order_by('-date')[:1].values_list('id',flat=True))

```

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 17, 2023, 2:05am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/23 "2023-02-17T02:05:27Z")

</div>

Ok, we need to change this filter:

> [@desphixs](#):
>
> `sender__in=[F(OuterRef('id')), user_id], reciever__in=[F(OuterRef('id')), user_id]`

I can’t seem to find an expression that works with using the OuterRef in the list for the `__in` clause.

That means I’m going to have to ask you to change that filter to:  
`Q(sender=OuterRef('id'),reciever=user_id) | Q(reciever=OuterRef('id'),sender=user_id)`

---

<div class="post-metadata">

**Author:** ![desphixs](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/desphixs/32/6411_2.png) [@desphixs](https://forum.djangoproject.com/u/desphixs)\
**Post date:** [February 17, 2023, 2:12am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/24 "2023-02-17T02:12:34Z")

</div>

I have changed the filter to the new one which is this

> [@KenWhitesell](#):
>
> `Q(sender=OuterRef('id'),reciever=user_id) | Q(reciever=OuterRef('id'),sender=user_id)`

it showed this error

```auto
'Subquery' object has no attribute 'order_by'

```

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 17, 2023, 2:13am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/25 "2023-02-17T02:13:43Z")

</div>

You’re missing or have a mis-placed close paren ( `)` ) somewhere.

---

<div class="post-metadata">

**Author:** ![desphixs](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/desphixs/32/6411_2.png) [@desphixs](https://forum.djangoproject.com/u/desphixs)\
**Post date:** [February 17, 2023, 2:14am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/26 "2023-02-17T02:14:32Z")

</div>

okay, i would look for the mistake now

---

<div class="post-metadata">

**Author:** ![desphixs](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/desphixs/32/6411_2.png) [@desphixs](https://forum.djangoproject.com/u/desphixs)\
**Post date:** [February 18, 2023, 6:15pm UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/27 "2023-02-18T18:15:15Z")

</div>

I have found the error and fixed it, but i seem to be getting this error, i have been trying to fix it myself but i just can’t fix it, please do you have an idea on what is going on, because in my serializer i have the `message` field added in the fields list

```auto
Got AttributeError when attempting to get a value for field `message` on serializer `MessageSerializer`.
The serializer field might be named incorrectly and not match any attribute or key on the `int` instance.
Original exception text was: 'int' object has no attribute 'message'.

```

serializer.py

```auto
class MessageSerializer(serializers.ModelSerializer):
    reciever_profile = ProfileSerializer(read_only=True)
    sender_profile = ProfileSerializer(read_only=True)

    class Meta:
        model = ChatMessage
        fields = ['id','sender', 'reciever', 'reciever_profile', 'sender_profile' ,'message', 'is_read', 'date']

```

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 18, 2023, 7:32pm UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/28 "2023-02-18T19:32:43Z")

</div>

I’d be happy to, please fill me in on what your query and view looks like so far.

---

<div class="post-metadata">

**Author:** ![desphixs](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/desphixs/32/6411_2.png) [@desphixs](https://forum.djangoproject.com/u/desphixs)\
**Post date:** [February 19, 2023, 5:41pm UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/29 "2023-02-19T17:41:34Z")

</div>

Thanks for your response, this is my current code

```auto
class MyInbox(generics.ListAPIView):
    serializer_class = MessageSerializer

    def get_queryset(self):
        user_id = self.kwargs['user_id']

        messages = User.objects.filter(
            Q(sender__reciever=user_id)|Q(reciever__sender=user_id)
                ).distinct().annotate(
                    last_msg=Subquery(
                        ChatMessage.objects.filter(
                            Q(sender=OuterRef('id'),reciever=user_id) |
                            Q(reciever=OuterRef('id'),sender=user_id)
                        )
                    )
                ).order_by('-id')[:1].values_list('id',flat=True)
        
        return messages

```

if i try accessing the api throught this url

```auto
http://127.0.0.1:8000/api/my-messages/1/
# user_id is the /1/

```

it shows the error above, but when i access a user id that does not exist it does not show any error.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 19, 2023, 5:48pm UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/30 "2023-02-19T17:48:42Z")

</div>

So you’re **really** close, but we didn’t get to the last step.

All we’ve got so far are the list of IDs of the messages to be retrieved.

**This** query then becomes a subquery in the final step - retrieve all the messages having these IDs as their PK:

```auto
messages = ChatMessage.objects.filter(
    id__in = Subquery(
        User.objects.filter(
            ... 
           (everything inside the filter and annotate 
            from your existing query above)
            ...
        ).order_by('-id')[:1].values_list('id',flat=True) # From the above query - still need this
)
```

---

<div class="post-metadata">

**Author:** ![desphixs](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/desphixs/32/6411_2.png) [@desphixs](https://forum.djangoproject.com/u/desphixs)\
**Post date:** [February 19, 2023, 6:12pm UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/31 "2023-02-19T18:12:44Z")

</div>

This is the updated code

```auto
    messages = ChatMessage.objects.filter(
            id__in = Subquery(
                User.objects.filter(
                    Q(sender__reciever=user_id)|
                    Q(reciever__sender=user_id)
                        ).distinct().annotate(
                            last_msg=Subquery(
                                ChatMessage.objects.filter(
                                    Q(sender=OuterRef('id'),reciever=user_id) |
                                    Q(reciever=OuterRef('id'),sender=user_id)
                                )
                            )
                    ).order_by('-id')[:1].values_list('id',flat=True) # From the above query - still need this
                )
        )
        
        return messages

```

Hopefully this is how it is supposed to be

Now if i access the endpoint again, it does not show any error and it also does not show any response for the existing users

 ![Screenshot (3)](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/2X/9/90e16345268c1aafde0390f40be321078c22ebae.png)

Please can you help me look at the new code i pasted, i am having a feeling i must have mixed somethings up.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 19, 2023, 6:45pm UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/32 "2023-02-19T18:45:00Z")

</div>

Sorry, I did get a bit confused along the way between these steps. I kinda lost track of where we were.

This should be the correct query

```auto
messages = ChatMessage.objects.filter(
    id__in = Subquery(
        User.objects.filter(
            Q(sender__reciever=user_id) |
            Q(reciever__sender=user_id)
         ).distinct().annotate(
             last_msg=Subquery(
                 ChatMessage.objects.filter(
                     Q(sender=OuterRef('id'),reciever=user_id) |
                     Q(reciever=OuterRef('id'),sender=user_id)
                 ).order_by('-date')[:1].values_list('id',flat=True) 
             )
         ).values_list('last_msg', flat=True)
    )
)
    
return messages

```

To summarize -

- The innermost query gets the most recent messages between each user and the designated user
- The intermediate query gets each user with a message with the designated user. Those results are annotated with the results of the innermost query, which gives us the most recent message between each user and the designated user.
- The outermost query retrieves the full message identified by the intermediate query.

I hope this all makes sense.

---

<div class="post-metadata">

**Author:** ![desphixs](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/desphixs/32/6411_2.png) [@desphixs](https://forum.djangoproject.com/u/desphixs)\
**Post date:** [February 19, 2023, 6:48pm UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/33 "2023-02-19T18:48:29Z")

</div>

it is working perfectly well now, thank you very much.

> [@KenWhitesell](#):
>
> I hope this all makes sense.

Yes it makes sense now, thanks for your summary above, i cleary understood what was going on.

Thanks you very much for your time, it really means alot to me ❤

---

<div class="post-metadata">

**Author:** ![Ilyespes2017](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/ilyespes2017/32/19288_2.png) [@Ilyespes2017](https://forum.djangoproject.com/u/Ilyespes2017)\
**Post date:** [February 18, 2024, 10:07pm UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/34 "2024-02-18T22:07:32Z")

</div>

Hello,

First of all thank you @KenWhitesell, for the proposed solution, at first I had no idea how to do it, yours worked 100% for me.

Otherwise after a few days of use, I noticed that the request was taking a long time (2.70sec to fetch 45 recent messages).

So I started thinking about a solution, and ended up finding one that’s almost 4 times faster.

Basically, I created a field in the table called “unique\_pair\_id”, which sums the two ids of the sender and receiver on\_save, and then the query becomes:

```auto
messages = Message.objects.order_by("unique_pair_id",
                                    "-datetime").distinct("unique_pair_id)

```

```auto
class Message(StandardModel):
    sender = models.ForeignKey(
        User, on_delete=models.CASCADE, related_name="sent_messages"
    )
    receiver = models.ForeignKey(
        User, on_delete=models.CASCADE, related_name="received_messages"
    )
    message = models.CharField(max_length=1000)
    datetime = models.DateTimeField(auto_now_add=True)
    unique_pair_id = models.IntegerField()

    def save(self, *args, **kwargs):
        self.unique_pair_id = self.sender.id + self.receiver.id
        super().save(*args, **kwargs)

```

I’d like to hear your opinion on my solution.  
Thank you again.  
Regards  
Ilyes

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 18, 2024, 10:30pm UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/35 "2024-02-18T22:30:34Z")

</div>

That is subject to collisions between disparate pairs of ids.  
I’m also not seeing how that facilitates getting all messages relating to a specific user.

---

<div class="post-metadata">

**Author:** ![Ilyespes2017](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/ilyespes2017/32/19288_2.png) [@Ilyespes2017](https://forum.djangoproject.com/u/Ilyespes2017)\
**Post date:** [February 19, 2024, 12:58am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/36 "2024-02-19T00:58:53Z")

</div>

1 - Finally you’re right about collisions, I’ll try to think of generating a unique key for each pair (sender, receiver) that will be the same for ( receiver,sender), what do you think about the following :

```auto
def save(self, *args, **kwargs):
    ids = sorted([self.sender.id, self.receiver.id])
    self.unique_pair_id = str(ids[0]) + "," + str(ids[1])
    super().save(*args, **kwargs)

```

2 - Sorry for my first message, which was incomplete and unclear.  
to give more context, here’s an action I use inside my crud Message :

```auto
@action(detail=False, methods=["get"], serializer_class=CustomMessageSerializer)
def get_last_message_with_every_user(self, request, pk=None):
    current_logged_user = self.request.user
    messages = (
        Message.objects.filter(
            Q(receiver_id=current_logged_user) | Q(sender_id=current_logged_user)
        )
        .order_by("unique_pair_id", "-datetime")
        .distinct("unique_pair_id")
    )
    return serialize_and_paginate(self, messages)

```

As the explicite name, this action retrieves all recent messages for the logged user with others

---

<div class="post-metadata">

**Author:** ![meermukhtar](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/meermukhtar/32/28529_2.png) [@meermukhtar](https://forum.djangoproject.com/u/meermukhtar)\
**Post date:** [May 16, 2025, 6:33am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/37 "2025-05-16T06:33:41Z")

</div>

id\_\_in = Subquery(  
User.objects.filter(  
Q(sender\_\_receiver=user\_id) |  
Q(receiver\_\_sender=user\_id)  
).distinct().annotate(  
last\_msg=Subquery(  
ChatMessage.objects.filter(  
Q(sender=OuterRef(‘id’),receiver=user\_id) |  
Q(receiver=OuterRef(‘id’),sender=user\_id)  
).order\_by(‘-id’)[:1].values\_list(‘id’,flat=False)  
)  
).values\_list(‘last\_msg’, flat=True).order\_by(“-id”)  
)  
).order\_by(“-id”)  
Use this it works in my chase perfectly

[Previous page](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887.md?page=1)