• Stars
    star
    507
  • Rank 87,068 (Top 2 %)
  • Language
    Ruby
  • License
    MIT License
  • Created over 10 years ago
  • Updated over 2 years ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

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

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

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

More Repositories

1

i18n-tasks

Manage translation and localization with static analysis, for Ruby i18n
Ruby
2,060
star
2

rails_email_preview

Preview and edit app mailer templates in Rails.
Ruby
567
star
3

gulp-webpack-react-bootstrap-sass-template

Web App Client Template: React. Sass, Coffee, JSX. Bootstrap for Sass. Compiled with Gulp and Webpack.
CoffeeScript
111
star
4

to_spreadsheet

Render XLSX from Rails using existing views (html ⇒ xlsx)
Ruby
92
star
5

render-whitespace-on-github

Are they tabs? Are they spaces? How many? Never wonder again!
JavaScript
73
star
6

DOMBrew

A fast 2.5 KB client-side DOM builder
CoffeeScript
28
star
7

popper_js-rubygem

Popper.js assets as a Ruby gem. https://popper.js.org/
Ruby
24
star
8

katex-ruby

Renders KaTeX from Ruby.
Ruby
24
star
9

sass-rewrite-url

Automatically rewrite paths in url() calls. Avoid asset-url.
CSS
12
star
10

redis_stats

Ruby
4
star
11

blog-glebm-com

Sources for blog.glebm.com
CSS
3
star
12

critical-rubygem

The `critical` npm package wrapped as a Ruby gem
Ruby
3
star
13

mini_settings

A minimal key-value config file reader/writer in C
C
2
star
14

glebm-nokogiri

a version of nokogiri that doesn't have the segfault problem. installable from bundler as git
Ruby
2
star
15

advent-of-code

Julia
2
star
16

od-slitherlink

Dingoo Slitherlink re-packaged for OpenDingux and RetroFW
C++
1
star
17

has_cache_key

Ruby
1
star
18

LifeGL

OpenGL driven cellular automata simulator (Delphi, 2007)
1
star
19

timeago_js-rubygem

Timeago.js assets as a Ruby gem. http://timeago.org/
JavaScript
1
star
20

Dicey

Configurable Dice app for DnD and other games (Delphi) --go to wiki for details
1
star