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.3'
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:
- Symbol of the attribute name (required).
- An array of values to order by, such as
%w(high medium low)
or[true, false]
(optional). - Sort direction,
:asc
or:desc
(optional). Default::asc
;:desc
when values to order by are specified. - 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.
Post.published.order_home #=> #<ActiveRecord::Relation>
Post.published.order_home_reverse #=> #<ActiveRecord::Relation>
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>
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>
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
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.