如何用Laravel Eloquent关联或JOIN高效获取聊天数据？

背景信息 #

我正在Laravel 11中尝试用Eloquent获取聊天相关数据，但难以构建基于Eloquent关联或JOIN的优化查询。

现有两个MySQL表：

• users：id, full_name, profile_pic
• chat_messages：id, sender_id, receiver_id, message, created_at, read（0=未读，1=已读）

期望输出 #

获取指定发送者（示例中sender_id=1）的如下格式数据：

{
  "user": {
    "id": 1,
    "full_name": "Sender Name",
    "profile_pic": "sender profile pic url"
  },
  "chat_message": {
    "unread": 3, // 发送者sender_id=1的未读消息数量
    "last_message": "whatever it was", // 发送者sender_id=1发送的最后一条消息
    "created_at": "last message's sent time" // 最后一条消息的发送时间
  }
}

已验证可用的原生SQL查询 #

-- 获取用户详情
SELECT id, full_name, profile_pic FROM users WHERE id = 196;

-- 统计发送者196的未读消息数量（接收者为197）
SELECT COUNT(id) FROM chat_messages WHERE read = 0 AND sender_id = 196 AND receiver_id = 197;

-- 获取发送者196的最后一条消息（接收者为197）
SELECT message, created_at 
FROM chat_messages 
WHERE sender_id = 196 AND receiver_id = 197
ORDER BY id DESC 
LIMIT 1;

我的疑问 #

1. 是否有高效方式用Eloquent关联或JOIN替代上述多个原生查询？
2. 如何在Laravel 11中对此查询进行性能优化？
3. JOIN的底层工作原理是什么？是MySQL逐行读取表1数据，再匹配表2行后处理下一行？还是一次性读取表1所有结果，再基于约束与表2关联？

测试用数据表结构及数据 #

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');

内容的提问来源于stack exchange，提问作者Uzair Usman