How to show the last message of each user to user conversations to keep a chat history?

Question

I'm creating a private user to user chat, in order to chat with someone the connected user has to type the username of the user with whom he wants to talk to on his own url.

Now that this system is already built, I want to keep a chat history so that later on I can send notification of chat. To do that I need to get the last message of each conversations and I want to show it on the connected user's own chat profile.

Just as the image below :

Model userComment fields are : recipient, sender, comment, sent_at

views.py :

def inbox(request, username):
    username = User.objects.get(username=username)
    connected_user = request.user

    if username == connected_user:

        #I'm having the issue on this line
        users = userComment.objects.filter(Q(client=request.user) | Q(worker=request.user)).order_by(?) 

    else:
        users = userComment.objects.filter(Q(Q(client=request.user) & Q(worker=username)) | Q(Q(client=username) & Q(worker=request.user))).order_by('sent_at')

models.py

class userComment(models.Model):
    client = models.ForeignKey(User, related_name="client")
    worker = models.ForeignKey(User, blank=True, null=True, related_name="worker")
    sent_at = models.DateTimeField(auto_now_add=True)
    comment = models.TextField(max_length=255, null=True)

    def __str__(self):
        return str(self.client)

Question : How can I filter and order my view to do so ?

Answer 1

Firstly in your userComment model add a related query name for reverse relation

class UserComment(models.Model):
    sender = models.ForeignKey(User, related_name='sender', related_query_name='s')
    recipient = models.ForeignKey(User, related_name='recipient', related_query_name='r')
    sent_at = models.DateTimeField(auto_now_add=True)
    comment = models.TextField()

Now in your views.py use this query:

user = request.user

users = User.objects.filter(Q(r__sender=user) | Q(s__recipient=user)).distinct().extra(select={'last_message_time': 'select MAX(sent_at) from appname_usercomment where (recipient_id=auth_user.id and sender_id=%s) or (recipient_id=%s and sender_id=auth_user.id)'}, select_params=(user.id, user.id,)).extra(order_by=['-last_message_time']).extra(select={'message': 'select comment from appname_usercomment where (sent_at=(select MAX(sent_at) from appname_usercomment where (recipient_id=auth_user.id and sender_id=%s) or (recipient_id=%s and sender_id=auth_user.id)) and ((recipient_id=auth_user.id and sender_id=%s) or (recipient_id=%s and sender_id=auth_user.id)))',}, select_params=(user.id, user.id,user.id, user.id,))

Set the appname in extra according to name of the app in which the model is.

Now you can access it as follows:

for user in users:
    print user.username
    print user.last_message_time
    print user.message

Answer 2

def inbox(request, username)    
    # first select all the comments related to user
    user = User.objects.get(username=username)
    related = userComment.objects.filter(q(client=user) | q(worker=user)).order_by('-sent_at')

    # This selects the latest comments.
    # Now loop over the related comments and group them.
    chats = {}
    for comment in related:
        if comment.client == user:
            previous_chat_history = chats.setdefault(comment.worker.username, [])
            if not len(previous_chat_history) >= 3:
                previous_chat_history.append(comment)
        if comment.worker== user:
            previous_chat_history = chats.setdefault(comment.client.username, [])
            if not len(previous_chat_history) >= 3:
                previous_chat_history.append(comment)

    # Reverse each list to keep the latest message last
    for k, v in chats.items():
        chats[k] = v.reverse()

    return render(request, 'template.html', context={chats: chats}) 

Answer 3

[Update]: I just realized that this solution will only work with Postgresql because it is using field names in distinct.

---

You can mix order_by and distinct to achieve the desired results:

1. filter the comments where the user is either client or a worker:

   comments = userComment.objects.filter(Q(client=request.user) | Q(worker=request.user))
   

2. order the user comments with client, worker and sent_at fields. Make sure to have a descending order for sent_at field so the latest comments for each client-worker pair are at top:

   comments = comments.order_by('client', 'worker', '-sent_at')
   

3. Now, get the distinct rows:

   comments = comments.distinct('client', 'worker')
   

   This will keep only the first row which is the latest comment for each for each client-worker pair and delete the rest of the rows from the queryset.

---

In one statement:

comments = userComment.objects \
              .filter(Q(client=request.user) | Q(worker=request.user)) \
              .order_by('client', 'worker', '-sent_at') \
              .distinct('client', 'worker')

This will give you the latest comment for each conversation where the user is either a client or a worker.