Help with Codeigniter v4 pagination Library

Hello, I found pagination library for Codeigniter v4 on github with this link https://github.com/KejawenLab/CI4Pager . I followed every step, But I get issue on displaying data, I get all the data even I provide a number of limit to be shown per page. But for pagination links it work correct. I'm now using codeigniter 4.0.4.  Please help, how can I limit number of result.

Here is my Code
//controller

PHP Code:

    public function index()
    {
        helper('paginator');
        $db = \Config\Database::connect();
        $result = $db->query('SELECT * FROM users');
        $paginator = \App\Libraries\Paginator::createFromResult($result, 1, 1);
        return view('page', ['paginator'=> $paginator]);
    } 


//view

PHP Code:

        <?php foreach($paginator->getResults() as $row):?>
            <div><?= $row['username'] ?></div>
        <?php endforeach;?>

        <?= ci_pager($paginator, [
                'base_url' => '/vyombo',
                'current_text' => 'Current Page',
                'total_text' => 'Total Records',
            ]);
        ?>

Why not use the one that comes with CI4? https://codeigniter.com/user_guide/libra...ation.html

@venance,

...or use something like DataTables (https://datatables.net/)

@venance,

...or use something like DataTables (https://datatables.net/)

I know the datatable is great, But I really don't like. I like codeigniter 4 pager, but is not working with the query builder.
I wan't to use that external library. I hope it works fine even if I don't get it right to work. I will appreciate if will get help with it. Thanks!

---

Why not use the one that comes with CI4? https://codeigniter.com/user_guide/libra...ation.html

Yes. And I always used a pager library. But I want to perform my own sql with query() method. Thus why I found that library will be helpeful to me.
But if there's a way I can user $db->query("SELECT subQuery... FROM table"); with paginate() please help.

Pager works just fine with Query Builder, do what you want in the model with Query Build
but do not tell it to return a result set instead return $this

Then it's chainable.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Pager works just fine with Query Builder, do what you want in the model with Query Build
but do not tell it to return a result set instead return $this

Then it's chainable.

Do you mean I can do this way:

$this->db->query("SELECT * FROM table");
return $this;

---

Pager works just fine with Query Builder, do what you want in the model with Query Build
but do not tell it to return a result set instead return $this

Then it's chainable.

Do you mean I can do this way:

$this->db->query("SELECT * FROM table");
return $this;

I tried to perform my query like this to
//my Model

PHP Code:

    public function getData(){       
        $this->table = 'province_income';
        $sql = "
        SELECT Date, 
        SUM(Income) as Income, SUM(Expense) as Expense, SUM(Income) - SUM(Expense) as Profit 
        FROM ( 
                  SELECT 
                      province_income.date as Date, 
                      province_income.amount as Income, 
                      0 as Expense 
                  FROM province_income
                  UNION ALL 
                  SELECT 
                      province_expenses.date as Date, 
                      0 as Income, 
                     province_expenses.amount as Expense 
                  FROM province_expenses
           ) as t 
        GROUP BY Date
      ";

        $this->db->query($sql);
        return $this;
    } 


// my controller

PHP Code:

public function index(){
        $data['results'] =$this->model->getData()->paginate(1)
        $data['pager']  = $this->model->pager->links();
        return view('Test/index', $data);
} 


//View

PHP Code:

<?php foreach ($results as $result): ?>
    <div>
        <p> <?= $result['Date'] ?></p>
        <p> <?= $result['Income'] ?></p>
        <p> <?= $result['Expense'] ?></p>
        <p> <?= $result['Profit'] ?></p>
    </div>
<?php endforeach;?>

But I get error:

PHP Code:

Undefined index: Date
Undefined index: Income
Undefined index:Expense
Undefined index: Profit 


How can I make it work?

Thank you all for the support and help, I carefully followed your device and I get a solution to my issue. I'd like to hear if this solution has no down effect to the app performance.

Here is what I have done to model class;

PHP Code:

class IncomesModel extends Model
{
    protected $table = "incomes"; 

    public function __construct(){
       parent::__construct();
    }

    public function getData()
    {
        //reset table to be used as default
        $this->table = 'view_province_incomes';

        //creating view table to your database to hold the data
          $sql = "CREATE OR REPLACE VIEW view_province_incomes AS ";
        $sql .= "
        SELECT Date, 
        SUM(Income) as Income, 
        SUM(Expense) as Expense, 
        SUM(Income) - SUM(Expense) as Profit 
        FROM ( 
                  SELECT 
                      province_income.date as Date, 
                      province_income.amount as Income, 
                      0 as Expense 
                  FROM province_income
                  LEFT JOIN province ON province.provId = province_income.provId
                  WHERE province_income.provId = 5
                  UNION ALL 
                  SELECT 
                      province_expenses.date as Date, 
                      0 as Income, 
                     province_expenses.amount as Expense 
                  FROM province_expenses
                  LEFT JOIN province ON province.provId = province_expenses.provId
                  WHERE province_expenses.provId = 5 
           ) as t 
        GROUP BY Date
       ";
      
       $this->db->query($sql);
       return $this; //to make easy chaining with the instance
    }
} 


//Controller

PHP Code:

  public function index()
  {
      $data['results'] = $this->model->getData()->asObject()->paginate(4);
      $data['pager']   = $this->model->pager->links();
      return view('Income/index', $data);
  } 


//View

PHP Code:

<?php foreach ($results as $result): ?>
    <div style="display: flex; justify-content: space-between;">
        <p> <?= $result->Date ?></p>
        <p> <?= $result->Income ?></p>
        <p> <?= $result->Expense ?></p>
        <p> <?= $result->Profit ?></p>
    </div>
<?php endforeach;?>

<!-- Pagination -->
<?= $pager ?>

My Output

PHP Code:

+------------+-----------------------------+
| Date       | Incomes  | Expense | Profit |
+------------+----------+---------+--------+
| 2018-06-12 | 10000    | 5000    | 5000   | 
+------------+----------+---------+--------+
| 2018-06-14 | 17000    | 9000    | 8000   | 
+------------+----------+---------+--------+
| 2018-06-15 | 8000     | 6000    | 2000   |
+------------------------------------------+ 


Any suggestion, advice, alternatives please.
Thank you very much!