# 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:** 20\
**Page:** 1

<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 16, 2023, 3:00pm UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/1 "2023-02-16T15:00:49Z")

</div>

I am trying to get the messages that belongs to a user, something that look like this

 ![image](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/2X/e/e89f5ec16dda2ddfe9069375e39769668bf2ee3b.jpeg)

I am finding it difficult to write the filter query, according to this [Answer](https://forum.djangoproject.com/t/django-filter-query-to-get-chat-messages-among-two-friends/18868/2), i wrote a query that looks like this

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

    def get_queryset(self):
        user_id = self.kwargs['user_id']
        
        messages = ChatMessage.objects.filter(Q(sender__in=user_id) | Q(reciever__in=user_id)).order_by("-date")
        return messages

```

This is my response

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

what i am trying to do is this, get the latest message that i had with sam, also the latest message that i had with moz and not all the messages. I am trying to get this messages because i want to loop through the message just like in the screenshot above.

This is my model

```auto

class ChatMessage(models.Model):
    user = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, related_name="user")
    sender = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, related_name="sender")
    reciever = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, related_name="reciever")
    order = models.ForeignKey(Orders, on_delete=models.SET_NULL, null=True, blank=True)

    message = models.CharField(max_length=10000000000)

    is_read = models.BooleanField(default=False)
    date = models.DateTimeField(auto_now_add=True)
    mid = ShortUUIDField(length=10, max_length=25, alphabet="abcdefghijklmnopqrstuvxyz")

```

Please how do i go about this?

---

<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 16, 2023, 4:20pm UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/2 "2023-02-16T16:20:34Z")

</div>

I’m sorry, I’m not sure I understand exactly what messages you want.

Are you looking for the latest message for _every_ person that you have sent or received a message with?

In other words, if you’ve sent messages to 10 people, you want _one_ message from each conversation, each message having the most recent date - which means you’d have a list of 10 messages. Is that correct?

---

<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 16, 2023, 4:28pm UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/3 "2023-02-16T16:28:07Z")

</div>

Yes, that is perfectly correct and that is what i want

---

<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 16, 2023, 8:40pm UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/4 "2023-02-16T20:40:40Z")

</div>

As a side note, what is the purpose or function of the `user` field in that model? What information does it convey that isn’t covered by either the `sender` or `receiver` field?

---

<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 16, 2023, 10:13pm UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/5 "2023-02-16T22:13:23Z")

</div>

The user field stores the same information as the sender field, i added it hoping that maybe i would be able to use it to somehow to get the latest message for _every_ person that i have sent or received a message with.

---

<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, 12:07am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/6 "2023-02-17T00:07:17Z")

</div>

Ok, so I can ignore it then. (Being a duplicate of an existing field doesn’t provide any benefits, but it’s not going to hurt anything, either.)

Unfortunately, your table structure doesn’t really facility queries like this, so this is going to be a bit complicated to explain. What I’ve come up with requires nested subqueries.

The outermost query will be a query on ChatMessage to retrieve the actual “latest message” from each user.  
The first Subquery to that is to identify all the User object that you have exchanged messages with.  
The innermost Subquery will be to find the latest message between you and each user.

Explaining this is going to take a couple of posts here, so we’re going to work this through step by step.

Assume you have two user objects, let’s call them `you` and `me`.

Can you write a query that will retrieve the most recent message between those two users?  
(Not all messages, just the most recent one.)

---

<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, 12:26am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/7 "2023-02-17T00:26:14Z")

</div>

> [@KenWhitesell](#):
>
> Can you write a query that will retrieve the most recent message between those two users?  
> (Not all messages, just the most recent one.)

```auto
you = self.kwargs['you ']
me= self.kwargs['me']
recent_messages = ChatMessage.objects.filter(sender__in=[you, me], reciever__in=[you, me])
.order_by('-date')[:1]

```

`[:1]` returns a slice of the queryset, which when **order\_by(‘-date’)** would return the last message (which is the current message)

> [@KenWhitesell](#):
>
> Unfortunately, your table structure doesn’t really facility queries like this, so this is going to be a bit complicated to explain

as much i wuold want to learn and understand this **new nested query** method of solving this problem, we can go ahead an make any adjustment to my table to make it easier for you sir.

---

<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, 12:33am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/8 "2023-02-17T00:33:13Z")

</div>

Great! We’re going to end up using that query as a subquery. But first, …

Using the same idea of having a “me” (you don’t need to repeat the definitions), can you write a query that gives you a list of every `User` that “me” has exchanged messages with?

Side note: Whether or not it’s worth refactoring your model would depend upon how many queries like this that you’re going to need to generate. If this is the most “unique” query you need to write, I wouldn’t bother.

---

<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, 12:40am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/9 "2023-02-17T00:40:20Z")

</div>

> [@KenWhitesell](#):
>
> can you write a query that gives you a list of every `User` that “me” has exchanged messages with?

```auto
user_list = ChatMessage.objects.filter(Q(sender__in=me) | Q(reciever__in=me))

```

This is what i could come up with…?

---

<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, 12:46am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/10 "2023-02-17T00:46:20Z")

</div>

That query is going to return you a queryset of ChatMessage, not of User.

The query you’re actually looking for here would be something like:  
`User.objects.filter(Q(sender__receiver=me)|Q(receiver__sender=me)).distinct()`

Do you understand what this query is doing?

---

<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:03am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/11 "2023-02-17T01:03:42Z")

</div>

I did not use the User model, because the user Model does not have the sender and reciever fields in the it, i got confused there.

> [@KenWhitesell](#):
>
> Do you understand what this query is doing?

i have a little idea, this `sender__receiver=me` would check if `me` is in the `sender’s receiver using the lookup ( \_\_ ) and vise-versa

The `.distinct()` method would remove duplicated results and return unique results

---

<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:24am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/12 "2023-02-17T01:24:51Z")

</div>

Yep, you got the basic idea.

So now, we have a query that will give us a list of every user that has chatted with some specific user.

We want to combine this query with the first one - this will give you a list of the users that you have chatted with, annotated with the id of the latest ChatMessage between you and that user:

```auto
User.objects.filter(
  Q(sender__receiver=me)|Q(receiver__sender=me)
).distinct().annotate(
  last_msg=Subquery(
    ChatMessage.objects.filter(
      sender__in=[OuterRef('id'), me], reciever__in=[OuterRef('id'), me]
    )
  ).order_by('-date')[:1].values_list('id',flat=True)
)

```

Are you still with me so far? (There’s still one more step to go - we’re almost done!)

---

<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:28am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/13 "2023-02-17T01:28:19Z")

</div>

yes i am still with you, i am going through the code block 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 17, 2023, 1:32am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/14 "2023-02-17T01:32:02Z")

</div>

> [@KenWhitesell](#):
>
> (There’s still one more step to go - we’re almost done!)

Okay, i am ready

> [@KenWhitesell](#):
>
> ```auto
> User.objects.filter(
> Q(sender__receiver=me)|Q(receiver__sender=me)
> )
> 
> ```

Isn’t there going to be an error with this query, or are we going to do something about it later?

* * *

### This is the full code up-till now

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

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

        return User.objects.filter(
            Q(sender__receiver=user_id)|Q(receiver__sender=user_id)
            ).distinct().annotate(
            last_msg=Subquery(
                ChatMessage.objects.filter(
                sender__in=[OuterRef('id'), user_id], reciever__in=[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, 1:33am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/15 "2023-02-17T01:33:44Z")

</div>

> [@desphixs](#):
>
> Isn’t there going to be an error with this query, or are we going to do something about it later?

Why do you think there’s an error here?

---

<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:35am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/16 "2023-02-17T01:35:34Z")

</div>

> [@KenWhitesell](#):
>
> Why do you think there’s an error here?

Because the User Model does not have the `sender` or the `receiver` field, if i view the api on browser now, it shows

```auto
FieldError at /api/my-messages/1/
Related Field got invalid lookup: receiver

```

This is my User Model

```auto
class User(AbstractUser):
    username = models.CharField(max_length=100)
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)
    verified = models.BooleanField(default=False)
    email = models.EmailField(unique=True)
    is_staff = models.BooleanField(default=False)
    is_superuser = models.BooleanField(default=False)

```

---

<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:39am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/17 "2023-02-17T01:39:06Z")

</div>

Ok, I’ve been typing “receiver” (which is the correct spelling of that word), where you have “reciever” - you should change every reference I have to a “receiver” to “reciever”.

You have these fields by these names on the User model because of your definition of the `related_name` attribute in the ForeignKey fields in the ChatMessage model.

You might want to review the docs at [Related objects reference | Django documentation | Django](https://docs.djangoproject.com/en/4.1/ref/models/relations/#related-objects-reference).

---

<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:42am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/18 "2023-02-17T01:42:27Z")

</div>

> [@KenWhitesell](#):
>
> Ok, I’ve been typing “receiver” (which is the correct spelling of that word), where you have “reciever” - you should change every reference I have to a “receiver” to “reciever”.

Okay, i have changed the receiver name error _(i would change to the correct spelling when we are done)_ and it stop showing the invalid lookup error

It showed this one now

```auto
TypeError at /api/my-messages/1/
Field 'id' expected a number but got ResolvedOuterRef(id).

```

> [@KenWhitesell](#):
>
> You have these fields by these names on the User model because of your definition of the `related_name` attribute in the ForeignKey fields in the ChatMessage model.

Ohh okay

> [@KenWhitesell](#):
>
> You might want to review the docs at [Related objects reference | Django documentation | Django](https://docs.djangoproject.com/en/4.1/ref/models/relations/#related-objects-reference).

I would read about this now

---

<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:45am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/19 "2023-02-17T01:45:08Z")

</div>

Ok, that should probably be `F(OuterRef('id'))` in both cases instead of just `OuterRef('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, 1:50am UTC](https://forum.djangoproject.com/t/how-to-filter-the-inbox-messages-that-belong-to-a-user/18887/20 "2023-02-17T01:50:40Z")

</div>

my bad, but it popped another one up

```auto
'OuterRef' object has no attribute 'split'

```

could it be because of the F expression that i added now?

Does it have something to do with this?[Query Expressions | Django documentation | Django](https://docs.djangoproject.com/en/4.1/ref/models/expressions/#django.db.models.OuterRef)

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