mysqllaraveljoineloquent

How to Fetch Chat Data Using Eloquent with Joins?

I'm trying to fetch chat-related data using Eloquent in Laravel 11 but I'm struggling to structure an optimized query using Eloquent relationships or joins.

I have two MySQL tables:

  1. users -> id, full_name, profile_pic
  2. chat_messages -> id, sender_id, receiver_id, message, created_at, read (0 = unread, 1 = read)

Desired Output: I want to fetch the following data for a specific sender (sender_id = 1):

{
  "user": {
    "id": 1,
    "full_name": "Sender Name",
    "profile_pic": "sender profile pic url"
  },
  "chat_message": {
    "unread": 3, // count of unread messages from sender_id = 1
    "last_message": "whatever it was", // last message sent by sender_id = 1
    "created_at": "last message's sent time"
  }
}

Raw Queries That Work:

// Fetch user details
SELECT id, full_name, profile_pic FROM users WHERE id = 196;

// Count unread messages from sender_id = 196
SELECT COUNT(id) FROM chat_messages WHERE read = 0 AND sender_id = 196 AND receiver_id = 197;

// Get last message from sender_id = 196
SELECT message, created_at 
FROM chat_messages 
WHERE sender_id = 196 AND receiver_id = 197
ORDER BY id DESC 
LIMIT 1;

My Question:

Is there an efficient way to achieve this using Eloquent relationships or joins instead of multiple raw queries?

How can I optimize this for performance in Laravel 11?

Additional Query About Joins: How does a JOIN actually work in-depth?

Does MySQL fetch each row from Table 1 one by one and then look for a matching row in Table 2 before moving to the next row?

Or does it fetch all results from Table 1 at once and then join them with Table 2 based on constraints? Any help or insights would be greatly appreciated! Thanks in advance. 🙌

Table with dummy data for testing.

CREATE TABLE `chat_messages` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `sender_id` bigint(20) UNSIGNED NOT NULL,
  `receiver_id` bigint(20) UNSIGNED NOT NULL,
  `message` text NOT NULL,
  `read` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `chat_messages` (`id`, `sender_id`, `receiver_id`, `message`, `read`, `created_at`) VALUES
(1, 196, 197, 'Some message 1', 0, '2025-02-14 00:04:47'),
(2, 196, 197, 'Some message 2', 0, '2025-02-14 00:10:02'),
(3, 196, 197, 'Some message 3', 0, '2025-02-14 00:11:06'),
(4, 197, 196, 'Some message 4', 0, '2025-02-14 00:11:31'),
(5, 196, 197, 'Some message 5', 0, '2025-02-14 00:13:11'),
(6, 197, 196, 'Some message 6', 0, '2025-02-14 00:13:15'),
(7, 196, 197, 'Some message 7', 0, '2025-02-14 00:13:51'),
(8, 197, 196, 'Some message 8', 0, '2025-02-14 00:14:04'),
(9, 196, 197, 'Some message 9', 0, '2025-02-14 00:14:13'),
(10, 197, 196, 'Some message 10', 0, '2025-02-14 00:14:20'),
(11, 196, 197, 'Some message 11', 0, '2025-02-14 00:14:40'),
(12, 197, 196, 'Some message 12', 0, '2025-02-14 00:14:47'),
(13, 197, 196, 'Some message 13', 0, '2025-02-14 00:15:02'),
(14, 196, 197, 'Some message 14', 0, '2025-02-14 00:15:11'),
(15, 196, 197, 'Some message 15', 0, '2025-02-14 00:25:08'),
(16, 197, 196, 'Some message 16', 0, '2025-02-14 00:25:15'),
(17, 196, 197, 'Some message 17', 0, '2025-02-14 00:25:30'),
(18, 196, 197, 'Some message 18', 0, '2025-02-14 00:27:48'),
(19, 197, 196, 'Some message 19', 0, '2025-02-14 00:27:53'),
(20, 196, 197, 'Some message 20', 0, '2025-02-14 00:27:58'),
(21, 196, 197, 'Some message 21', 0, '2025-02-14 00:28:59'),
(22, 197, 196, 'Some message 22', 0, '2025-02-14 00:29:01'),
(23, 196, 197, 'Some message 23', 0, '2025-02-14 00:29:15'),
(24, 197, 196, 'last message from 197', 0, '2025-02-14 00:29:18'),
(25, 196, 197, 'last message from 196', 0, '2025-02-14 00:29:23');
Solution

  • You can do it this way...

    1. Change Your User.php(User Model) like this...

    class User extends Model
{
    public function chatMessages()
    {
        return $this->hasMany(ChatMessage::class, 'sender_id');
    }

    public function unreadMessages($receiverId)
    {
        return $this->hasMany(ChatMessage::class, 'sender_id')
            ->where('receiver_id', $receiverId)
            ->where('read', 0);
    }

    public function lastMessage($receiverId)
    {
        return $this->hasMany(ChatMessage::class, 'sender_id')
            ->where('receiver_id', $receiverId)
            ->orderByDesc('id')
            ->limit(1);
    }
}

    2. Define Relationship in ChatMessage Model(i.e. ChatMessage.php)* like this...

    class ChatMessage extends Model
{
    protected $fillable = ['sender_id', 'receiver_id', 'message', 'created_at', 'read'];

    public function sender()
    {
        return $this->belongsTo(User::class, 'sender_id');
    }
}

    Then base on sender_id and receiver_id you can fetch data like this..

    $user = User::with([
    'chatMessages' => function ($query) use ($receiverId) {
        $query->where('receiver_id', $receiverId);
    }
])
->where('id', $senderId)
->first();

// Count unread messages
$unreadCount = $user->unreadMessages($receiverId)->count();

// Get last message
$lastMessage = $user->lastMessage($receiverId)->first();

    How does a JOIN actually work in-depth?

    MySQL does not fetch all rows from Table 1 at once. Instead, it iterates over each row of Table-1 and finds matching rows in Table-2 .If there's no index, MySQL scans the entire Table-2 for each row from Table-1.Then moves on to the next row in table-1.