What is a query offset?

Question

I saw this at the Kohana documentation:

$content = new View('pages/items');
$items = new Items_Model;

$content->items = $items->get_items($page_no, 10); // page to get starting at offset, number of items to get

As you can see, we can assume that we have get_items method for Items model that receives 2 parameters, $page_no and 10 (as shown here). I know that 10 is the number of items to get and $page_no is the page to get starting at offset

I can probably implement a limit sql statement for the 10 parameter, but what sql statement will correspond to $page_no? What does Kohana mean regarding "page to get starting at offset"

Ducal

Answer 1

It corresponds to a LIMIT statement:

SELECT something FROM table LIMIT $limit OFFSET $offset;

//or alternatively
SELECT something FROM table LIMIT $offset,$limit;

In other words, select something from table, but only give me $limit records starting from record $offset.

$offset = ($page_no - 1) * $limit
_{$page_no is 1 based.}

More information in the MySQL documentation:

12.2.8 SELECT Syntax

DISCLAMER: $limit and $offset is used in this question only to aid understanding. Of course, you do not want to build a query without proper value escaping.

Remscheid

you might also want to actually multiply the page number by the limit amount, to get the obvious pagination behavior, otherwise, you are only skipping single rows, rather than page-size chunks of rows. – Herne

Answer 2

That particular comment unfortunately confuses two common ways to consider pagination or grouping in queries. The SELECT syntax, as Andrew describes, allows an OFFSET parameter, the number of items to skip before returning anything. However, it is most often used with pagination, as in the pagination library from which your quotation was taken. In this case, it is more useful to ask for a specific page number.

To compare the two, consider a case where you've made a search and have gone to page 3, with 10 items per page. Items 1-20 were on the first two pages; therefore, the OFFSET parameter will be 20:

SELECT * FROM table WHERE searched LIMIT 10 OFFSET 20

or

SELECT * FROM table WHERE searched LIMIT 20,10

Unfortunately, the $page_no parameter in the cited example probably wants to be 3, the page number. In that case, the SQL offset will have to be calculated. Given that get_items does not seem to be a standard model method, it's probably calculated in there; alternatively, the pagination library seems to have a property called sql_offset which probably calculates it for you. Either way, the calculation is easy enough:

$offset = max($page_no - 1, 0) * $limit;

Baumgartner