Laravel Pagination when using a MySQL Stored Procedure

Question

This might be an old question but it seems I can't get around this problem. I have a Laravel 5.0 API which calls a procedure that retrieves data from multiple tables. The retrieved is around 50-80 rows and I would like to have a pagination on the results of the service.

This is the code:

$infoList = DB::connection('mysql')
    ->select(DB::raw('CALL dbname.GetAllUserInfo()'));

Is there a way to paginate the results of this? Even if I convert the array to a class and use ->paginate(15) it gives me an error that the Call to undefined method stdClass::paginate(). I've tried using foreach to create the object but still cannot use pagination. Any suggestions, please? I'm a beginner in Laravel.

Answer 1

if you are using laravel.5 then you have to do this way pagination.

$infoList = DB::connection('mysql')
      ->select(DB::raw('CALL dbname.GetAllUserInfo()'));

$page = Input::get('page', 1);
$paginate = 10;

$offSet = ($page * $paginate) - $paginate;
$itemsForCurrentPage = array_slice($data, $offSet, $paginate, true);
$data = new \Illuminate\Pagination\LengthAwarePaginator($itemsForCurrentPage, count($data), $paginate, $page);

return view('test',compact('data'));

Answer 2

You can't do it the way you want. Because the $infoList->paginate(..) method is build in the Eloquent Model/Builder and you have the Illuminate\Database\Query\Builder instance.

Checkout the facade from DB.

There are multiple solutions for this. You can put the $infoList array within a collection and use this slice method.

$infoList = DB::connection('mysql')
    ->select(DB::raw('CALL dbname.GetAllUserInfo()'));

$list = collect($infoList); // or new Collection($list);
// paginate/slice the array into the set you want
// first parameter: offset, second parameter the amount/limit
$pageList = $list->slice($request->get('offset', 0), 30);

Another solution would be creating a model and don't use stored procedures (but not your required solution i guess).

class User extends Illuminate\Database\Eloquent\Model
{
    // model implementation
}

$infoList = User::paginate(30); // parameter: limit/size

And another solution is too use the store procedure and do pagination in your stored procedure call:

// stored procedure
DELIMITER //
CREATE PROCEDURE GetAllUserInfo (
    IN _limit smallint unsigned, 
    IN _offset smallint unsigned
)
BEGIN
    SELECT Name, HeadOfState FROM Country
    WHERE Continent = con
    LIMIT _offset, _limit;
END //
DELIMITER ;

// execute the stored procedure
$infoList = DB::connection('mysql')
    ->select(DB::raw('CALL dbname.GetAllUserInfo('. $request->get('offset', 0) .', 30)'));

Note: its been a while I wrote a stored procedure so Im out of shape writing it. And I wrote it whats on top of my head atm.

I hope one of these solutions works out for you.

Answer 3

I I have a similar answer in this url : https://www.onlineinterviewquestions.com/blog/laravel-pagination-with-array-or-object
I just change in the view (for Laravel 5.1): this:
{{ $products->links() }}
for this:
{!! str_replace('/?', '?', $storecreditlist->appends([])->render()) !!}
I hope this solutions works out for you.