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
