order_query

This gem finds the next or previous record(s) relative to the current one efficiently. It is also useful for implementing infinite scroll. It uses keyset pagination to achieve this.

Installation

Add to Gemfile:

gem 'order_query', '~> 0.3.1'

Usage

Define named order columns with order_query:

class Post < ActiveRecord::Base
  include OrderQuery
  order_query :order_home,
    [:pinned, [true, false]],
    [:published_at, :desc],
    [:id, :desc]
end

Order query accepts a list of order columns as varargs or one array, each one specified as:

[<attribute name>, (attribute values in order), (:asc or :desc), (options hash)]

Available options:

option	description
unique	Unique attribute. Default: true for primary key, false otherwise.
sql	Customize attribute value SQL

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

Looping to the first / last record is enabled 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(*spec) 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>

Alternatively, get an OrderQuery::Point using the seek(scope, *spec) 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 options 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.