How to use pagination with laravel DB::select query

user_7735177 0
Viewed 48299

I am working on a project in Laravel and using DB facade to run raw queries of sql. In my case I am using DB::select, problem is that pagination method is not working with this DB raw query and showing this error

Call to a member function paginate() on array

I just want how to implement laravel pagination with DB raw queries here is my code:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Notice;
use Illuminate\Support\Facades\DB;
use Illuminate\Pagination\Paginator;
use Illuminate\Pagination\LengthAwarePaginator;

class NoticeController extends Controller
{

public function index(){

    $notices = DB::select('select 
notices.id,notices.title,notices.body,notices.created_at,notices.updated_at,
    users.name,departments.department_name
    FROM notices
    INNER JOIN users ON notices.user_id = users.id
    INNER JOIN departments on users.dpt_id = departments.id
    ORDER BY users.id DESC')->paginate(20);

    $result = new Paginator($notices,2,1,[]);

    return view('welcome')->with('allNotices', $notices);
 }
}
laravel pagination mysql php
7 Answers

This is suitable for me

$sql = "some sql code";

$page = 1;
$size = 10;
$data = DB::select($sql);
$collect = collect($data);

$paginationData = new LengthAwarePaginator(
                         $collect->forPage($page, $size),
                         $collect->count(), 
                         $size, 
                         $page
                       );
user_42257480

Don't ever use the pagination logic on php-side! Use limit and offset on your sql's and leave the rest to the database server. Additional use a seperate count-select for your statement.

Count:

$sql_count = 'SELECT count(1) cnt FROM ('. $sql . ') x';
$result = \DB::select( DB::raw($sql_count) );
$data['count'] = $result[0]->cnt;

Results:

$sql .= ' LIMIT ' . $offset . ', ' . $limit; 

$result = \DB::select( DB::raw($sql) );
$myPaginator = new \Illuminate\Pagination\LengthAwarePaginator($result, $data['count'], $limit, $page, ['path' => action('MyController@index')]);
$data['result'] = $result;
user_99673770

It work for me, see First use in your controller

use Illuminate\Pagination\Paginator;

then in function

$query =  DB::select(DB::raw("SELECT pro.* , (SELECT TIMESTAMPDIFF(DAY,updated_at,'$current_date') from users as u where u.id=pro.id) as days FROM users as pro where role_id = 6 and delete_status=0 and user_status = 'A' and approved_status = 1 and is_clever_courier = 1 having days >= 5"));

     
$page1 = new Paginator($query, $maxPage);

dd($page1);

o/p =>

Paginator {#1450 ▼
  #hasMore: true
  #items: Collection {#1509 ▼
    #items: array:10 [▼
      0 => {#1454 ▶}
      1 => {#1455 ▶}
      2 => {#1456 ▶}
      3 => {#1457 ▶}
      4 => {#1458 ▶}
      5 => {#1459 ▶}
      6 => {#1460 ▶}
      7 => {#1461 ▶}
      8 => {#1462 ▶}
      9 => {#1463 ▶}
    ]
  }
  #perPage: 10
  #currentPage: 1
  #path: "/"
  #query: []
  #fragment: null
  #pageName: "page"
  +onEachSide: 3
  #options: []
user_133681040

After trying so many things I found the solution and it works fine for me, maybe it will be helpful for someone else.

First, in a PHP class use Illuminate\Pagination\LengthAwarePaginator:

use Illuminate\Pagination\LengthAwarePaginator;

public function index(Request $request) {
    $notices = DB::select('SELECT notices.id, notices.title, notices.body, notices.created_at, notices.updated_at, users.name,departments.department_name
    FROM notices
    INNER JOIN users ON notices.user_id = users.id
    INNER JOIN departments on users.dpt_id = departments.id
    ORDER BY users.id DESC');
    
    $notices = $this->arrayPaginator($notices, $request);
    
    return view('welcome')->with('allNotices', $notices);
}
    
public function arrayPaginator($array, $request) {
        $page = Input::get('page', 1);
        $perPage = 10;
        $offset = ($page * $perPage) - $perPage;

        return new LengthAwarePaginator(
            array_slice(
                $array,
                $offset,
                $perPage,
                true
            ),
            count($array),
            $perPage,
            $page,
            ['path' => $request->url(), 'query' => $request->query()]
        );
}
user_96318110

this work for me ... and good for performance :

  1. query for count all results.

  2. query for main query with offset and limit.

  3. make paginate for current items based on page number.

    $sql_count = "select count(*) as aggregate from `products`";
$data_count = DB::select($sql_count);
$count = $data_count[0]->aggregate;  
$per_page =10; //define how many items for a page
$pages = ceil($count/$per_page);
$page = ($request->page=="") ?"1" :$request->page;
$start    = ($page - 1) * $per_page;  

    $sql = "select * from `products`";
    $sql.= ' LIMIT ' . $start . ', ' . $per_page;
    $page = 1;
    $size = 10;
    $data = DB::select($sql);
    
   $data = $this->paginate($data , $count , $per_page , $request->page);

   public function paginate($items, $total , $perPage = 5, $page = null, $options = [])
   {
    $page = $page ?: (Paginator::resolveCurrentPage() ?: 1);
    $items = $items instanceof Collection ? $items : Collection::make($items);
    return new LengthAwarePaginator($items,  $total, $perPage, $page, $options);
   }
user_80008050
Related