Codeigniter 4 Pagination With Join and Where sql

You have defined $total as 20, but how can we know it beforehand?

You can know it by querying the database.

PHP Code:

$total = $model->where('offer_status', 'pending')->countAllResults(); 


Yeah, I know. That's what I tried, you see. We should add such notes to the documentation because there are lots of  beginner level users. (Actually I am working on an official blog project about "how to do x with CodeIgniter". We should clearly write tutorials there. I will ask help here about it soon).

I really wonder if anyone, including you Kenjis, has ever used such data fetching + pagination in real life. So that we will have the real example about it on internet.

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.

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

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


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.

If limit is 7:
Page 1: 1 to 7
Page 2: 8 to 14
Page 3: 15 to 21
Page 4: 22 to 28

You need two things for pagination.
1. pagination links
2. data to display

1. needs total count, per page, current page number.
2. needs data set to display.

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

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.

If limit is 7:
Page 1: 1 to 7
Page 2: 8 to 14
Page 3: 15 to 21
Page 4: 22 to 28

You need two things for pagination.
1. pagination links
2. data to display

1. needs total count, per page, current page number.
2. needs data set to display.

I understand. My problem is with the combination of 1 and 2 as I wrote you before. Your codes work when I do it in controller but then I cannot use my joins for sql.
This works as I told you but how can I use my joins? :

PHP Code:

$pager = service('pager');
$count = $model->where('offer_status', 'pending')->countAllResults();

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

$data['offers_pending'] = $model->where('offer_status', 'pending')->paginate($perPage);

$pager_links = $pager->makeLinks($page, $perPage, $total);
$data['pager_links'] = $pager_links; 


You can't use Model's paginate() method. Because you try to use your own query with JOIN for the pagination.
The last thing you need to do is to set the correct data set to $data['offers_pending'].

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper