Keyset Pagination

Problem

id author published_at
3 Kathaleen Drovin 2019-03-12 10:20:14
1 Shelton Flippen 2019-03-03 13:23:51
2 Dreama Langone 2019-03-01 07:00:22
5 Evon Marguez  2019-02-11 10:32:40
4 Hermelinda Pinson 2019-02-03 21:20:20
id author published_at
3 Kathaleen Drovin 2019-03-12 10:20:14
1 Shelton Flippen 2019-03-03 13:23:51
2 Dreama Langone 2019-03-01 07:00:22
5 Evon Marguez  2019-02-11 10:32:40
4 Hermelinda Pinson 2019-02-03 21:20:20

1 2

id author published_at
8 Tai Shoffner
 2019-01-29 01:05:13
9 Stacia Sours  
 2019-01-28 11:41:28
6 Irma Rennie   2019-01-13 01:59:45
10 Agatha Kemper   2019-01-11 19:22:08
7 Renee Brey   2019-01-08 02:34:11

1 2

SELECT * FROM news
ORDER BY published_at DESC
OFFSET 5
LIMIT 5
SELECT * FROM news
ORDER BY published_at DESC
OFFSET 5
LIMIT 5

…the rows are first sorted according to the <order by clause> and then limited by dropping the number of rows specified in the <result offset clause> from the beginning…

SQL:2016, Part 2, §4.15.3 Derived tables

id author published_at
1 Shelton Flippen 2019-03-03 13:23:51
2 Dreama Langone 2019-03-01 07:00:22
3 Kathaleen Drovin 2019-01-29 01:05:13
4 Hermelinda Pinson 2019-02-11 10:32:40
5 Evon Marguez 2019-02-03 21:20:20
6 Lenna Timms  
 2019-01-08 02:34:11
7 Renee Brey  2019-01-30 01:05:13
8 Tai Shoffner 2019-01-28 11:41:28
9 Irma Rennie   2019-01-13 01:59:45
10 Agatha Kemper   2019-01-11 19:22:08 
SELECT * FROM news;

ORDER BY published_at DESC
OFFSET 5
LIMIT 5
id author published_at
3 Kathaleen Drovin 2019-03-12 10:20:14
1 Shelton Flippen 2019-03-03 13:23:51
2 Dreama Langone 2019-03-01 07:00:22
5 Evon Marguez  2019-02-11 10:32:40
4 Hermelinda Pinson 2019-02-03 21:20:20
7 Renee Brey   2019-01-30 02:34:11
8 Tai Shoffner 2019-01-29 01:05:13
9 Stacia Sours   2019-01-28 11:41:28
6 Irma Rennie   2019-01-13 01:59:45
10 Agatha Kemper   2019-01-11 19:22:08 
SELECT * FROM news
ORDER BY published_at DESC;
OFFSET 5
LIMIT 5
id author published_at
3 Kathaleen Drovin 2019-03-12 10:20:14
1 Shelton Flippen 2019-03-03 13:23:51
2 Dreama Langone 2019-03-01 07:00:22
5 Evon Marguez  2019-02-11 10:32:40
4 Hermelinda Pinson 2019-02-03 21:20:20
7 Renee Brey   2019-01-30 02:34:11
8 Tai Shoffner 2019-01-29 01:05:13
9 Stacia Sours   2019-01-28 11:41:28
6 Irma Rennie   2019-01-13 01:59:45
10 Agatha Kemper   2019-01-11 19:22:08 
SELECT * FROM news
ORDER BY published_at DESC
OFFSET 5
LIMIT 5

Problems

  • Fetching offset + limit records (needed only limit)
  • Sorting offset + limit records (index?)
  • What if something changes?
id author published_at
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03
7 Renee Brey   2019-01-30
8 Tai Shoffner 2019-01-29
9 Stacia Sours   2019-01-28
6 Irma Rennie   2019-01-13
10 Agatha Kemper   2019-01-11
id author published_at
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03
7 Renee Brey   2019-01-08
8 Tai Shoffner 2019-01-29
9 Stacia Sours   2019-01-28
6 Irma Rennie   2019-01-13
10 Agatha Kemper   2019-01-11 
News.find(9).update(published_at: Time.current)
id author published_at
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03
7 Renee Brey   2019-01-08
8 Tai Shoffner 2019-01-29
9 Stacia Sours   2019-01-28
6 Irma Rennie   2019-01-13
10 Agatha Kemper   2019-01-11 
News.find(9).update(published_at: Time.current)
id author published_at
9 Stacia Sours   2019-03-19
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03
7 Renee Brey   2019-01-30
8 Tai Shoffner 2019-01-29
6 Irma Rennie   2019-01-13
10 Agatha Kemper   2019-01-11

*click on news tab*

SELECT * FROM news
ORDER BY published_at DESC
OFFSET 0
LIMIT 5

*click on news tab*

id author published_at
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03 
SELECT * FROM news
ORDER BY published_at DESC
OFFSET 0
LIMIT 5

*click on news tab*

id author published_at
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03 
SELECT * FROM news
ORDER BY published_at DESC
OFFSET 0
LIMIT 5
News.find(9)
    .update(published_at: Time.current)

*click on news tab*

id author published_at
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03 
SELECT * FROM news
ORDER BY published_at DESC
OFFSET 0
LIMIT 5
News.find(9)
    .update(published_at: Time.current)
SELECT * FROM news
ORDER BY published_at DESC
OFFSET 5
LIMIT 5

*click*

*click on news tab*

id author published_at
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03 
SELECT * FROM news
ORDER BY published_at DESC
OFFSET 0
LIMIT 5
News.find(9)
    .update(published_at: Time.current)
SELECT * FROM news
ORDER BY published_at DESC
OFFSET 5
LIMIT 5

*click*

*click on news tab*

4 Hermelinda Pinson 2019-02-03
7 Renee Brey   2019-01-30
8 Tai Shoffner 2019-01-29
6 Irma Rennie   2019-01-13
10 Agatha Kemper   2019-01-11
id author published_at
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03 
SELECT * FROM news
ORDER BY published_at DESC
OFFSET 0
LIMIT 5
News.find(9)
    .update(published_at: Time.current)
SELECT * FROM news
ORDER BY published_at DESC
OFFSET 5
LIMIT 5

*click*

*click on news tab*

4 Hermelinda Pinson 2019-02-03
7 Renee Brey   2019-01-30
8 Tai Shoffner 2019-01-29
6 Irma Rennie   2019-01-13
10 Agatha Kemper   2019-01-11
id author published_at
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03
7 Renee Brey   2019-01-08
8 Tai Shoffner 2019-01-29
9 Stacia Sours   2019-01-28
6 Irma Rennie   2019-01-13
10 Agatha Kemper   2019-01-11

1. page

2. page

News.find(9).update(published_at: Time.current)
id author published_at
9 Stacia Sours   2019-03-19
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03
7 Renee Brey   2019-01-08
8 Tai Shoffner 2019-01-29
6 Irma Rennie   2019-01-13
10 Agatha Kemper   2019-01-11

1. page

2. page

Keyset pagination

Keyset pagination

SELECT * FROM news
WHERE published_at < previous_date
ORDER BY published_at DESC
LIMIT 5

*click on news tab*

*click on news tab*

SELECT * FROM news
WHERE published_at < NOW()
ORDER BY published_at DESC
LIMIT 5
id author published_at
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03

*click on news tab*

SELECT * FROM news
WHERE published_at < NOW()
ORDER BY published_at DESC
LIMIT 5
id author published_at
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03 
News.find(9)
    .update(published_at: Time.current)

*click on news tab*

SELECT * FROM news
WHERE published_at < NOW()
ORDER BY published_at DESC
LIMIT 5
id author published_at
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03 
News.find(9)
    .update(published_at: Time.current)

*click*

*click on news tab*

SELECT * FROM news
WHERE published_at < NOW()
ORDER BY published_at DESC
LIMIT 5
SELECT * FROM news
WHERE published_at < '2019-02-03'
ORDER BY published_at DESC
LIMIT 5
id author published_at
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03 
News.find(9)
    .update(published_at: Time.current)

*click*

SELECT * FROM news
WHERE published_at < '2019-02-03'
ORDER BY published_at DESC
LIMIT 5

*click on news tab*

SELECT * FROM news
WHERE published_at < NOW()
ORDER BY published_at DESC
LIMIT 5
7 Renee Brey 2019-01-30
8 Tai Shoffner 2019-01-29
6 Irma Rennie   2019-01-13
10 Agatha Kemper   2019-01-11
id author published_at
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03
7 Renee Brey   2019-01-08
8 Tai Shoffner 2019-01-29
9 Stacia Sours   2019-01-28
6 Irma Rennie   2019-01-13
10 Agatha Kemper   2019-01-11

1. page

2. page

News.find(9).update(published_at: Time.current)
id author published_at
9 Stacia Sours   2019-03-19
3 Kathaleen Drovin 2019-03-12
1 Shelton Flippen 2019-03-03
2 Dreama Langone 2019-03-01
5 Evon Marguez  2019-02-11
4 Hermelinda Pinson 2019-02-03
7 Renee Brey   2019-01-30
8 Tai Shoffner 2019-01-29
6 Irma Rennie   2019-01-13
10 Agatha Kemper   2019-01-11

1. page

2. page

SELECT * FROM news
WHERE published_at < '2019-02-03'
ORDER BY published_at DESC
LIMIT 5
id author published_at
3 Kathaleen 2019-03-12
1 Shelton 2019-03-03
2 Dreama 2019-03-01
5 Evon  2019-02-11
4 Hermelin 2019-02-03 
News.find(1).destroy
id author published_at
3 Kathaleen 2019-03-12
2 Dreama 2019-03-01
5   Evon 2019-02-11
4 Hermelin  2019-02-03
7 Renee 2019-01-30
8 Tai 2019-01-29
9 Stacia 2019-01-28
6 Irma  2019-01-13
10 Agatha  2019-01-11

Basic

Keyset

id author published_at
3 Kathaleen 2019-03-12
2 Dreama 2019-03-01
5   Evon 2019-02-11
4 Hermelin  2019-02-03
7 Renee 2019-01-30
8 Tai 2019-01-29
9 Stacia 2019-01-28
6 Irma  2019-01-13
10 Agatha  2019-01-11
7 Renee 2019-01-30
8 Tai 2019-01-29
9 Stacia 2019-01-28
6 Irma  2019-01-13
10 Agatha  2019-01-11
id author published_at
3 Kathaleen 2019-03-12
1 Shelton 2019-03-03
2 Dreama 2019-03-01
5 Evon  2019-02-11
4 Hermelin 2019-02-03
7 Renee 2019-01-30
8 Tai 2019-01-29
9 Stacia 2019-01-28
6 Irma  2019-01-13
10 Agatha  2019-01-11
id author published_at
3 Kathaleen 2019-03-12
1 Shelton 2019-03-03
2 Dreama 2019-03-01
5 Evon  2019-02-11
4 Hermelin 2019-02-03
7 Renee 2019-01-30
8 Tai 2019-01-29
9 Stacia 2019-01-28
6 Irma  2019-01-13
10 Agatha  2019-01-11 
News.create(author: 'Janella', published_at: Time.current)

Basic

Keyset

id author published_at
11 Janella 2019-03-19
3 Kathaleen 2019-03-12
1 Shelton 2019-03-03
2 Dreama  2019-03-01
5 Evon 2019-02-11
4 Hermelin 2019-02-03
7 Renee 2019-01-30
8 Tai 2019-01-29
9 Stacia 2019-01-28
6 Irma  2019-01-13
10 Agatha  2019-01-11
id author published_at
11 Janella 2019-03-19
3 Kathaleen 2019-03-12
1 Shelton 2019-03-03
2 Dreama  2019-03-01
5 Evon 2019-02-11
4 Hermelin 2019-02-03
7 Renee 2019-01-30
8 Tai 2019-01-29
9 Stacia 2019-01-28
6 Irma  2019-01-13
10 Agatha  2019-01-11
id author published_at
3 Kathaleen 2019-03-12
1 Shelton 2019-03-03
2 Dreama 2019-03-01
5 Evon  2019-02-11
4 Hermelin 2019-02-03
7 Renee 2019-01-30
8 Tai 2019-01-29
9 Stacia 2019-01-28
6 Irma  2019-01-13
10 Agatha  2019-01-11

Basic

Keyset

With index

Without index

Overview

  • Fetching all records
  • Sorting all records
  • Does not work when records change
  • Fetching offset + limit records
  • Sorting offset + limit records
  • Does not work when records change
  • Fetching records that meet WHERE condition
  • Sorting  records that meet WHERE condition
  • Fetching exactly limit records

Basic

Keyset

With index

Without index

Overview

  • Fetching all records
  • Sorting all records
  • Does not work when records change
  • Fetching offset + limit records
  • Sorting offset + limit records
  • Does not work when records change
  • Fetching records that meet WHERE condition
  • Sorting  records that meet WHERE condition
  • Fetching exactly limit records

gem 'order_query'

gem 'order_query'

class News < ActiveRecord::Base
  include OrderQuery

  order_query :order_published, [:published_at, :desc]
end

Scopes for ORDER BY:

Post.order_published         #=> #<ActiveRecord::Relation>
Post.order_published_reverse #=> #<ActiveRecord::Relation>

p = News.order_published_at(News.find(params[:id])) #=> #<OrderQuery::Point>

p.before          #=> #<ActiveRecord::Relation>
p.after           #=> #<ActiveRecord::Relation>
p.after.limit(20) #=> #<ActiveRecord::Relation>
p.previous        #=> #<News>
p.next            #=> #<News>
p.position        #=> 5

Any questions?

More about order_query gem

More about keyset pagination

Keyset pagination

By ceki101

Made with Slides.com

Keyset pagination

  • 121

More from ceki101