CODEWITHSUNDEEP
phplaravelsearchlimitoffset
syed1234
syed1234

Reputation: 835

How to use offset & limit in laravel

I am facing issue to fetch records using offset and limit.

Currently I have 73 records in my database

My body request look like below.

{

"email": "",
"name": "",
"role_id":"",
"pageNumber": 8,
"pageSize": 10
 }

Here pageNumber 8 means 7 page because it starts from 0 and page size is 10 means want to show 10 records in page and expected output should be 3 records because there is 3 records between 70 to 80 as my total records is 73 and it should display 3 records.

But its displaying 10 records:

My Code:

                $pram = $request->name;
    $email= $request->email;
    $roleId= $request->role_id;
    $pageNumber= $request->pageNumber;
    $pageSize= $request->pageSize;

    $UserResultQuery = User::with('userBasicInfo','userFacility','roles');

    if (!empty($pram)) {
        $UserResultQuery->where(function ($query) use ($pram) {
            $query->whereHas('userBasicInfo', function ($query1) use ($pram) {
                $query1->where('first_name', 'like', '%' . $pram . '%');
                $query1->orWhere('middle_name', 'like', '%' . $pram . '%');
                $query1->orWhere('last_name', 'like', '%' . $pram . '%');

            })->with(['userBasicInfo' => function ($query1) use ($pram) {
                $query1->where('first_name', 'like', '%' . $pram . '%');
                   $query1->orWhere('middle_name', 'like', '%' . $pram . '%');
                   $query1->orWhere('last_name', 'like', '%' . $pram . '%');
            }]);
        });
    }
    if (!empty($roleId)) {
        $UserResultQuery->where(function ($query) use ($roleId) {
            $query->whereHas('userFacility', function ($query1) use ($roleId) {
                $query1->where('role_id', $roleId);
            })->with(['userFacility' => function ($query1) use ($roleId) {
                $query1->where('role_id', $roleId);
            }]);
        });
    }
    if (!empty($email)) {
        $UserResultQuery->where('email', 'like', '%' . $email . '%');
    }

    $results=$UserResultQuery->offset($pageNumber)->limit($pageSize)->get();
    $results=$results->toArray();       // here using offset and limit
        $totalRecords=count($allUsers);

     return response(['status' => true, 'data' => $user_array,'totalRecords' => $totalRecords]);

Upvotes: 0

Views: 745

Answers (1)

Puckwang
Puckwang

Reputation: 434

Pass offset row amount to offset() not page number.

$offset = ($pageNumber - 1) * $pageSize;
$results=$UserResultQuery->offset($offset)->limit($pageSize)->get();

You can also use this forPage(int $page, int $perPage = 15) doc.

$results=$UserResultQuery->forPage($pageNumber, $pageSize)->get();

Upvotes: 1

Related Questions