How to use pagination with laravel DB::select query

Question

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);
 }
}

You are already doing `->paginate(20)`, why are you using `new Paginator` again ? For anyone reading this, always try to understand what you are writing, literally read what you wrote, it has "semantic", there is no logic in paginating a paginator... And try to avoid raw queries as much as possible, ALWAYS use Models, and only use `DB` in when you have a really complex query and Eloquent will not solve it or would be harder to read than plain `SQL`. — matiaslauriti, Jul 22 '21 at 04:03

score 29 · Accepted Answer · answered May 20 '17 at 20:52

29

Try:

$notices = DB::table('notices')
        ->join('users', 'notices.user_id', '=', 'users.id')
        ->join('departments', 'users.dpt_id', '=', 'departments.id')
        ->select('notices.id', 'notices.title', 'notices.body', 'notices.created_at', 'notices.updated_at', 'users.name', 'departments.department_name')
        ->paginate(20);

answered May 20 '17 at 20:52

MohamedSabil83

1,529
7
12

Your code also provide help in how to implements joins in laravel... thanks again – Umair Gul May 20 '17 at 22:12
how to hydrate the above result so as to relationships can be accessed for notices. – Sonal Jul 24 '19 at 08:26

score 28 · Answer 2 · answered May 20 '17 at 20:31

28

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()]);
}

answered May 20 '17 at 20:31

Tymur Abdullaiev

471
4
6

2

It's saved my day. great customization of built-in paginate() function. Hight Appreciated brother. – Kamran Jabbar Dec 28 '17 at 17:36
1

thank you a lot another day here has been saved , my query had some calculations and won't work the normal way – Mohammed Omer Jan 15 '18 at 14:00
8

Isn't this pagination is at php level rather than at mysql level? You are fetching all rows from mysql and then slicing at php level? Please correct me if I am wrong. @Tymur Abdullaiev – Irfan Raza May 18 '18 at 10:59
Thanks! The `array_slice` function needs to have preserve keys set to **false**. Reason being, the data structure on the first page because of the **true** flag in preserving the array keys changes for subsequent pages. Setting it to **false** maintains consistent data structure from the paginator class for all pages. – Dele Sep 03 '18 at 05:39
8

This is works but not efficient, what if you are selecting 1Million rows? You should do this in Mysql not PHP – Emeka Mbah Nov 01 '18 at 04:33
not the best solution, but it saved the day for now. Thanks :) – Asad ullah Jan 29 '21 at 22:38
@EmekaMbah, You'll have more complex problems to sort than pagination if your table got 1Million rows. – Udy Warnasuriya Feb 09 '21 at 14:12
For anyone reading this, DO NOT USE THIS ANSWER AS IT IS NOT PERFORMANT. As the third comment says: This query is not being limited to each "page", but it is rather fetching all data on DB and then PHP is slicing it, total madness. You could have 100k records and DB is going to fetch all and PHP is going to run out of memory... Also, you are replicating what Laravel already does with a simple `->paginate(10)` after the query... No need to reinvent the wheel... – matiaslauriti Jul 22 '21 at 04:07
You nailed it buddy – MUHAMMAD MESUM Mar 27 '22 at 18:48

score 6 · Answer 3 · answered Jun 20 '18 at 12:17

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;

score 6 · Answer 4 · answered May 11 '19 at 14:51

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
                       );

score 5 · Answer 5 · edited Aug 06 '21 at 07:43

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: []

score 0 · Answer 6 · edited Jul 22 '21 at 04:01

0

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()]
        );
}

edited Jul 22 '21 at 04:01

matiaslauriti

7,065
4
31
43

answered Jun 23 '21 at 11:58

Syed Arsalan Ahmed

115
6

This answer is not performant at all, avoid this 100%. This query is not being limited to each "page", but it is rather fetching all data on DB and then PHP is slicing it, total madness. You could have 100k records and DB is going to fetch all and PHP is going to run out of memory... Also, you are replicating what Laravel already does with a simple ->paginate(10) after the query... No need to reinvent the wheel... – matiaslauriti Jul 22 '21 at 04:10
Yes, the paginate(10) is good enough but I suggested this to use custom pagination when you are fetching data by using DB:raw query then you can't be able to use the "->paginte(10)" function. So in that case you have to use custom paginate or another solution is that you can convert your raw query to an eloquent query. – Syed Arsalan Ahmed Jul 22 '21 at 09:34
Avoid raw sql as much as possible, and if you need to use it, do not write and entire sql with it, use rhe methods select, table, join, etc... – matiaslauriti Jul 22 '21 at 18:20

score 0 · Answer 7 · answered Jun 14 '22 at 02:59

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

query for count all results.
query for main query with offset and limit.

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);
   }

How to use pagination with laravel DB::select query

7 Answers7

Linked