php 如何在 Laravel DB::select 查询中使用分页
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44090392/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use pagination with laravel DB::select query
提问by Umair Gul
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
我正在 Laravel 中开发一个项目，并使用 DB Facade 来运行 sql 的原始查询。就我而言，我使用的是 DB::select，问题是分页方法不适用于此 DB 原始查询并显示此错误
Call to a member function paginate() on array
I just want how to implement laravel pagination with DB raw queries here is my code:
我只想要如何使用 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);
}
}
回答by Tymur Abdullaiev
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()]);
}
回答by MohamedSabil83
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);
回答by Heinz
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.
永远不要在 php 端使用分页逻辑！对您的 sql 使用限制和偏移量，并将其余部分留给数据库服务器。另外为您的语句使用单独的计数选择。
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;
回答by runie
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
);
