How to use pagination with laravel DB::select query
When working with Laravel, the Eloquent ORM provides a robust set of tools that make retrieving paginated data straightforward. However, situations arise where you need to execute raw SQL queries using the DB facade, perhaps to optimize performance or retrieve complex aggregated data. The common issue developers encounter is attempting to apply the paginate() method directly to the result of DB::select().
The error message Call to a member function paginate() on array occurs because DB::select() returns an array of raw database rows, not an Eloquent Collection or Model instance. In Laravel, the pagination machinery relies on specific methods available on collection objects, such as count(), total(), and lastPage(). Standard arrays do not possess these methods.
To resolve this, you must manually construct a LengthAwarePaginator instance. This class allows you to take a raw array of data and wrap it in an object that behaves like a paginated collection. Here is the corrected approach for your controller code:
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Support\Facades\DB;
public function index()
{
// Execute the raw query
$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');
// Get the total count for the paginator to calculate metadata
$total = collect($notices)->count();
// Create the LengthAwarePaginator instance
$paginator = new LengthAwarePaginator(
$notices, // The data (must be an array)
$total, // Total number of records
20, // Items per page
request('page', 1), // Current page
[
'path' => request()->url(), // Preserves the query string path
'query' => request()->query() // Preserves existing query parameters
]
);
return view('welcome')->with('allNotices', $paginator);
}
A critical step often overlooked when working with raw queries is ensuring that the SQL statement includes the necessary LIMIT and OFFSET clauses. When using a manual paginator, you must handle the offset calculation yourself if your query does not include it.
If you omit pagination logic in the SQL string, the database will return all records, which can lead to memory exhaustion on large datasets. You should modify your raw query to dynamically append pagination constraints based on the current page and items per page:
$page = request('page', 1);
$perPage = 20;
$offset = ($page - 1) * $perPage;
$sql = "SELECT notices.id, notices.title, notices.body
FROM notices ORDER BY notices.created_at DESC LIMIT {$perPage} OFFSET {$offset}";
$notices = DB::select($sql);
However, relying solely on manual offset calculation in the SQL query means you lose the ability to easily calculate the total number of pages without running a separate COUNT(*) query. This is where LengthAwarePaginator shines: it expects the full count of records for accurate metadata generation. If your query filters results dynamically (e.g., based on search terms), calculating the total count via COUNT(*) can become expensive if not optimized with proper indexing.
Another consideration involves data types. When mapping raw SQL columns to views, ensure that your view expects an array of objects or associative arrays, which DB::select() provides by default. If you were using DB::table('table')->get(), you would get an Eloquent collection, but with select and multiple tables joined without an alias map, the results are plain arrays.
Finally, if your raw query involves complex joins or aggregations where standard pagination feels cumbersome, consider whether a view-based approach or a dedicated repository class might offer cleaner separation of concerns. For deeper dives into Laravel's database capabilities, you can explore resources like LaravelCompany to find community-driven examples that address specific edge cases in raw query handling.