13

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);
 }
}
Improve this question
1
  • 1
    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. Commented Jul 22, 2021 at 4:03

7 Answers 7

Reset to default
30

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Your code also provide help in how to implements joins in laravel... thanks again
how to hydrate the above result so as to relationships can be accessed for notices.
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()]);
}
Improve this answer

9 Comments

It's saved my day. great customization of built-in paginate() function. Hight Appreciated brother.
thank you a lot another day here has been saved , my query had some calculations and won't work the normal way
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
This is works but not efficient, what if you are selecting 1Million rows? You should do this in Mysql not PHP
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...
|
7

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;
Improve this answer

Comments

7

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
                       );
Improve this answer

Comments

6

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: []
Improve this answer

Comments

1

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

3 Comments

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...
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.
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...
0

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);
   }
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.