0

When using value based pagination

select * from articles 
where id > @start_value 
limit @page_size

how can I calculate @start_value given only page number?

Namely: say, I had a website and html page with a list of articles that I needed to paginate. But even to render the very 1st page, I'd need to calculate @start_value somehow. The input from a user would be a number of a page which he clicked; for the very first page it'd be 1 - by default.

  • given that 1, how would I calcualate @start_value?

  • or given any random page, still how would I calcualate @start_value?

Note that the values of the column id of a table aren't necessarily sequential, even if id is autoincremented.

Incerteza
  • 32,326
  • 47
  • 154
  • 261

2 Answers2

0

First off, pagination without any sorting is not ideal. You can't guarantee how SQL will sort your results without including and order by clause.

You will also need to know the page size to calculate your start value, but given @page_num, and @page_size: @start_value is calculated by @start_value = @page_num * @page_size;.

Here it is without the where clause and with limit/offset instead

    select * 
    from articles
    order by id
    limit @page_size
    offset (@page_size * @page_num)
Nick Dolan
  • 11
  • 1
  • Re-read my answer. You need the page_size in order to calculate the start_value. Your question even includes the `@pagesize` variable in the query. Does it deserve a downvote because I specified that this is the case? Good luck finding an answer, mate. – Nick Dolan Jan 31 '19 at 16:47
  • Maybe these will help: [Pagination #1](https://stackoverflow.com/questions/3520996/calculating-item-offset-for-pagination) [Pagination #2](https://stackoverflow.com/questions/27992413/how-do-i-calculate-the-offsets-for-pagination/27992616) [Math behind pagination](https://math.stackexchange.com/questions/68285/calculating-number-of-items-displayed-pagination) – Nick Dolan Jan 31 '19 at 17:01
  • Know the answer -- post it. I don't need the links. – Incerteza Jan 31 '19 at 22:13
0

You don't need the "where id > ..." part. The right way of achieving this is using limit @page_size offset @offset construct. This way you don't have to worry about the gaps. To calculate the offset based on page number, you just have to multiply page_size * page_number. Another important thing is that you should order your registers if you want to have the same result always. If you don't trust the IDs, you can order by date or another field. So:

select * from articles
order by date
limit @page_size
offset (@page_size * (@page_num-1))

Note: I used (@page_num-1) to start with a 0 offset for page 1.

jfc
  • 180
  • 1
  • 11
  • Hey, why downvoting. What you asked is not possible because of the gaps that you can have in IDs as you pointed out. Thats why the right way of doing pagination is using an offset in the number of records instead of doing it in the IDs, as I or @NickDolan stated. – jfc Jan 31 '19 at 16:48
  • Or rather only you know about is offset based pagination. – Incerteza Jan 31 '19 at 16:51
  • What you are asking for, is also an offset based pagination (what is @start_value but an offset?). The problem is that you want to apply that offset to the primary key (ID) instead of the number of records. Not saying it's impossible, but it won't paginate correctly because of the possible gaps. But if you prefer using ID, go on, you can calculate the first value using the min() function. Regards! – jfc Jan 31 '19 at 21:29
  • No, that's not it. – Incerteza Jan 31 '19 at 22:13