如何用Eloquent实现原生SQL查询的相同结果？附数据表示例

看起来你在尝试用Eloquent实现和原生SQL一致的聊天记录查询，但当前的查询代码出了问题。我先根据你提供的数据表结构和数据，整理了几个常见聊天场景的解决方案，你可以对照自己的需求参考：

数据表结构与数据 #

场景1：获取指定用户参与的所有聊天记录（发送/接收） #

如果你的原生SQL是这样的：

SELECT * FROM chats 
WHERE sent_by = 1 OR received_by = 1 
ORDER BY created_at DESC;

对应的Eloquent实现：

$userId = 1;
$list = ChatModel::where('sent_by', $userId)
    ->orWhere('received_by', $userId)
    ->orderBy('created_at', 'desc')
    ->get();

场景2：获取与指定用户对话的联系人列表（带最新消息和未读计数） #

这是聊天系统中很常见的需求，原生SQL大概是：

SELECT 
    CASE WHEN sent_by = 1 THEN received_by ELSE sent_by END AS partner_id,
    MAX(created_at) AS last_message_at,
    COUNT(CASE WHEN received_by = 1 AND `read` = 0 THEN 1 END) AS unread_count,
    SUBSTRING_INDEX(GROUP_CONCAT(message ORDER BY created_at DESC), ',', 1) AS last_message
FROM chats 
WHERE sent_by = 1 OR received_by = 1
GROUP BY partner_id
ORDER BY last_message_at DESC;

对应的Eloquent查询：

$userId = 1;
$list = ChatModel::selectRaw(
    "CASE WHEN sent_by = ? THEN received_by ELSE sent_by END AS partner_id,
     MAX(created_at) AS last_message_at,
     COUNT(CASE WHEN received_by = ? AND `read` = 0 THEN 1 END) AS unread_count,
     SUBSTRING_INDEX(GROUP_CONCAT(message ORDER BY created_at DESC), ',', 1) AS last_message",
    [$userId, $userId]
)
->where(function ($query) use ($userId) {
    $query->where('sent_by', $userId)
          ->orWhere('received_by', $userId);
})
->groupBy('partner_id')
->orderBy('last_message_at', 'desc')
->get();

场景3：获取指定用户的未读消息 #

原生SQL：

SELECT * FROM chats 
WHERE received_by = 1 AND `read` = 0;

Eloquent实现：

$list = ChatModel::where('received_by', 1)
    ->where('read', 0)
    ->get();

注意事项 #

• read是MySQL的保留字，所以在查询中使用时需要用反引号包裹（像上面SQL里的`read`），Eloquent中如果直接用where('read', 0)框架会自动处理，但如果是在selectRaw里一定要加反引号。
• 如果你的原生SQL需求和上面的场景不一样，把你想要的原生SQL语句贴出来，我可以帮你转换成对应的Eloquent写法。

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