Welcome Guest, Not a member yet? Register   Sign In
How can I use a join to paginate the model?
(This post was last modified: 11-10-2022, 08:48 AM by arslanturk.)

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();                
//->join('contact', 'contact.customer_id = customers.id', 'left')
 //->join('address', 'address.customer_id = customers.id', 'left')

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? Smile

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.

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 :


It seems your query is wrong. Check your query.

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.

(This post was last modified: 11-22-2022, 09:23 PM by kenjis.)

Using Model::paginate() for SQL Join in CodeIgniter 4 | Welcome to Abdul Malik Ikhsan's Blog

Codeigniter 4 Pagination with join tables showing error - Stack Overflow

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.

Theme © iAndrew 2016 - Forum software by © MyBB