SqlDataProvider调用存储过程时分页失效问题求助

这个问题我之前也碰到过，核心原因是SqlDataProvider默认会自动在你的SQL语句末尾拼接LIMIT/OFFSET，但MySQL不允许在CALL存储过程的语句后面直接追加LIMIT，这就导致了你看到的语法错误。下面是几个可行的解决思路：

方案1：修改存储过程，内置分页逻辑（推荐） #

这是最稳妥的方案，适合大数据量场景。我们可以给存储过程添加分页参数，让它内部处理分页，同时单独写一个获取总条数的存储过程来配合DataProvider的分页计算。

步骤1：更新存储过程，添加分页参数 #

首先修改ErroresEnHoras存储过程，加入pageSize和offset参数，在查询末尾加上LIMIT：

DELIMITER //
CREATE PROCEDURE ErroresEnHoras(
    IN project_id INT,
    IN fecha_iniciop DATE,
    IN fecha_finp DATE,
    IN proyecto VARCHAR(255),
    IN pageSize INT,
    IN offset INT
)
BEGIN
    -- 这里保留你原来的查询逻辑，最后追加LIMIT
    SELECT * FROM tu_tabla 
    WHERE 
        -- 你的原有过滤条件
        (project_id = 0 OR tu_tabla.project_id = project_id)
        AND (fecha_iniciop IS NULL OR tu_tabla.fecha >= fecha_iniciop)
        AND (fecha_finp IS NULL OR tu_tabla.fecha <= fecha_finp)
        AND (proyecto IS NULL OR tu_tabla.proyecto = proyecto)
    LIMIT offset, pageSize;
END //
DELIMITER ;

步骤2：创建获取总条数的存储过程 #

为了让DataProvider知道总记录数，我们需要一个单独的存储过程来计算符合条件的总条数：

DELIMITER //
CREATE PROCEDURE ErroresEnHorasCount(
    IN project_id INT,
    IN fecha_iniciop DATE,
    IN fecha_finp DATE,
    IN proyecto VARCHAR(255),
    OUT total INT
)
BEGIN
    SELECT COUNT(*) INTO total FROM tu_tabla 
    WHERE 
        (project_id = 0 OR tu_tabla.project_id = project_id)
        AND (fecha_iniciop IS NULL OR tu_tabla.fecha >= fecha_iniciop)
        AND (fecha_finp IS NULL OR tu_tabla.fecha <= fecha_finp)
        AND (proyecto IS NULL OR tu_tabla.proyecto = proyecto);
END //
DELIMITER ;

步骤3：在PHP代码中调用并配置DataProvider #

先获取总条数，再创建SqlDataProvider并关闭自动分页，手动传递分页参数：

// 获取总记录数
$command = Yii::$app->db->createCommand("CALL ErroresEnHorasCount(:project_id, :fecha_iniciop, :fecha_finp, :proyecto, @total)");
$command->bindValues([
    ':project_id' => ($this->proyectoid == '' || is_null($this->proyectoid)) ? 0 : $this->proyectoid,
    ':fecha_iniciop' => ($filtrosModel->fecha_inicio == '' ? null : $filtrosModel->fecha_inicio ),
    ':fecha_finp' => ($filtrosModel->fecha_fin == '' ? null : $filtrosModel->fecha_fin ),
    ':proyecto' => ($filtrosModel->proyecto == '' ? null : $filtrosModel->proyecto)
]);
$command->execute();
$totalCount = Yii::$app->db->createCommand("SELECT @total")->queryScalar();

// 初始化分页组件
$pagination = new Pagination([
    'totalCount' => $totalCount,
    'pageSize' => 10,
]);

// 创建SqlDataProvider，关闭自动分页
$dataProvider = new SqlDataProvider([
    'sql' => "CALL ErroresEnHoras(:project_id, :fecha_iniciop, :fecha_finp, :proyecto, :pageSize, :offset)",
    'params' => [
        ':project_id' => ($this->proyectoid == '' || is_null($this->proyectoid)) ? 0 : $this->proyectoid,
        ':fecha_iniciop' => ($filtrosModel->fecha_inicio == '' ? null : $filtrosModel->fecha_inicio ),
        ':fecha_finp' => ($filtrosModel->fecha_fin == '' ? null : $filtrosModel->fecha_fin ),
        ':proyecto' => ($filtrosModel->proyecto == '' ? null : $filtrosModel->proyecto),
        ':pageSize' => $pagination->pageSize,
        ':offset' => $pagination->offset
    ],
    'totalCount' => $totalCount,
    'pagination' => false, // 禁止自动添加LIMIT
]);

方案2：用ArrayDataProvider手动分页（适合小数据量） #

如果你的数据量不大（比如你之前写的335条），可以先一次性获取所有结果，再用ArrayDataProvider手动分页。这种方法不需要修改存储过程，但大数据量下会影响性能：

// 执行存储过程获取所有结果
$command = Yii::$app->db->createCommand("CALL ErroresEnHoras(:project_id, :fecha_iniciop, :fecha_finp, :proyecto)");
$command->bindValues([
    ':project_id' => ($this->proyectoid == '' || is_null($this->proyectoid)) ? 0 : $this->proyectoid,
    ':fecha_iniciop' => ($filtrosModel->fecha_inicio == '' ? null : $filtrosModel->fecha_inicio ),
    ':fecha_finp' => ($filtrosModel->fecha_fin == '' ? null : $filtrosModel->fecha_fin ),
    ':proyecto' => ($filtrosModel->proyecto == '' ? null : $filtrosModel->proyecto)
]);
$allModels = $command->queryAll();

// 初始化分页
$pagination = new Pagination([
    'totalCount' => count($allModels),
    'pageSize' => 10,
]);

// 手动截取当前页的数据
$currentPageModels = array_slice($allModels, $pagination->offset, $pagination->pageSize);

// 创建ArrayDataProvider
$dataProvider = new ArrayDataProvider([
    'models' => $currentPageModels,
    'pagination' => $pagination,
]);

方案3：自定义DataProvider（进阶） #

如果你不想修改存储过程，也不想一次性获取所有数据，可以自定义一个继承自SqlDataProvider的类，重写prepareModels和prepareTotalCount方法，避免自动拼接LIMIT，而是把分页参数传递给存储过程。不过这个方案相对复杂，适合有一定Yii框架基础的开发者。

内容的提问来源于stack exchange，提问作者Nicolas Morales Galindo