LARAVEL 5 使用 DB-RAW SQL搭配 PAGINATION 自動分頁功能

參考網頁
LARAVEL 5 使用 DB-RAW SQL搭配 PAGINATION 自動分頁功能

原始的簡單作法

要取得分頁的資料，可以使用 query builder。

$data = DB::table('projects')
    ->groupBy('id')
    ->orderBy('id', 'asc')
    ->paginate(15); 

但在線上資料有如下的提示
Note: Currently, pagination operations that use a groupBy statement cannot be executed efficiently by Laravel. If you need to use a groupBy with a paginated result set, it is recommended that you query the database and create a paginator manually.

上面的查詢，可改成直接用 SQL 指令的查詢如下。

$sql = "SELECT * FROM projects
        GROUP BY id
        ORDER BY id ASC";

$query = DB::select($sql); 

依照網頁的說明，原始的 SQL 指令，可以自行使用分頁函式來建立分頁資料

Sometimes you may wish to create a pagination instance manually, passing it an array of items. You may do so by creating either an Illuminate\Pagination\Paginator  or   Illuminate\Pagination\LengthAwarePaginator  instance, depending on your needs.

自己建立分頁資料的作法如下

use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Pagination\Paginator; 

$query = DB::select($sql);
$page = Paginator::resolveCurrentPage("page");
$perPage = $count; // 實際每頁筆數
$offset = ($page * $perPage) - $perPage;

$data = new LengthAwarePaginator(
    array_slice($query, $offset, $perPage, true), 
    count($query), 
    $perPage, 
    $page, 
    ['path' =>  Paginator::resolveCurrentPath()]
); 

其中 DB::select() 函式會回傳整個查詢結果。

$query = DB::select( DB::raw($sql) ); 

然後，再用 array_slice() 擷取對應該 page 的資料列。

array_slice($query, $offset, $perPage, true)




$data 就是整個查詢出來的data。

使用 MSSQL 的 cursor 的作法

上述作法，因為 DB::select() 會傳回所有的資料，會使得效率較差。例如 64,679 筆資料，要花 1.7秒左右，假如使用 SQL 2008 的 cursor，只取回所需的 10筆，大約只要 0.2秒左右。

另外，上述程式中取得 page 和 url 的作法，在只使用 Laravel 部分 component，而不是使用 Laravel framework 的情況時，無法無法正常運作，因此改用 Request 的作法。

$pdo = DB::connection()->getPdo();

$stmt = $pdo->prepare($sql, [
    PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL,
    PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_STATIC
]);

$stmt->execute($param);

// Get the total number of rows returned by the query.
$totalCount = $stmt->rowCount();
$perPage = $count; // 實際每頁筆數
$page = $page = trim(Request::input('page', '1'));
$offset = ($page * $perPage) - $perPage;
$rows = array();

$i = 0;
while($row = $stmt->fetch(
    PDO::FETCH_ASSOC,
    PDO::FETCH_ORI_ABS,
    $offset + $i
)) {
    // dump($row);
    $rows[] = ($row);
    $i++;

    if ($i >= $pPageRows) {
        break;
    }
}

$stmt = null;

$data = new LengthAwarePaginator($rows, $totalCount, $perPage, $page,
    ['path' => Request::url()]);