Welcome Guest, Not a member yet? Register   Sign In
How can I use a join to paginate the model?
#1
Question 
(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();                
$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.
Reply
#2

I guess no one will answer this question? Smile
Reply
#3

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.
Reply
#4

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'
Reply
#5

Sharing the error I got :

https://prnt.sc/DR8HyyNLic8l
Reply
#6

It seems your query is wrong. Check your query.
Reply
#7

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.
Reply
#8

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.
Reply
#9

(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
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
Reply
#10

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.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB