order_query

by glebm

glebm /order_query

Find next / previous Active Record(s) in one query

454 Stars 15 Forks Last release: Not found Other 155 Commits 14 Releases

Available items

No Items, yet!

The developer of this repository has not created any items for sale yet. Need a bug fixed? Help with integration? A different license? Create a request here:

order_query Build Status Coverage Status

100% offset-free

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.0'

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
NULL
s 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         #=> #<:relation>
Post.published.order_home_reverse #=> #<:relation>

Before / after, previous / next, and position

First, get an

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

It exposes these finder methods:

p.before   #=> #<:relation>
p.after    #=> #<:relation>
p.previous #=> #
p.next     #=> #
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) #=> #<: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        #=> #
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) #=> #<:point>

Dynamic columns

Query with dynamic order columns using the

seek(*order)
class method:
space = Post.visible.seek([:id, :desc]) #=> #<:space>

This returns an

OrderQuery::Space
that exposes these methods:
space.scope           #=> #<:relation>
space.scope_reverse   #=> #<:relation>
space.first           #=> scope.first
space.last            #=> scope_reverse.first
space.at(Post.first)  #=> #<:point>

OrderQuery::Space
is also available for defined order_queries:
Post.visible.order_home_space #=> #<:space>

Alternatively, get an

OrderQuery::Point
using the
seek(scope, *order)
instance method:
Post.find(42).seek(Post.visible, [:id, :desc]) #=> #<:point>
# scope defaults to Post.all
Post.find(42).seek([:id, :desc]) #=> #<: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.

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.