Welcome Guest, Not a member yet? Register   Sign In
Codeigniter 4 Pagination With Join and Where sql
#11

(Today, 12:51 AM)kenjis Wrote:
(Today, 12:28 AM)demyr Wrote: You have defined $total as 20, but how can we know it beforehand?

You can know it by querying the database.

PHP Code:
$total $model->where('offer_status''pending')->countAllResults(); 

Yeah, I know. That's what I tried, you see. We should add such notes to the documentation because there are lots of  beginner level users. (Actually I am working on an official blog project about "how to do x with CodeIgniter". We should clearly write tutorials there. I will ask help here about it soon).

I really wonder if anyone, including you Kenjis, has ever used such data fetching + pagination in real life. So that we will have the real example about it on internet.
Reply
#12

(Today, 12:28 AM)demyr Wrote: I believe limit and offset cannot solve the problem since offset creates a starting point until it reaches limit, am I right? says: "return only 10 records, start on record 16 (OFFSET 15)" on w3school

The explanation of limit and offset is no problem.
But I don't know why you believe limit and offset cannot solve the problem.

Using limit and offset is a common way to create pagination.
Reply
#13

(This post was last modified: Today, 04:45 AM by demyr.)

(Today, 03:35 AM)kenjis Wrote:
(Today, 12:28 AM)demyr Wrote: I believe limit and offset cannot solve the problem since offset creates a starting point until it reaches limit, am I right? says: "return only 10 records, start on record 16 (OFFSET 15)" on w3school

The explanation of limit and offset is no problem.
But I don't know why you believe limit and offset cannot solve the problem.

Using limit and offset is a common way to create pagination.

Because it returned me the items only between offset and limit and nothing else. For example, if it was set for 7,15 It starts from 16 + 7 items and same items for each pagination 1-2-3-4.. No different result. And if I fetch data from my Model, your codes below have no effect:

PHP Code:
$page    = (int) ($this->request->getGet('page') ?? 1);
$perPage 2;
$total  10;

// Call makeLinks() to make pagination links.
$pager_links $pager->makeLinks($page$perPage$total);
$data['pager_links'] = $pager_links


If I don't fetch data from Model but do it in my controller , like in the example below, then it works. But not in a healthy way for me because it is not my exact sql with joins .

PHP Code:
  $model = new \App\Models\AdminModels\OffersModel();
  $data['offers_pending'] = $model->where('offer_status''pending')->paginate(2); 
Reply
#14

(Today, 04:45 AM)demyr Wrote: For example, if it was set for 7,15 It starts from 16 + 7 items and same items for each pagination 1-2-3-4.. No different result.

If limit is 7:
Page 1: 1 to 7
Page 2: 8 to 14
Page 3: 15 to 21
Page 4: 22 to 28

You need two things for pagination.
1. pagination links
2. data to display

1. needs total count, per page, current page number.
2. needs data set to display.
Reply
#15

(Today, 05:33 AM)kenjis Wrote:
(Today, 04:45 AM)demyr Wrote: For example, if it was set for 7,15 It starts from 16 + 7 items and same items for each pagination 1-2-3-4.. No different result.

If limit is 7:
Page 1: 1 to 7
Page 2: 8 to 14
Page 3: 15 to 21
Page 4: 22 to 28

You need two things for pagination.
1. pagination links
2. data to display

1. needs total count, per page, current page number.
2. needs data set to display.

I understand. My problem is with the combination of 1 and 2 as I wrote you before. Your codes work when I do it in controller but then I cannot use my joins for sql.
This works as I told you but how can I use my joins? :

PHP Code:
$pager service('pager');
$count $model->where('offer_status''pending')->countAllResults();

$page    = (int) ($this->request->getGet('page') ?? 1);
$perPage 5;
$total  $count;

$data['offers_pending'] = $model->where('offer_status''pending')->paginate($perPage);

$pager_links $pager->makeLinks($page$perPage$total);
$data['pager_links'] = $pager_links
Reply
#16

You can't use Model's paginate() method. Because you try to use your own query with JOIN for the pagination.
The last thing you need to do is to set the correct data set to $data['offers_pending'].
Reply




Theme © iAndrew 2016 - Forum software by © MyBB