Codeigniter 4 Pagination With Join and Where sql

Hi there. For the first time with CI4 I need to use pagination but the explanations in our document or the examples I have found on internet lack when it comes to having an sql with joins or where conditions. Here is a simplified example of my Model and Controller. Could you please help how to convert it something that I can use with pagination.

My Model:

PHP Code:

public function get_all_offers_pending(){
    $db      = \Config\Database::connect();
    $builder = $db->table('company_offers as co');
    $query = $builder->select('*')
    ->join('companies as c', 'c.company_id = co.company_id')
    ->join('company_contact_info as cci', 'cci.company_id = co.company_id')
    ->join('sectors as s','s.sector_id  = c.sector')
    ->where('co.offer_status =', 'pending')    
    ->get();

    return $query->getResult();
  } 


My Controller:

PHP Code:

$data['offers_pending'] = $this->AdminLib->OfferModel->get_all_offers_pending(); 


Thanks in advance

See https://codeigniter4.github.io/CodeIgnit...pagination

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

Thanks Kenji for the answer. I posted my question here because I didn't get any help from the documentation actually. When I try, I receive errors such as :



BadMethodCallException

Call to undefined method App\Models\AdminModels\OffersModel::pager

SYSTEMPATH\Model.php at line 730 -

PHP Code:

throw new BadMethodCallException('Call to undefined method ' . static::class . '::' . $name); 


I don't know what you did.
But all you have to do to create pagination links is to call the method with appropriate arguments.

PHP Code:

$pager->makeLinks($page, $perPage, $total) 


It might be good to forget about CI4 Model pagination.

Or if you want to do something like CI4 Model pagination,
read BaseModel::paginate() method, and override it for your need.

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

Unfortunately, the documentation needs real examples of usage of pagination. It's not working. It's not clear.  Adding a pagination should not have been so difficult, I guess.

Is there anyone who can suggest a way to get pagination for my query above?

I will update the sample code in the user guide.

See https://github.com/codeigniter4/CodeIgni...on/015.php

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

It looks much more better. We are making progress. Thanks.

The missing point here is that we should add the retrieving data part from the model and tell the users to keep them seperated.

For instance,

PHP Code:

$data['offers_pending'] = $this->AdminLib->OfferModel->get_all_offers_pending(); 


This is nothing to do with the pagination code of yours below:

PHP Code:

$pager = service('pager');
$count = $model->where('offer_status', 'pending')->countAllResults(); //I have added this

                $page    = (int) ($this->request->getGet('page') ?? 1);
                $perPage = 2;
                $total  = $count; // or $count/$perPage or 10 as you typed

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


But the thing I face here is the $total variable. It is not usable. Because imagine the total is 6 and I have 3 results per page for 10 results. Then after the second or third pagination it keeps showing the same, the last, item for the rest of the paginations.

But the thing I face here is the $total variable. It is not usable. Because imagine the total is 6 and I have 3 results per page for 10 results. Then after the second or third pagination it keeps showing the same, the last, item for the rest of the paginations.

Sorry, I don't get what you say.

$page is the current page number, starting at 1.
$perPage is the number of the records found you show on one page.
$total is the number of total records found.

And you need to modify get_all_offers_pending() so that returns data set with limit and offset.
The limit is $perPage. The offset can be calculated from $page.

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

But the thing I face here is the $total variable. It is not usable. Because imagine the total is 6 and I have 3 results per page for 10 results. Then after the second or third pagination it keeps showing the same, the last, item for the rest of the paginations.

Sorry, I don't get what you say.

$page is the current page number, starting at 1.
$perPage is the number of the records found you show on one page.
$total is the number of total records found.

And you need to modify get_all_offers_pending() so that returns data set with limit and offset.
The limit is $perPage. The offset can be calculated from $page.

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



You have defined $total as 20, but how can we know it beforehand? With a regular data fetch from $perPage or $total doesn't work.


It only works when I call my datas within controller as :

PHP Code:

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


However, how can I join here with the same column names like fk ?



So, whatever I try, the result is not healthy.



P.S. edit for the uncceserray spaces in the post

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


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