Codeigniter 4 Pagination With Join and Where sql

I believe limit and offset cannot solve the problem since offset creates a starting point until it reaches limit, am I right? says: "return only 10 records, start on record 16 (OFFSET 15)" on w3school

The explanation of limit and offset is no problem.
But I don't know why you believe limit and offset cannot solve the problem.

Using limit and offset is a common way to create pagination.

Because it returned me the items only between offset and limit and nothing else. For example, if it was set for 7,15 It starts from 16 + 7 items and same items for each pagination 1-2-3-4.. No different result. And if I fetch data from my Model, your codes below have no effect:

PHP Code:

$page    = (int) ($this->request->getGet('page') ?? 1);
$perPage = 2;
$total  = 10;

// Call makeLinks() to make pagination links.
$pager_links = $pager->makeLinks($page, $perPage, $total);
$data['pager_links'] = $pager_links; 


If I don't fetch data from Model but do it in my controller , like in the example below, then it works. But not in a healthy way for me because it is not my exact sql with joins .

PHP Code:

  $model = new \App\Models\AdminModels\OffersModel();
  $data['offers_pending'] = $model->where('offer_status', 'pending')->paginate(2);