Laravel 10中如何用MySQL存储过程获取模型关联数据？

以下是三种适配你技术环境的实现思路，可根据业务场景选择：

方案1：存储过程返回多结果集（推荐批量数据场景） #

利用MySQL 8.0支持多结果集返回的特性，在存储过程中依次输出主表和各关联表数据，再在Laravel中手动关联。

存储过程示例 #

CREATE PROCEDURE sp_taskGetWithRelations(IN target_task_id INT)
BEGIN
    -- 返回Task主表数据
    SELECT * FROM tasks WHERE id = target_task_id;
    -- 返回关联的创建者
    SELECT u.* FROM users u JOIN tasks t ON u.id = t.creator_id WHERE t.id = target_task_id;
    -- 返回关联的父任务
    SELECT * FROM tasks WHERE id = (SELECT parent_task_id FROM tasks WHERE id = target_task_id);
    -- 返回关联的分类
    SELECT c.* FROM categories c JOIN tasks t ON c.id = t.category_id WHERE t.id = target_task_id;
    -- 返回关联的任务用户
    SELECT * FROM task_users WHERE task_id = target_task_id;
END;

Laravel仓库层调用代码 #

public function getTaskWithRelations(int $taskId)
{
    $pdo = DB::getPdo();
    $stmt = $pdo->prepare('CALL sp_taskGetWithRelations(:taskId)');
    $stmt->execute(['taskId' => $taskId]);

    // 提取主表数据
    $task = $stmt->fetch(PDO::FETCH_OBJ);
    if (!$task) return null;

    // 切换到下一个结果集，提取创建者
    $stmt->nextRowset();
    $task->creator = $stmt->fetch(PDO::FETCH_OBJ);

    // 切换结果集提取父任务
    $stmt->nextRowset();
    $task->parentTask = $stmt->fetch(PDO::FETCH_OBJ);

    // 切换结果集提取分类
    $stmt->nextRowset();
    $task->category = $stmt->fetch(PDO::FETCH_OBJ);

    // 切换结果集提取任务用户列表
    $stmt->nextRowset();
    $task->taskUsers = $stmt->fetchAll(PDO::FETCH_OBJ);

    return $task;
}

方案2：存储过程用JSON聚合返回结构化数据（推荐单条数据场景） #

借助MySQL 8.0的JSON函数，将关联数据聚合为JSON对象/数组，直接返回包含所有关联信息的单条结果。

存储过程示例 #

CREATE PROCEDURE sp_taskGetWithRelations(IN target_task_id INT)
BEGIN
    SELECT 
        t.*,
        -- 一对一关联转JSON对象
        COALESCE(JSON_OBJECT('id', u.id, 'name', u.name, 'email', u.email), JSON_OBJECT()) AS creator,
        COALESCE(JSON_OBJECT('id', pt.id, 'title', pt.title), JSON_OBJECT()) AS parentTask,
        COALESCE(JSON_OBJECT('id', c.id, 'name', c.name), JSON_OBJECT()) AS category,
        -- 一对多关联转JSON数组
        COALESCE(JSON_ARRAYAGG(JSON_OBJECT('user_id', tu.user_id, 'role', tu.role)), JSON_ARRAY()) AS taskUsers
    FROM tasks t
    LEFT JOIN users u ON t.creator_id = u.id
    LEFT JOIN tasks pt ON t.parent_task_id = pt.id
    LEFT JOIN categories c ON t.category_id = c.id
    LEFT JOIN task_users tu ON t.id = tu.task_id
    WHERE t.id = target_task_id
    GROUP BY t.id;
END;

Laravel仓库层调用代码 #

public function getTaskWithRelations(int $taskId)
{
    $result = DB::selectOne('CALL sp_taskGetWithRelations(?)', [$taskId]);
    if (!$result) return null;

    // 将JSON字段转为对象/数组
    $result->creator = json_decode($result->creator);
    $result->parentTask = json_decode($result->parentTask);
    $result->category = json_decode($result->category);
    $result->taskUsers = json_decode($result->taskUsers);

    return $result;
}

方案3：拆分调用多个存储过程（适合关联逻辑复杂场景） #

将主表和各关联数据的查询拆分为独立存储过程，在Laravel仓库层依次调用后手动组装数据。

Laravel仓库层示例代码 #

public function getTaskWithRelations(int $taskId)
{
    $task = DB::selectOne('CALL sp_taskGet(?)', [$taskId]);
    if (!$task) return null;

    // 调用对应存储过程获取关联数据
    $task->creator = DB::selectOne('CALL sp_getUserById(?)', [$task->creator_id]);
    $task->parentTask = $task->parent_task_id ? DB::selectOne('CALL sp_taskGet(?)', [$task->parent_task_id]) : null;
    $task->category = DB::selectOne('CALL sp_getCategoryById(?)', [$task->category_id]);
    $task->taskUsers = DB::select('CALL sp_getTaskUsersByTaskId(?)', [$task->id]);

    return $task;
}

注意事项 #

• 存储过程中需用COALESCE处理关联数据不存在的情况，避免返回NULL导致JSON解析错误
• 批量获取Task时，优先选择方案1，避免N+1查询问题
• 确保Laravel的PDO配置支持多结果集（默认配置已支持）

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