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.

Monthly Downloads: 8,816
Programming language: Ruby
License: MIT License
Tags: Pagination     Projects    
Latest version: v0.5.0

order_query alternatives and similar gems

Based on the "Pagination" category.
Alternatively, view order_query alternatives based on common mentions on social networks and blogs.

Do you think we are missing an alternative of order_query or a related project?

Add another 'Pagination' Gem


order_query Build Status Coverage Status

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.


Add to Gemfile:

gem 'order_query', '~> 0.5.0'


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

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

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))
  "posts"."pinned"='t' DESC, "posts"."pinned"='f' DESC,
  "posts"."published_at" DESC,
  "posts"."id" DESC

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.

*Note that all licence references and agreements mentioned in the order_query README section above are relevant to that project's source code only.