How to Limit and Offset in Pagination Laravel Eloquent

Today article we will show you how to usage method limit and offset function. When should you using limit function and offset? In this case we will give you simple example how to use limit and offset in Laravel query builder, so that you will learn how to use offset in eloquent.

In generally, SQL statement limit and offset found in query to create pagination. The same as method in Laravel limit() method will use to get specific data from a table and offset() method will use for skip amount of data in specific number. We can use either limit() or offset() to create pagination as below example.

The example below, you can apply with Laravel 6, Laravel 7, Laravel 8 and Laravel 9 versions without any error. Let's see example below.

SQL STATEMENT

This is what generally we see in SQL statement.

SELECT [column_1],...,[column_n]
FROM [table_name]
ORDER BY [column_name] DESC
LIMIT [number_of_rows_to_retrieve]
OFFSET [number_of_rows_to_skip];

Laravel Limit Eloquent

For instance, we want to get top 5 score students, which mean we sorted score order by descending and get first 5 records. The code should implement as below:

$score = StudentMark::select("*")
         ->orderBy('score','DESC')
         ->limit(5)
         ->get();

Laravel Eloquent in Controller

Here how we place the code in Laravel controller file.

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\Models\StudentMark;
  
class StudentMarkController extends Controller
{
    public function index()
    {
        // Get Top 10 highest score
        $st= StudentMark::select("*")
                        ->orderBy('score','DESC')
                        ->offset(0)
                        ->limit(10)
                        ->get();
        dd($st);
    }
}

Laravel Pagination Eloquent

The below example is alternative way to create pagination record which get parameter page from client request.

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\Models\StudentMark;
  
class StudentMarkController extends Controller
{
    private $limit= 10;

    public function index()
    {
        // page number sent by client
        $page = request("page");

        // in case negative page
        if($page < 1) $page = 1;
        $st= StudentMark::select("*")
                        ->offset((page - 1) * $this->limit)
                        ->limit($this->limit)
                        ->get();
        dd($st);
    }
}

Hope this short article help you with your project today. Thanks for reading.

You might also like...

• Laravel Check App Environment Running

• Laravel 9 Install Bootstrap 5 via Vite

• How to Set Timezone in Laravel

• Laravel 9 Resize Thumbnail Image Before Upload

• How to Install Tailwind CSS in Laravel 9 in 3 Ways