如何在 Laravel DB::select 查询中使用分页功能

Question

如何在 Laravel DB::select 查询中使用分页功能

12

我正在使用Laravel框架进行项目开发，并使用DB外观运行原始的SQL查询。我使用了DB::select方法，但是在使用该DB原始查询时，分页方法不起作用并显示以下错误

Call to a member function paginate() on array

我想知道如何使用DB原始查询实现Laravel分页功能，这是我的代码：

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

- Umair Gul

你已经在使用 ->paginate(20)，为什么还要再使用 new Paginator？对于任何阅读此内容的人，请始终尝试理解您正在编写的内容，字面上阅读您所写的内容，它具有“语义”，在分页分页器中没有逻辑...并尽可能避免使用原始查询，始终使用模型，并且仅在您有一个非常复杂的查询并且Eloquent无法解决它或比纯SQL更难阅读时才使用 DB。 - matiaslauriti

7个回答

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

- Tymur Abdullaiev

2

它救了我的一天。内置paginate()函数的很好的定制化。非常感谢你，兄弟。 - Kamran Jabbar

1

非常感谢，又节省了一天的时间。我的查询涉及一些计算，无法按照正常方式工作。 - Mohammed Omer

8

这个分页不是在MySQL层面上进行，而是在PHP层面上进行的，对吗？你正在从MySQL检索所有行，然后在PHP层面上进行切片吗？如果我错了，请纠正我。@Tymur Abdullaiev - Irfan Raza

8

这个功能可以实现但效率不高，如果你需要选择100万行数据呢？你应该在MySQL中实现而不是在PHP中实现。 - Emeka Mbah

1

对于任何阅读此内容的人，请不要使用此答案，因为它不够高效。正如第三条评论所说：此查询未被限制在每个“页面”，而是提取了DB上的所有数据，然后PHP对其进行切片，完全疯狂。您可能有100,000条记录，DB将提取全部数据，而PHP将耗尽内存...此外，您正在复制Laravel已经通过简单的->paginate(10)查询执行的操作...没必要重新发明轮子... - matiaslauriti

显示剩余4条评论

6

不要在 PHP 端使用分页逻辑！在 SQL 中使用 limit 和 offset，将其余部分留给数据库服务器处理。另外，在语句中使用单独的 count-select。

计数：

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

结果：

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

- Heinz

6

这对我来说很合适。

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

- sumatoreo

5

这对我起作用了，请参见在您的控制器中首次使用

use Illuminate\Pagination\Paginator;

然后在函数中

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

输出 =>

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

- hariom nagar

1

经过尝试了很多方法之后，我找到了解决方案并且它对我有效，希望对其他人也有所帮助。

首先，在一个PHP类中使用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()]
        );
}

- Syed Arsalan Ahmed

这个答案的性能非常差，强烈建议避免使用。这个查询没有被限制在每个“页面”，而是在数据库中获取所有数据，然后用 PHP 进行切片，完全是一种疯狂的做法。如果你有 100k 条记录，数据库将会获取全部数据，而 PHP 将会耗尽内存...此外，你正在复制 Laravel 已经通过简单的 ->paginate(10) 实现的功能...没有必要重新发明轮子... - matiaslauriti

是的，paginate(10)已经足够好了，但我建议在使用DB:raw查询获取数据时使用自定义分页，因为你无法使用“->paginate(10)”函数。所以在这种情况下，你必须使用自定义分页或另一种解决方案是将原始查询转换为Eloquent查询。 - Syed Arsalan Ahmed

尽可能避免使用原始的 SQL，如果必须使用，请不要直接编写整个 SQL，而是使用 select、table、join 等方法。 - matiaslauriti

0

这对我来说很有效...而且对性能也有好处：

查询所有结果的数量。
带有偏移量和限制条件的主查询。

根据页码为当前项目创建分页。

$sql_count = "select count(*) as aggregate from `products`";
$data_count = DB::select($sql_count);
$count = $data_count[0]->aggregate;  
$per_page =10; //定义每页显示的数量
$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);
   }

- Ahmed Adel

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- MohamedSabil83 · Accepted Answer

尝试：

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