order_query Build Status Code Climate Coverage Status

order_query gives you next or previous records relative to the current one efficiently.

For example, you have a list of items, sorted by priority. You have 10,000 items! If you are showing the user a single item, how do you provide buttons for the user to see the previous item or the next item?

You could pass the item's position to the item page and use OFFSET in your SQL query. The downside of this, apart from having to pass a number that may change, is that the database cannot jump to the offset; it has to read every record until it reaches, say, the 9001st record. This is slow. Here is where order_query comes in!

order_query uses the same ORDER BY query, but also includes a WHERE clause that excludes records before (for next) or after (for prev) the current one.

Installation

Add to Gemfile:

gem 'order_query', '~> 0.1.3'

Usage

Define a list of order conditions with order_query:

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

An order condition is specified as an attribute name, optionally an ordered list of values, and a sort direction. Additional options are:

option description
unique Unique attribute, avoids redundant comparisons. Default: true for primary key, false otherwise.
complete Complete attribute, avoids redundant comparisons. Default: false for ordered lists, true otherwise.
sql Customize attribute value SQL

Order scopes

Order scopes are defined by order_query:

Post.order_for_index         #=> ActiveRecord::Relation<...>
Post.reverse_order_for_index #=> ActiveRecord::Relation<...>

Before, after, previous, and next

An method is added by order_query to query around a record:

# get the order object, scope default: Post.all
p = Post.find(31).order_for_index(scope) #=> OrderQuery::RelativeOrder<...>
p.before         #=> ActiveRecord::Relation<...>
p.previous       #=> Post<...>
# pass true to #next and #previous in order to loop onto the the first / last record
# will not loop onto itself
p.previous(true) #=> Post<...>
p.position   #=> 5
p.next       #=> Post<...>
p.after      #=> ActiveRecord::Relation<...>

Order conditions, advanced example

class Issue < ActiveRecord::Base
  include OrderQuery
  order_query :order_display, [
    # Pass an array for attribute order, and an optional sort direction for the array,
    # default is *:desc*, so that first in the array <=> first in the result
    [:priority, %w(high medium low), :desc, complete: true],
    # Sort attribute can be a method name, provided you pass :sql for the attribute
    [:valid_votes_count, :desc, sql: '(votes - suspicious_votes)'],
    # Default sort order for non-array attributes is :asc, just like SQL
    [:updated_at, :desc],
    # pass unique: true for unique attributes to get more optimized queries
    # default: true for primary_key, false otherwise
    [:id, :desc, unique: true]
  ]
  def valid_votes_count
    votes - suspicious_votes
  end
end

Dynamic order conditions

To query with dynamic order conditions use Model.order_by_query and Model#relative_order_by_query:

Issue.order_by_query([[:id, :desc]])         #=> ActiveRecord::Relation<...>
Issue.reverse_order_by_query([[:id, :desc]]) #=> ActiveRecord::Relation<...>
Issue.find(31).relative_order_by_query([[:id, :desc]]).next #=> Issue<...>
Issue.find(31).relative_order_by_query(Issue.visible, [[:id, :desc]]).next #=> Issue<...>

For example, consider ordering by a list of ids returned from an elasticsearh query:

ids = Issue.keyword_search('ruby') #=> [7, 3, 5]
Issue.where(id: ids).order_by_query([[:id, ids]]).first(2).to_a #=> [Issue<id=7>, Issue<id=3>]

How it works

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

SELECT ... WHERE
x0 OR
y0 AND (x1 OR
        y1 AND (x2 OR
                y2 AND ...))
ORDER BY ...
LIMIT 1

Where x correspond to > / < terms, and y to = terms (for resolving ties), per order criterion.

A query may then look 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

A query for the advanced example would look like this:

-- Current issue: priority='high' (votes - suspicious_votes)=4 updated_at='2014-03-19 10:23:18.671039' id=9
SELECT  "issues".* FROM "issues"  WHERE
  ("issues"."priority" IN ('medium','low') OR
   "issues"."priority" = 'high' AND (
       (votes - suspicious_votes) < 4 OR
       (votes - suspicious_votes) = 4 AND (
           "issues"."updated_at" < '2014-03-19 10:23:18.671039' OR
           "issues"."updated_at" = '2014-03-19 10:23:18.671039' AND
               "issues"."id" < 9)))
ORDER BY
  "issues"."priority"='high' DESC,
  "issues"."priority"='medium' DESC,
  "issues"."priority"='low' DESC,
  (votes - suspicious_votes) DESC,
  "issues"."updated_at" DESC,
  "issues"."id" DESC
LIMIT 1

The top-level x0 OR .. clause is actually wrapped with x0' AND (x0 OR ...), where x0' is a non-strict condition, for performance reasons. This can be disabled with OrderQuery::WhereBuilder.wrap_top_level_or = false.

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

This project uses MIT license.