CODEWITHSUNDEEP
phpcakephpcakephp-3.0cakephp-3.x
Andy
Andy

Reputation: 5405

How do you calculate the OFFSET value used by page() in CakePHP 3.x?

CakePHP 3.x

According to the docs

To limit the number of rows or set the row offset you can use the limit() and page() methods:

// Fetch rows 50 to 100
$query = $articles->find()
->limit(50)
->page(2);

What this does is generates the equivalent of the following SQL query:

LIMIT 50 OFFSET 50

I can't work out how you calculate the value 2 to pass to page()? As per the comment it says "fetch rows 50 to 100".

But diving 100/50 to get 2 is not the answer.

Assume you wanted to calculate the page() values for the following?

LIMIT 250 OFFSET 250  // page(2)

LIMIT 250 OFFSET 500  // page(3)

LIMIT 250 OFFSET 750  // page(4)

LIMIT 250 OFFSET 1000 // page(5)

I've put in a comment - from doing some debugging - what the values of page() are to generate those OFFSET queries.

Am I missing something here?? If we take the last example LIMIT 250 OFFSET 1000, where do you get a value of 5 for page()??

For clarification the PHP used for that last condition would look like this:

$query = $model->find()->limit(250)->page(5);

Then debug($query); will show that the actual SQL generated is 

SELECT ... LIMIT 250 OFFSET 1000

Given that many libraries (e.g. DataTables) would only provide you with the values for LIMIT (250) and OFFSET (1000), how are you supposed to calculate the page() value that Cake needs to produce the actual results from the database?

Upvotes: 2

Views: 3580

Answers (2)

rads
rads

Reputation: 11

It's probably too late to answer maybe, but recently I had similar issues while building pagination query and hence got to learn a bit about this.

When we set the limit($limit)->page($page), this will divide all the results in equal parts with each part having $limit number of results. So imagine this as multiple pages holding $limit number of results in each page. Then $page will be indirectly acting as offset for this query.

For example LIMIT 250 OFFSET 1000 will be - total results divided in parts of 250 results each. Then we want results from 1000th offset. Since offset indexing is 0-based, this offset will be in the 5th page - 1001th record. That means the value that should be given to page() will be 5.

Upvotes: 1

Andy
Andy

Reputation: 5405

The answer, which was provided by @ndm in a comment, is that there is an offset() method.

This method is not well documented - it doesn't appear in the Cookbook but does appear in the API Docs: https://api.cakephp.org/3.5/class-Cake.Database.Query.html#_offset

So, the answer to the question is it can be done like this:

$query = $model->find()->limit(250)->offset(1000);

It is also worth noting that the Cookbook does not document every feature. There are some things that appear in the API Docs that are not in the Cookbook, so worth checking both. This is a good example of such a thing.

Upvotes: 3

Related Questions