Model::paginate() behavior when $page exceeds the last page

PHP Version

8.3

CodeIgniter4 Version

4.1.5

CodeIgniter4 Installation Method

Manual (zip or tar.gz)

Which operating systems have you tested for this bug?

Linux

Which server did you use?

apache

Database

MySQL 5.6

What happened?

When i paginate the model like this $UsersMode->orderBy('id_user', 'ASC')->paginate(8, 'group', $page_number);

returns "SELECT * FROM users ORDER BY id_user ASC LIMIT 8, 8" based on offset and per page number

But my total count rows in the table are 24, so when i reach the end 16, 8 it returns me the same query as the last existing page in my database even with $page_number = 100

Steps to Reproduce

	public function load_more($page_number = 2)
	{
		$UsersModel 	= new UsersModel();
		
		$this->ViewData['Page'] = $page_number;
		
		$this->ViewData['Users'] = $UsersMode->orderBy('id_user', 'ASC')->paginate(8, 'group', $page_number);

		echo $UsersModel->db->getLastQuery();
	}

returns "SELECT * FROM users ORDER BY id_user ASC LIMIT 8, 8"

But my total count rows in the table are 24, so when i reach the end 16, 8 $page_number = 10 and go to a page number more than 3 it returns the same query "SELECT * FROM users ORDER BY id_user ASC LIMIT 16, 8"

Expected Output

Going to a page number more than the total count rows of my table it should return the propper offset even if i have the total rows less than the per page number or less than the total count

$page_number = 10 it should returns the query "SELECT * FROM users ORDER BY id_user ASC LIMIT 80, 8" even if i don't have enough records in my database table

Anything else?

No response

one this BaseModel
https://github.com/codeigniter4/CodeIgniter4/blob/develop/system/BaseModel.php#L1273

	public function paginate(?int $perPage = null, string $group = 'default', ?int $page = null, int $segment = 0)
	{
		// Since multiple models may use the Pager, the Pager must be shared.
		$pager = service('pager');

		if ($segment !== 0) {
			$pager->setSegment($segment, $group);
		}

		$page = $page >= 1 ? $page : $pager->getCurrentPage($group);
		// Store it in the Pager library, so it can be paginated in the views.
		$this->pager = $pager->store($group, $page, $perPage, $this->countAllResults(false), $segment);
		$perPage     = $this->pager->getPerPage($group);
		$offset      = ($pager->getCurrentPage($group) - 1) * $perPage;

		return $this->findAll($perPage, $offset);
	}

should be converted to something like this

	public function paginate(?int $perPage = null, string $group = 'default', ?int $page = null, int $segment = 0)
	{
		// Since multiple models may use the Pager, the Pager must be shared.
		$pager = service('pager');

		if ($segment !== 0) {
			$pager->setSegment($segment, $group);
		}

		$page = $page >= 1 ? $page : $pager->getCurrentPage($group);
		// Store it in the Pager library, so it can be paginated in the views.
		$this->pager = $pager->store($group, $page, $perPage, $this->countAllResults(false), $segment);
		$offset      = ($page) * $perPage;
		$perPage     = $this->pager->getPerPage($group);

		return $this->findAll($perPage, $offset);
	}

@crustamet

@crustamet I don't understand the difference. Please show the diff by diff command or git diff.

$page_number = 10 it should returns the query "SELECT * FROM users ORDER BY id_user ASC LIMIT 80, 8" even if i don't have enough records in my database table

Why?

it doesn't matter the difference because how i did it is no good it is just to point out that if the total records in the database are 10

when you go to page 1000

the query remains as of the last page so the limit whould be {a long number}, {8}

I dont have pagination only a load more button

"Why?" - because i cannot remove the button when i have no records, it always gives me records even when i don't have so it always shows load more

$page_number = 10 it should returns the query "SELECT * FROM users ORDER BY id_user ASC LIMIT 80, 8" even if i don't have enough records in my database table

Why?

First of all it should return that query if you use

$Model->paginate(8, 'group', 10);
it should create the query above ? right or wrong ?

While the current implemented code is dependent on how many rows you actually have in the table

If you have 10 users and you are at page 10

$Model->paginate(8, 'group', 10);
This returns "SELECT * FROM users ORDER BY id_user ASC LIMIT 16, 8"

I cannot return a 404 page if i have no more users on page 10 LOL

It seems this behavior is intentional.

CodeIgniter4/tests/system/Models/PaginateModelTest.php

Lines 78 to 85 in 8fc8985

    
           public function testPaginatePageOutOfRange(): void 
        
           { 
        
               $this->createModel(ValidModel::class); 
        
               $this->model->paginate(1, 'default', -500); 
        
               $this->assertSame(1, $this->model->pager->getCurrentPage()); 
        
               $this->model->paginate(1, 'default', 500); 
        
               $this->assertSame($this->model->pager->getPageCount(), $this->model->pager->getCurrentPage()); 
        
           }

You can check if it has any more pages with $pager->hasMore().

Ok i get what you are saying, I dig this $pager->hasMore() method it does what I need a kind of a checker, but i have millions in my database, i just can't wrap my head around this one why it is an intentional behavior. while the query speed is slower when you select from millions at the last page than if you select from millions from a page that does not exists.
Furthermore you don't actually need to add additional code to check this as pagination model will return an empty array if you go out of range

Anyways up to you guys, $pager->hasMore() is missing from the documentation

I don't know why the pagination behaves like that.
But test code is document that describes how the system works,
so as you see the test code, the current behavior is intentional.

If we change the behavior, it will be an breaking change.

If you insist that this behavior is something that needs to be corrected,
please send a PR to 4.6 branch and explain reasons that many people would agree.
If it is to meet the needs of just one of you, you can simply customize the pagination.

In my opinion, this pagination was not originally designed to handle very large amounts of data at high speed.
If you want to process large amounts of data at high speed,
I recommend a different implementation without using OFFSET.

I agree with everything you said, actually i will extend the current BaseModel paginate() maybe in time i will understand why it returns data even if it is out of range.

I hope some other guy read this and maybe help us understand better

I've known about this behavior for a long time.
At first, this caused a misunderstanding of "Why?" Such a feature, I think, was popular in 2009.
This is not critical at this stage. Problems may appear if you use paginate() for the API (strict result is important).
As a fix, it is possible to create a new strictPaginate() method and remove this behavior

The method name pagenateStrict() is better, but I agree with adding the new method.

i made it and tested it
The old one

    public function paginate(?int $perPage = null, string $group = 'default', ?int $page = null, int $segment = 0)
    {
        // Since multiple models may use the Pager, the Pager must be shared.
        $pager = Services::pager();

        if ($segment) {
            $pager->setSegment($segment, $group);
        }

        $page = $page >= 1 ? $page : $pager->getCurrentPage($group);
        // Store it in the Pager library, so it can be paginated in the views.
        $this->pager = $pager->store($group, $page, $perPage, $this->countAllResults(false), $segment);
        $perPage     = $this->pager->getPerPage($group);
        $offset      = ($pager->getCurrentPage($group) - 1) * $perPage;

        return $this->findAll($perPage, $offset);
    }

The new one

	public function paginateStrict(?int $perPage = null, string $group = 'default', ?int $page = null, int $segment = 0)
	{
		$pager = Services::pager();
		
		if ($segment) {
			$pager->setSegment($segment, $group);
		}
	
		$page = $page >= 1 ? $page : $pager->getCurrentPage($group);
		
		// Store it in the Pager library, so it can be paginated in the views.
		$this->pager = $pager->store($group, $page, $perPage, $this->countAllResults(false), $segment);
		
		$perPage     = $this->pager->getPerPage($group);
		$offset      = ($page - 1) * $perPage;
	
		return $this->findAll($perPage, $offset);
	}

I've looked at the code.

In a good way, you need to change Pager->store(), $page is illogically set in it, $page has a "raw" value in line 148, and then it in line 154 is changed to limit.

CodeIgniter4/system/Pager/Pager.php

Lines 147 to 154 in 4151c12

    
           if ($segment > 0 && $this->groups[$group]['currentPage'] > 0) { 
        
               $page = $this->groups[$group]['currentPage']; 
        
           } 
        
           $perPage ??= $this->config->perPage; 
        
           $pageCount = (int) ceil($total / $perPage); 
        
           $this->groups[$group]['currentPage'] = $page > $pageCount ? $pageCount : $page;

Similar actions in BaseModel->paginate(): On line 1282 it has one value, then it is not used for $offset

CodeIgniter4/system/BaseModel.php

Lines 1282 to 1286 in 4151c12

    
           $page = $page >= 1 ? $page : $pager->getCurrentPage($group); 
        
           // Store it in the Pager library, so it can be paginated in the views. 
        
           $this->pager = $pager->store($group, $page, $perPage, $this->countAllResults(false), $segment); 
        
           $perPage     = $this->pager->getPerPage($group); 
        
           $offset      = ($pager->getCurrentPage($group) - 1) * $perPage;

So the main reason is to change the currentPage.
Will we find more options instead of editing the BaseModel?

I think this feature was implemented in the Feature.php

/**
 * The behavior of `limit(0)` in Query Builder.
 *
 * If true, `limit(0)` returns all records. (the behavior of 4.4.x or before in version 4.x.)
 * If false, `limit(0)` returns no records. (the behavior of 3.1.9 or later in version 3.x.)
 */
public bool $limitZeroAsAll = false;

Right ? this issue should be closed.

No. limit(0) ‐ switches receiving 1000/1000 rows or 0/1000 rows

So with with this

public bool $limitZeroAsAll = false;

If I have a total of 10 Pages
If I go to the Page 11, will get records ? or not ? :)))

If you still get records for sure this is not implemented... and in fact this limitZeroAsAll has nothing to do with pagination

#8278

i don't think you got my issue, This is another issue #8278

Please answer my question as the answer matters the most

If I have a total of 10 Pages
If I go to the Page 11, will get records ? or not ?

And it has nothing to do with ->limit(0,0);

I've tested it the answer is still YES they are still returning data even tough you are exceeding the total pages

If i go to the Page 11, but I only have records for 10 pages it should return NO RECORDS !

Why ? because of SEO they see duplicate pages and someone can create issues by just adding +1 to the page number

Page 11, Page 12, Page 10000 they will be all issues ! of duplicate content

I understood the problem, but you don't understand the $limitZeroAsAll parameter. That's why I pointed to the issue. The answer to your question is: it doesn't affect Pager.

You can extend the Pager class and overwrite the service. All. This is the fastest solution for you right now.
https://codeigniter4.github.io/userguide/extending/core_classes.html#replacing-core-classes

@neznaika0

I know @neznaika0 i did extended the pager for sure as i did not have any other solution.

that parameter $limitZeroAsAll should be related with the Pager from my point of view...

@crustamet

@crustamet I want to try to change the behavior. Do you have any idea what a list of pages should look like in HTML?

That is, we'll just change the Pager, the template will look broken on page 1000.

See which links should be shown for each page? Example: First, Prev, 997, 998, Prev, Last

# Page 10 of 10
# Model return N rows

[First](http://localhost:8080/index.php?page=1)
[Previous](http://localhost:8080/index.php?page=7)
[8](http://localhost:8080/index.php?page=8)
[9](http://localhost:8080/index.php?page=9)
[10](http://localhost:8080/index.php?page=10)

[Newer](http://localhost:8080/index.php?page=9)
[Older](http://localhost:8080/index.php?page=10#)

hasMore: 0
total: 100
perPage: 10
pageCount: 10
currentPage: 10
next:
previous: http://localhost:8080/index.php?page=9
current: http://localhost:8080/index.php?page=10
Last query: SELECT * FROM `db_job` LIMIT 90, 10

# Page 12 of 10
# Model return 0 rows

[First](http://localhost:8080/index.php?page=1)
[Previous](http://localhost:8080/index.php?page=9)
[10](http://localhost:8080/index.php?page=10)

[Newer](http://localhost:8080/index.php?page=11)
[Older](http://localhost:8080/index.php?page=12#)

hasMore: 0
total: 100
perPage: 10
pageCount: 10
currentPage: 12
next:
previous: http://localhost:8080/index.php?page=11
current: http://localhost:8080/index.php?page=12
Last query: SELECT * FROM `db_job` LIMIT 110, 10

# Page 1000 of 10
# Model return 0 rows

[First](http://localhost:8080/index.php?page=1)
[Previous](http://localhost:8080/index.php?page=997)

[Newer](http://localhost:8080/index.php?page=999)
[Older](http://localhost:8080/index.php?page=1000#)

hasMore: 0
total: 100
perPage: 10
pageCount: 10
currentPage: 1000
next:
previous: http://localhost:8080/index.php?page=999
current: http://localhost:8080/index.php?page=1000
Last query: SELECT * FROM `db_job` LIMIT 9990, 10

Is this supposed to be done for negative pages as well? Although I would implement the checks in the project, not in the Pager.

in the pager you can use abs((int)$value); to have it positive instead of negative and to escape it safely.

the template will look broken on page 1000. - it should show 404 Page instead of broken... as there are no records on the last page if you have no records.. Yet the query should only return records if you have records on that page.

Anyways the query should return 0 records, then you can check easily and show 404 yourself, the pager should not forcefully show the 404.

Ahh yes, if you are exceeding the page number, and you still show a 200 page. for sure the "next button" is disabled and the prev button should go on the last existing page.

When you have

    $data = [
        'users' => $model->paginate(10),
        'pager' => $model->pager,
    ];

this $model->paginate(10) should return empty array if you exceed the total pages...

You should also test if your pager are on segments instead of the query

No. There is no need to use abs(), this is how it works now: -1000 >> 1 I say is it necessary to show an error with strict pagination?

Of course, the 404 should be called by the programmer.
Yes, I indicated that there are 0 rows. This has little effect on the question - I'm wondering how to show the list of pages? First Last or Error

Navigation Menu

Repository navigation

`Model::paginate()` behavior when `$page` exceeds the last page #8904

PHP Version

CodeIgniter4 Version

CodeIgniter4 Installation Method

Which operating systems have you tested for this bug?

Which server did you use?

Database

What happened?

Steps to Reproduce

Expected Output

Anything else?

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	public function testPaginatePageOutOfRange(): void
	{
	$this->createModel(ValidModel::class);
	$this->model->paginate(1, 'default', -500);
	$this->assertSame(1, $this->model->pager->getCurrentPage());
	$this->model->paginate(1, 'default', 500);
	$this->assertSame($this->model->pager->getPageCount(), $this->model->pager->getCurrentPage());
	}

	if ($segment > 0 && $this->groups[$group]['currentPage'] > 0) {
	$page = $this->groups[$group]['currentPage'];
	}

	$perPage ??= $this->config->perPage;
	$pageCount = (int) ceil($total / $perPage);

	$this->groups[$group]['currentPage'] = $page > $pageCount ? $pageCount : $page;

	$page = $page >= 1 ? $page : $pager->getCurrentPage($group);
	// Store it in the Pager library, so it can be paginated in the views.
	$this->pager = $pager->store($group, $page, $perPage, $this->countAllResults(false), $segment);
	$perPage = $this->pager->getPerPage($group);
	$offset = ($pager->getCurrentPage($group) - 1) * $perPage;

Model::paginate() behavior when $page exceeds the last page #8904

Description

PHP Version

CodeIgniter4 Version

CodeIgniter4 Installation Method

Which operating systems have you tested for this bug?

Which server did you use?

Database

What happened?

Steps to Reproduce

Expected Output

Anything else?

Activity

crustamet commented on May 19, 2024

kenjis commented on May 19, 2024

kenjis commented on May 19, 2024

crustamet commented on May 19, 2024

crustamet commented on May 20, 2024

kenjis commented on May 20, 2024

kenjis commented on May 20, 2024

crustamet commented on May 20, 2024

kenjis commented on May 20, 2024

crustamet commented on May 20, 2024

neznaika0 commented on Jun 6, 2024

kenjis commented on Jun 7, 2024

crustamet commented on Jun 28, 2024

neznaika0 commented on Jul 2, 2024

crustamet commented on Jan 10, 2025

neznaika0 commented on Jan 10, 2025

crustamet commented on Jan 10, 2025

neznaika0 commented on Jan 10, 2025

crustamet commented on Jan 10, 2025

neznaika0 commented on Jan 10, 2025

crustamet commented on Jan 10, 2025

neznaika0 commented on Mar 3, 2026

crustamet commented on Mar 4, 2026

neznaika0 commented on Mar 4, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

`Model::paginate()` behavior when `$page` exceeds the last page #8904