Nope, not your bad, my bad.
From the docs on OuterRef
It acts like an
Fexpression 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.
Okay
This is my full API View incase you want to make any refrence to it
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))
Ok, we need to change this filter:
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)
I have changed the filter to the new one which is this
it showed this error
'Subquery' object has no attribute 'order_by'
You’re missing or have a mis-placed close paren ( ) ) somewhere.
okay, i would look for the mistake now
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
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
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']
I’d be happy to, please fill me in on what your query and view looks like so far.
Thanks for your response, this is my current code
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
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.
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:
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
)This is the updated code
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
Please can you help me look at the new code i pasted, i am having a feeling i must have mixed somethings up.
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
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 -
I hope this all makes sense.
it is working perfectly well now, thank you very much.
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 
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:
messages = Message.objects.order_by("unique_pair_id",
"-datetime").distinct("unique_pair_id)
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
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.
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 :
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 :
@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