order_query

This gem finds the next or previous record(s) relative to the current one efficiently using keyset pagination, e.g. for navigation or infinite scroll.

Installation

Add to Gemfile:

gem 'order_query', '~> 0.5.5'

Usage

Use order_query(scope_name, *order_option) to create scopes and class methods
in your model and specify how you want results ordered. A basic example:

class Post < ActiveRecord::Base
  include OrderQuery
  order_query :order_home,
    [:pinned, [true, false]], # First sort by :pinned over t/f in :desc order
    [:published_at, :desc] # Next sort :published_at in :desc order
end

Each order option specified in order_query is an array in the following form:

1. Symbol of the attribute name (required).
2. An array of values to order by, such as %w(high medium low) or [true, false] (optional).
3. Sort direction, :asc or :desc (optional). Default: :asc; :desc when values to order by are specified.
4. A hash (optional):

option	description
unique	Unique attribute. Default: true for primary key, false otherwise.
sql	Customize column SQL.
nulls	If set to :first or :last, orders NULLs accordingly.

If no unique column is specified, [primary_key, :asc] is used. Unique column must be last.

Scopes for ORDER BY

Post.published.order_home         #=> #<ActiveRecord::Relation>
Post.published.order_home_reverse #=> #<ActiveRecord::Relation>

Before / after, previous / next, and position

First, get an OrderQuery::Point for the record:

p = Post.published.order_home_at(Post.find(31)) #=> #<OrderQuery::Point>

It exposes these finder methods:

p.before   #=> #<ActiveRecord::Relation>
p.after    #=> #<ActiveRecord::Relation>
p.previous #=> #<Post>
p.next     #=> #<Post>
p.position #=> 5

The before and after methods also accept a boolean argument that indicates
whether the relation should exclude the given point or not.
By default the given point is excluded, if you want to include it,
use before(false) / after(false).

If you want to obtain only a chunk (i.e., a page), use before or after
with ActiveRecord’s limit method:

p.after.limit(20) #=> #<ActiveRecord::Relation>

Looping to the first / last record is enabled for next / previous by default. Pass false to disable:

p = Post.order_home_at(Post.order_home.first)
p.previous        #=> #<Post>
p.previous(false) #=> nil

Even with looping, nil will be returned if there is only one record.

You can also get an OrderQuery::Point from an instance and a scope:

posts = Post.published
post  = posts.find(42)
post.order_home(posts) #=> #<OrderQuery::Point>

Dynamic columns

Query with dynamic order columns using the seek(*order) class method:

space = Post.visible.seek([:id, :desc]) #=> #<OrderQuery::Space>

This returns an OrderQuery::Space that exposes these methods:

space.scope           #=> #<ActiveRecord::Relation>
space.scope_reverse   #=> #<ActiveRecord::Relation>
space.first           #=> scope.first
space.last            #=> scope_reverse.first
space.at(Post.first)  #=> #<OrderQuery::Point>

OrderQuery::Space is also available for defined order_queries:

Post.visible.order_home_space #=> #<OrderQuery::Space>

Alternatively, get an OrderQuery::Point using the seek(scope, *order) instance method:

Post.find(42).seek(Post.visible, [:id, :desc]) #=> #<OrderQuery::Point>
# scope defaults to Post.all
Post.find(42).seek([:id, :desc]) #=> #<OrderQuery::Point>

Advanced example

class Post < ActiveRecord::Base
  include OrderQuery
  order_query :order_home,
    # For an array of order values, default direction is :desc
    # High-priority issues will be ordered first in this example
    [:priority, %w(high medium low)],
    # A method and custom SQL can be used instead of an attribute
    [:valid_votes_count, :desc, sql: '(votes - suspicious_votes)'],
    # Default sort order for non-array columns is :asc, just like SQL
    [:updated_at, :desc],
    # pass unique: true for unique attributes to get more optimized queries
    # unique is true by default for primary_key
    [:id, :desc]
  def valid_votes_count
    votes - suspicious_votes
  end
end

How it works

Internally this gem builds a query that depends on the current record’s values and looks like this:

-- Current post: pinned=true published_at='2014-03-21 15:01:35.064096' id=9
SELECT "posts".* FROM "posts"  WHERE
  ("posts"."pinned" = 'f' OR
   "posts"."pinned" = 't' AND (
      "posts"."published_at" < '2014-03-21 15:01:35.064096' OR
      "posts"."published_at" = '2014-03-21 15:01:35.064096' AND "posts"."id" < 9))
ORDER BY
  "posts"."pinned"='t' DESC, "posts"."pinned"='f' DESC,
  "posts"."published_at" DESC,
  "posts"."id" DESC
LIMIT 1

The actual query is a bit different because order_query wraps the top-level OR with a (redundant) non-strict column x0' AND (x0 OR ...)
for performance reasons.
This can be disabled with OrderQuery.wrap_top_level_or = false.

See the implementation in sql/where.rb.

See how this affects query planning in Markus Winand’s slides on Pagination done the Right Way.

This project uses MIT license.