Level 75
Calculate the offset and the number of rows per page and pass it to the stored procedure. You may also need such things as the max page in your logic but that's up to you.
Jan 9, 2017
6
Level 6
pagination for query builder and stored procedure
i can't seem to figure out how to get pagination to work on query builder with stored procedure. it returns an array and not a collection so i can't use ->paginate(). i found this post but i don't know how to incorporate it to my code http://itsolutionstuff.com/post/mysql-procedure-with-pagination-in-laravel-5example.html.
here's what i have.
controller
public function index()
{
$data['results'] = DB::select('SET NOCOUNT ON; EXEC stored_procedure');
return view('reports.index', $data);
}
view
@foreach ($results as $result)
{{ blah blah blah }}
@endforeach
the code above returns a result and i can loop through them as usual. i just need to paginate them.
Level 6
@jlrdw ok i'll try that. i guess there's no way to paginate an array? i was hoping for a "faster and simpler" solution.
Level 4
@w1n78 Sure you can, try this:
public function index()
{
$page = request('page', 1);
$pageSize = 10;
$results = DB::select('SET NOCOUNT ON; EXEC stored_procedure');
$offset = ($page * $pageSize) - $pageSize;
$data = array_slice($results, $offset, $pageSize, true);
$paginator = new \Illuminate\Pagination\LengthAwarePaginator($data, count($data), $pageSize, $page);
return view('reports.index', ['results' => $paginator]);
}
1 like
Level 6
@koerel thanks very much. that did the trick. i had to add the following since my method was different than index().
$paginator->setPath(request('url'));
otherwise the url wasn't working right for some reason. but thank you for the help.
Level 1
Hey, thanks for the answer, it helped me a lot. Just one more thing if somebody looking at this, on the "LengthAwarePaginator" line, the 2nd parameter should be count($result), you should supply the length of the entire result array instead of just sliced data
Level 1
This code works good but I don't know how to show the link to each page.
Please or to participate in this conversation.