How to Use Pagination with Laravel Db::Select Query

How to use pagination with laravel DB::select query

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

How to use pagination on DB::select(query) - Laravel

Currently, pagination operations that use a groupBy statement cannot be executed efficiently by Laravel. If you need to use a groupBy with a paginated result set, it is recommended that you query the database and create a paginator manually.

Check documentation

Laravel Paginate usage with DB::raw query

You may use ->paginate() method or if you want to create a pagination manually, you would do something like:

$perPage = $request->input("per_page", 10);
$page = $request->input("page", 1);
$skip = $page * $perPage;
if($take < 1) { $take = 1; }
if($skip < 0) { $skip = 0; }

$basicQuery = DB::select(DB::raw(" select a.user_id, a.user_email, a.user_account_status, a.created_at, b.s_account_limit AS account_limit, c.consumed_limit, ((b.s_account_limit*1024) - c.consumed_limit) AS remaining_limit FROM upload_limits as b, users AS a JOIN user_upload_limits as c WHERE (a.user_id=c.user_id) AND a.user_type='Simple'"));
$totalCount = $basicQuery->count();
$results = $basicQuery
    ->take($perPage)
    ->skip($skip)
    ->get();

$paginator = new \Illuminate\Pagination\LengthAwarePaginator($results, $totalCount, $take, $page);

return $paginator;

How can i paginate this raw query?

For pure raw query, you may use this way.

$perPage = $request->input("per_page", 10);
$page = $request->input("page", 1);
$skip = $page * $perPage;
if($take < 1) { $take = 1; }
if($skip < 0) { $skip = 0; }

$que = DB::select(DB::raw("SELECT * FROM tbl_ourpeople INNER JOIN tbl_ourpeople_category ON
tbl_ourpeople.category = tbl_ourpeople_category.categoryId WHERE tbl_ourpeople.id>1"));

$totalCount = $que->count();
$results = $que
    ->take($perPage)
    ->skip($skip)
    ->get();

$paginator = new \Illuminate\Pagination\LengthAwarePaginator($results, $totalCount, $take, $page);

return $paginator;

Laravel Select Query and Pagination

You can try like that:

$res = DB::table('messages')
    ->join('users', 'users.id', '=', 'messages.creator')
    ->join('threads', 'threads.id', '=', 'messages.thread_id')
    ->where('threads.id', $id)
    ->orderBy('messages.on_thread_id')
    ->get();

How to use pagination() in laravel by a direct query

Select by raw query will return results as array and lost access to paginate(). There are two solutions to utilize paginate().

(1) Use query builder

You need to build entire query using query builder to have an access to paginate() function. Go study how to convert your SQL to query builder style. It's worth as the query will be independent from database engines.

(2) Create your own paginate()

Here is the source of paginate() from class Illuminate/Database/Query/Builder (Laravel 5.0)

/**
 * Paginate the given query into a simple paginator.
 *
 * @param  int  $perPage
 * @param  array  $columns
 * @return \Illuminate\Contracts\Pagination\LengthAwarePaginator
 */
public function paginate($perPage = 15, $columns = ['*'])
{
    $page = Paginator::resolveCurrentPage();

    $total = $this->getCountForPagination($columns);

    $results = $this->forPage($page, $perPage)->get($columns);

    return new LengthAwarePaginator($results, $total, $perPage, $page, [
        'path' => Paginator::resolveCurrentPath(),
    ]);
}

So you may create your own paginate() like this.

use Illuminate\Pagination\Paginator;
use Illuminate\Pagination\LengthAwarePaginator;

class YourClass {

    public function paginateArray($data, $perPage = 15)
    {
        $page = Paginator::resolveCurrentPage();
        $total = count($data);
        $results = array_slice($data, ($page - 1) * $perPage, $perPage);

        return new LengthAwarePaginator($results, $total, $perPage, $page, [
            'path' => Paginator::resolveCurrentPath(),
        ]);
    }
}

And using is easy.

$blog = $this->paginateArray(
    DB::select( ... )
);

---

Related Topics