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

Question

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?

Where is that documented? I've just done a search for `offset` on the documentation I linked to, and it's not there! It does work though, and thank you very much. — Andy, Mar 14 '18 at 14:36
In the API docs: **https://api.cakephp.org/3.5/class-Cake.Database.Query.html#_offset**. The Cookbook doesn't cover the whole API, though in this case it wouldn't hurt if the example would show the `offset()` usage too. — ndm, Mar 14 '18 at 14:38
@ndm. Since the Cookbook does not cover this argument I think this comment could be useful for other people too, so why don't you put it as an answer? — arilia, Mar 15 '18 at 07:14
@arilia Sometimes I'm on the go, and just leave a quick comment... sometimes OPs add the solution as an answer themselves in such cases, sometimes not, sometimes I add one later on... Feel free to add an answer yourself if you stumble over something like this, unless someone's indicating to add an answer themselves, it's usually appreciated :) — ndm, Mar 15 '18 at 11:42
I've answered it and credited @ndm. Feel free to edit the answer if anyone thinks it can be improved. — Andy, Mar 15 '18 at 15:02

score 3 · Answer 1 · answered Mar 15 '18 at 15:02

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.

I second cross-checking the API. You'll be amazing to see what you'll find and learn :] — tfont, Dec 03 '18 at 12:13

score 1 · Answer 2 · edited Oct 01 '21 at 06:36

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.

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

2 Answers2