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.