What is a query offset?

Question

I saw this in 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 a get_items method for the 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 starting at offset

I can probably implement a sql statement limit for parameter 10, but which sql statement will match $ page_no? What does Cohan mean with respect to the “page starting at offset”

Answer 1

This corresponds to the 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 the table, but give me only the $limit entries, starting with the $offset entry.

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

Further information in the MySQL documentation:

12.2.8 SELECT Syntax

DISCLAIMER: $limit and $offset are used in this question for understanding only. Of course, you do not want to create a query without proper alignment of values.

Answer 2

This specific comment, unfortunately, confuses the two common ways of considering pagination or grouping in queries. The SELECT syntax, as Andrew describes, allows the 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 quote was made. In this case, it is more useful to request a specific page number.

To compare these two options, consider the case when you performed a search and went to page 3, with 10 points per page. Elements 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 parameter $page_no in the above example probably wants to be 3, the page number. In this case, it will be necessary to calculate the SQL offset. Given that get_items does not seem to be a standard model method, it is probably calculated there; alternatively, the pagination library seems to have the sql_offset property, which is likely to compute it for you. In any case, the calculation is quite simple:

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