How to use pagination in complex query

Hello,



Thanks for all helped me to move forward with my project conversion to CI MVC , moving forward step by step with your help.

What I'm missing in my work, I don't know why query not getting page , only table gets it


 

namespace App\Models;
use CodeIgniter\Model;
use CodeIgniter\Database;
use CodeIgniter\Database\RawSql;

class ListModel extends Model
{
public static function  get_list()
{
$db = \Config\Database::connect();

$query = $db->query('SELECT customerName, customercity, customermail, ordertotal,salestotal
FROM onlinecustomers AS c
  INNER JOIN
  orders AS o
  ON c.customerid = o.customerid
  LEFT JOIN
  sales AS s
  ON o.orderId = s.orderId
  WHERE
salestotal > 100
and c.state in ('CA', 'TX')
and c.active = 1
and o.item_no in (1,5,15) ');
$result = $query->getResult();
return $result; 
  }

}
-----------------------
On My Controller


namespace App\Controllers;
class MyList extends BaseController
{

public function __construct() {
        helper('url');
      // Library("pagination");
      //$pager = \Config\Services::pager();
    }

public function index()
    {
        $listModel = new \App\ModelsListModel();
        $myquery= $listModel->get_list;    //just query works, but not accepting paging


            $data = [
              'listings' => $myquery ,                        (not showing anything)
              'pager' => $myquery->pager,               (getting error, I guess query doesn't allow paging)
              'total' => $myquery->countAll(),         (getting error, same not getting count as table)
              'page' => isset($_GET['page']) ? $_GET['page'] : 1,
              'perPage' => 15
        ];
       
        return view('default', $data);

----
and my default view
<?php
foreach($listings as $order):
echo $order['customerName'];
endforeach;

  if ($pager)
      echo  $pagi_path='mylist';
        $pager->setPath($pagi_path);
      echo $pager->links();
        endif
?>

How to use pagination in complex query

Thank you

Your using all single quotes ' change you beginning and ending quotes to double " quotes.
When it sees the next single quote it thinks its the end of that.

PHP Code:

$query = $db->query("SELECT customerName, customercity, customermail, ordertotal,salestotal
FROM onlinecustomers AS c
  INNER JOIN
  orders AS o
  ON c.customerid = o.customerid
  LEFT JOIN
  sales AS s
  ON o.orderId = s.orderId
  WHERE
salestotal > 100
and c.state in ('CA', 'TX')
and c.active = 1
and o.item_no in (1,5,15) "); 


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

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Hello,
Thank you for your time, and answer , I don't issue with query, executing and getting result, my issue ion paging, I'm getting array error when I try to get
'listings' => $myquery->paginate(10) ,           Call to a member function paginate() on array     (shows error)
              'pager' => $myquery->pager,       Attempt to read property "pager" on array          (getting error, I guess query doesn't allow paging)
              'total' => $myquery->countAll(),        (getting error, same not getting count as table)

 
My question is:
how can I do paging with above query ? not just single table which all example on the net just one table, even CI help site, I can't find single example how to do paging with multi tables query !
Is CI paging works only with single table as model ? that is all I need to know.

Thank you

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

Unfortunately the CodeIgniter Model does not work with the code like you wrote.
When you use Model's pagination, you need to use/extend the Model's functionality properly.
You used $db->query() but Model uses QueryBuilder inside. So you can't use $db->query()
for Model pagination.

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

Thank you,

I figured that, for days I couldn't find any example, nothing in documentation says user can't do query, I hope someone can update the help file explain this issue, looks MVC in general for advanced user not for beginners like me. I'm stuck on this.
Thank you again

It is documented.

Important
It is important to understand that the Model::paginate() method uses the Model and QueryBuilder methods. Therefore, trying to use $db->query() and Model::paginate() will not work because $db->query() executes the query immediately and is not associated with a QueryBuilder.

See https://www.codeigniter.com/user_guide/l...ith-models

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

Thank you so much,
Your explanation makes sense, and good one, now I understand what model used for, I assume model is active records keeps all data till the browser is closed, is this case if I have 100 users online means I have 100 open connection , is that right ?
Also, can someone show me to use QueryBuilder as above query as join tables, and use where
I know how to do it within one table as online doc explained, but I can't find out multi tables example with conditions
I'm learning it as I go with my project
Thank you again

See https://codeigniter4.github.io/CodeIgnit....html#join

And this is not for pagination, but a sample for join in a model.
See https://github.com/codeigniter4/shield/d...nt-4814631

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

    ->select('users.*)
    ->join('auth_groups_users agu', 'agu.user_id = users.id')
    ->whereIn('agu.group', 'superadmin')
    ->findAll();

more understandably than CI help .anyway thanks again for your help.

You may create a database view by using CREATE VIEW AS SELECT ....