ruby on rails - SQL pagination based on last record retrieved -

ruby on rails - SQL pagination based on last record retrieved -

i need implement pagination semi-resilient info changing between paginations. standard pagination relies on sql's limit , offset, offset has potential become inaccurate new info points created or ranking shifts in sort.

one thought hold onto lastly info point requested api , next elements. don't know sql (we're using postgres), (certainly flawed) effort @ doing that. trying store position of lastly element 'rownum' , utilize in next query.

with rownum ( select *, row_number() on (order rank asc, id) rownum id = #{after_id} items ) select * items offset rownum order rank asc, id limit #{pagination_limit}

i can see issues this, if lastly item changes in rank. if can think of way this, great. confine single db query if possible since applications nail api.

your whole syntax doesn't quite work. offset comes after order by. from comes before where etc.

this simpler query think code supposed do:

select * items (rank, id) > ( select (rank, id) items id = #{after_id} ) order rank, id limit #{pagination_limit};

comparing composite type (rank, id) guarantees identical sort order.

make sure have two indexes:

a multicolumn index on (rank, id).

another 1 on (id) - have pk constraint on column doing already. (a multicolumn index leading id job well.)

more indexes: is composite index queries on first field?

if rank not volatile more efficient parameterize additionally instead of retrieving dynamically - volatility of rank seems point of deliberations ...

sql ruby-on-rails postgresql pagination sql-order-by