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

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

I am finding it difficult to write the filter query, according to this Answer, i wrote a query that looks like this

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

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


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?

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?

1 Like

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

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?

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.

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.)

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)

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.

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.

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

This is what i could come up with…?

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?

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.

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

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:

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!)

yes i am still with you, i am going through the code block now

Okay, i am ready

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

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)
            )

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

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

This is my User Model

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)

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.

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

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

Ohh okay

I would read about this now

Ok, that should probably be F(OuterRef('id')) in both cases instead of just OuterRef('id')

my bad, but it popped another one up

'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