Codeigniter 4 Pagination with join tables showing error

Question

I am working on codeigniter project. I am showing news table with join left news category and paginate. When I tried the below code I get an error Call to undefined method CodeIgniter\Database\MySQLi\Builder::paginate(). How to show news data with join and pagination?

    $db      = \Config\Database::connect();
    $news_tbl = $db->table('tbl_news')->join('tbl_category', 'tbl_news.category_id = tbl_category.category_id');
    $data['news_fetched'] = $news_tbl->paginate(10);
    $data['pager'] = $news_fetched->pager;
    $data['links'] = $data['pager']->links();

Answer 1

$db->table() creates a query builder object. Pagination works with model objects. You're not setting your pagination the right way. You should create a Model file called tblNewsModel that will handle pagination for you.

<?php
namespace App\Models;

class tblNewsModel extends \CodeIgniter\Model {

    protected $table = 'tbl_news';
    protected $primaryKey = 'your_pk_id';

    // your function to paginate
    public function paginateNews(int $nb_page) {
        return $this->select()->join('tbl_category', 'tbl_news.category_id = tbl_category.category_id')->paginate($nb_page);
    }

}

And then in your controller, just create a new instance of this model and ask him to give you the pagination

$tblNewsModel = new \App\Models\tblNewsModel();
$data['news_fetched'] = $tblNewsModel->paginateNews(10);
$data['pager'] = $tblNewsModel->pager;
$data['links'] = $data['pager']->links();