Get range starting from a specific record / item-keyed pagination

user_2232127 0
Viewed 153

My end goal is to create pagination and I'm wondering if there's a better or correct way to do things.

Given a Table with multiple columns for sorting, as in ORDER BY s1, s2, s3 I want to get the next n records after a given record. It's assumed that I know the full record including it's values for s1, s2, s3.

What I came up with so far looks like this:

-- Given current entry (prefixed with e_), get next n records

SELECT * FROM Entries
  WHERE
    (s1 < e_s1) OR
    (s1 = e_s1 AND s2 < e_s2) OR
    (s1 = e_s1 AND s2 = e_s2 AND s3 < e_s3)
  ORDER BY s1, s2, s3
  LIMIT n;

While I did create an index over (s1, s2, s3) I feel that this is not the most efficient or elegant way to do this. The query also gets bloated the more sort keys there are. Even so, this query is easy to adapt to look backwards, which I also need to be able to do.

I don't have access to the current offset of the record and I fear that it might be more inefficient to go by that way since I have to calculate it by using a subquery and aggregation (window functions are not available on my platform since it's android).

Is there a better/more elegant/efficient way of querying the data?

Here's an SQL Fiddle with some sample data

Important for anyone using this method: You should always have a unique column as the last column in the ordering and comparing plus the corresponding index. This makes the index perform better and also provides the necessary tie-breaking when you have two equal rows.

This of course is not necessary when there's a unique constraint on the combination of your columns [i.e. there's an unique index on (s1, s2, s3)]

where-clause sql-order-by sqlite sql
2 Answers

SQLite supports tuple comparison, so you can do:

SELECT * 
FROM Entries
WHERE (s1, s2, s3) < (29, 30, 30)
ORDER BY s1, s2, s3
LIMIT 5;
user_106767160

Much better way would be using OFFEST with the LIMIT and then no need to put any conditions in the WHERE. Every page you retrieve, you just need to remember the offset (or give back to user as "next token"). The offset of course starts from 0. Every call to a page you increase (or decrease if you want to go backwards) the offset by n.

Like this:

SELECT * FROM Entries
  ORDER BY s1, s2, s3
  LIMIT n OFFSET offset

Note1: the syntax of the limit could also be written: LIMIT {offset},{limit}.

Note2: the ORDER BY must be unique, to make sure we always get same entry at same offset.

https://www.sqlite.org/lang_select.html#limitoffset

If you do NOT want to use the offset, you can concat with padding all indexes and do one comparison. This looks more elegant:

SELECT * FROM Entries
  WHERE 
     PRINTF('%04d%04d%04d',s1,s2,s3) < PRINTF('%04d%04d%04d',29,30,30)
  ORDER BY s1, s2, s3
  LIMIT 5;

The simple padding was taken from here.

user_52872210
Related