How can I use a join to paginate the model?

Hello everyone,

I've been using ci4 for a long time. I am very pleased. Thank you.

If I come to my question. Now I have a model and I'm paginating. But I need to join from other tables.

and I want to do this with the model. How can I do ?

Code Example :

PHP Code:

$search_by  = $this->request->getget('search_by');
$page      = $this->request->getget('page');
$per_page  = $this->request->getget('per_page');
$sort_by    = $this->request->getget('sort_by');
$sort      = $this->request->getget('sort');

if ($page == ""){$page = 1;}
if ($per_page == ""){$per_page = 5;}
if ($sort_by == ""){$sort_by = "id";}
if ($sort == ""){$sort = "desc";}

$where = [
 'customer_id' => 5,
 'active' => 1,
 ];

$c  = new customers(); 
$pager = \Config\Services::pager();                
$customers= $c
 //->join('contact', 'contact.customer_id = customers.id', 'left')
 //->join('address', 'address.customer_id = customers.id', 'left')
 ->where($where)
 ->orderBy($sort_by, $sort)
 ->paginate($per_page, 'active_customers', $page);
  


When I activate the //join line on the model, the system gives an error.

I haven't been able to solve this issue for a long time.

Can you write a full sample code based on the example I gave?

Thank you from now.

I guess no one will answer this question?

Your code does not work in others environments, so it is difficult to write sample code.
I recommend you see the exact error message and do debug by yourself.

Or showing the exact error message would make you get more feedback.

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

Yeah, your error message would be fine.

It might sound weird or funny but can you please try your join lines without "customers".id  ? As in the example below:

PHP Code:

->join('contact', 'contact.customer_id = id', 'left')
->join('address', 'address.customer_id = id', 'left') 


Sharing the error I got :

https://prnt.sc/DR8HyyNLic8l

It seems your query is wrong. Check your query.

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

Hello there,

There is nothing wrong with the query. I am writing for the query in my first message. I can't join the model and this is a very serious problem.

The solution I'm looking for:

There will be both where and join, order by and pagination. How can I do it ?

Can you show me an example based on the first message?

Thank you.

I had also asked it here before but there is no exact answer. Not only here but also on internet. So, I agree. This is a huge missing point for CI. Pagination with joins in models looks like impossible.

Using Model::paginate() for SQL Join in CodeIgniter 4 | Welcome to Abdul Malik Ikhsan's Blog
https://samsonasik.wordpress.com/2020/03...igniter-4/

Codeigniter 4 Pagination with join tables showing error - Stack Overflow
https://stackoverflow.com/questions/6527...wing-error

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

If you have a GET method (url) for that page, due to a filter function of a form, then they are messed up and no result. Especially if you have also a where clause within this query.

The point is, the thing I can easily do with pure PHP cannot be easily done with our framework. For an easy and consistent CodeIgniter way of query, the method in the model should end with a classsical CI way, for example, $query->getResult(); However, now we have to start our statement with a "return" which is "a solution" and in the end it is not working properly. This is not a framework design kenjis. We are not trying something exraordinary or we do not need anything which is required only for one project. 

As I said, when I try it with pure php it works well but with CI it doesn't. And I don't want to mess up my project working with two different sql styles.