CODEWITHSUNDEEP
laravel
Aleksej_Shherbak
Aleksej_Shherbak

Reputation: 3058

How I can paginate DB::select(...) result and get links() method?

I have a big and difficult SQL query. It works fine for me. And I have the following code in the controller: 

 public function index(OpenRegDepDataReportInterface $openRegDepDataReport, Request $request): Renderable
    {
        $applications = $openRegDepDataReport->getReport($advertisers, $category);

        return view('applications.index', compact('applications'));
    }

So, the method getReport gives me a result of DB::select('<here is my big diffecult SQL>'), and, as well known it's an array.

But as you can see I'm trying to pass the result on a view. And, of course, I would like to call $applications->links() in the view, like for eloquent collection. Which is proper and faster way to do that?

Upvotes: 0

Views: 573

Answers (3)

Sergey Andrianov
Sergey Andrianov

Reputation: 518

doc

To display pagination at the table, you must call the select and then the pagination method.

in Controller: 

$test = DB::table('users')->select('id')->paginate(10);

in View:

$test->links();

Upvotes: 1

pimarc
pimarc

Reputation: 4145

Simple answer, use paginate() method: 

$basicQuery = DB::select(DB::raw("<here is the big diffcult SQL query>"));

However, paginate() works only on collections, and since you have an array of objects, you need to turn it into a collection first, using the forPage() method:

The forPage method returns a new collection containing the items that would be present on a given page number. The method accepts the page number as its first argument and the number of items to show per page as its second argument: 

$collection = collect($basicQuery);
$chunk = $collection->forPage(2, 3);
$chunk->all();

Complicated answer: build a paginator instance yourself:

$perPage = 10;
$page = $request->input("page", 1);
$skip = $page * $perPage;
if($take < 1) { $take = 1; }
if($skip < 0) { $skip = 0; }

$basicQuery = DB::select(DB::raw("<here is the big diffcult SQL query>"));

$totalCount = $basicQuery->count();
$results = $basicQuery
    ->take($perPage)
    ->skip($skip)
    ->get();

$paginator = new \Illuminate\Pagination\LengthAwarePaginator($results, $totalCount, $take, $page);

return $paginator;

I would recommend using the paginate() method.

You can read more about Laravel's pagination.

Upvotes: 0

nakov
nakov

Reputation: 14268

So based on the documentation if your $applications returns a Query Builder result, then just append ->paginate(10); to it.

https://laravel.com/docs/master/pagination#paginating-query-builder-results

$applications = $openRegDepDataReport->getReport($advertisers, $category)->paginate(10);

Upvotes: 0

Related Questions