主页 / coding / 问题 / 79438303
Accepted
Uzair Usman
Asked: 2025-02-14 11:36:04 +0800 CST

如何使用 Eloquent 连接来获取聊天数据？

  • 772

我正在尝试使用 Laravel 11 中的 Eloquent 获取与聊天相关的数据，但我正在努力使用 Eloquent 关系或连接构建优化查询。

我有两个 MySQL 表：

  1. 用户 -> ID、全名、个人资料图片
  2. 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, // 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"
  }
}

有效的原始查询：

// 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;

我的问题：

有没有一种有效的方法可以使用 Eloquent 关系或连接而不是多个原始查询来实现这一点？

我该如何优化 Laravel 11 的性能？

关于连接的附加查询：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');
mysql

1 个回答

  1. Best Answer

    你可以这样做...

    1.User.php(User Model)像这样改变你……

    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.在 ChatMessage 模型（即 ChatMessage.php）中定义关系* 如下...

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

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

    然后基于sender_id此receiver_id您可以获取这样的数据..

    $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();

    JOIN 实际上如何深入工作？

    MySQL 不会一次性从表 1 中获取所有行。相反，它会遍历 的每一行Table-1并在 中找到匹配的行Table-2。如果没有索引，MySQL则扫描Table-2中的每一行Table-1。然后转到 中的下一行table-1。

    • 1

相关问题