Get the latest Message of Chat Model with MySQL. Just cannot get the idea how to do this.

Assuming you have an Eloquent model name ChatMessage, this should work:

$message = ChatMessage::latest()->where('sender_id', 1)->get();

@sutherland That would be easy but don't forget it is >where('sender_id', 1)->orWhere('receiver_id',1)

But I would get ALL messages back, but I just want to get the last one.

So in your case I didn't get the receiver and I got a message that is too old.

Oops, you can change ->get() to ->first()

Edit: So the complete thing would be:

$message = ChatMessage::latest()->where('sender_id', 1)->orWhere('receiver_id', 1)->first();

Thank you for your fast reply, but still no @sutherland. First() get's me only one result

#original: array:7 [▼
"id" => 5
"sender_id" => 1
"receiver_id" => 4
"content" => "Hey Jake"
"read" => 0
"created_at" => "2018-01-16 18:09:19"
"updated_at" => "2018-01-16 18:09:19"
]

But I expect of course Row ID 2 as well. So not just one.

To explain it again, it can be with id 1

 sender_id receiver_id
      1         2
      3         1
      1         4
     12         1

Should get me four results

I think I see what you're asking now, and I think it would require a fairly complex query. My first instinct would be to try using groupBy() but then you'll still get two messages for each conversation: the latest where the user is the sender and the latest where the user is the receiver.

If it's not impossible to change your schema I'd recommend adding a Conversation model and make it so that a message belongs to a sender and to a conversation, and use a many to many relationship to assign users to a conversation.

Thank you @sutherland! I've to think about it!

Meanwhile I found a GitHub Project that contained the similar idea: https://github.com/felicianopj/laravel-vue-chat/blob/master/app/Http/Controllers/ChatController.php

But the thing is, that as I can see they don't have a Inbox View of the last messages of all users to one user (as you might see on the first page of the twitter Messages Modal)

Thank you anyway, if I find a solution I will post it here

So I got a solution, but I'm not happy about it. It's pretty complicated SQL but I wish it would be possible to do it with Eloquent. Right now I'm not sure how to do this.

Here is my current solution:

$id = auth()->id();
    $inbox = DB::select('
        SELECT t1.*
        FROM chats AS t1
        INNER JOIN
        (
            SELECT
                LEAST(sender_id, receiver_id) AS sender_id,
                GREATEST(sender_id, receiver_id) AS receiver_id,
                MAX(id) AS max_id
            FROM chats
            GROUP BY
                LEAST(sender_id, receiver_id),
                GREATEST(sender_id, receiver_id)
        ) AS t2
            ON LEAST(t1.sender_id, t1.receiver_id) = t2.sender_id AND
               GREATEST(t1.sender_id, t1.receiver_id) = t2.receiver_id AND
               t1.id = t2.max_id
            WHERE t1.sender_id = ? OR t1.receiver_id = ?
        ', [$id, $id]);

@shadrix can you explain the query

Until today, it is the best solution I have found. Thank you so much

Still the best solution. Thank you.

until this day, it is the best solution I've found!!