SortParam
Sort records using a query parameter based on JSON API's sort parameter format.
In a nutshell, this gem converts comma-separated sort fields from this:
?sort=+users.first_name,-users.last_name:nulls_last,users.email
to this:
users.first_name asc, users.last_name desc nulls last, users.email asc
or to this:
{"users.first_name"=>{:direction=>:asc}, "users.last_name"=>{:direction=>:desc, :nulls=>:last}, "users.email"=>{:direction=>:asc}}
Features
- Sort field whitelisting.
- Supports
ORDER BY
expression generation for MySQL and PG. - Parsing of comma-separated sort fields into hash for any further processing.
- Specifying
NULL
sort order.
Installation
Add this line to your application's Gemfile:
gem 'sort_param'
And then execute:
bundle install
Or install it yourself as:
gem install sort_param
Usage
Basic
1. Whitelist/define the sort fields
sort_param = SortParam.define do
field :first_name, nulls: :first
field :last_name
end
OR we can do:
sort_param = SortParam::Definition.new
.field(:first_name, nulls: :first)
.field(:last_name)
field
method accepts the column name as the first argument. Any default column configuration such as :nulls
(for NULLS FIRST
or NULLS LAST
sort order) follows the name.
2. Parse sort fields from a parameter
The load!
method translates a given sort string/fields parameter to an SQL ORDER BY expression or to a Hash:
I. PostgreSQL example
sort_param.load!("+first_name,-last_name", mode: :pg)
=> "first_name asc nulls first, last_name desc"
II. MySQL example
sort_param.load!("+first_name,-last_name", mode: :mysql)
=> "first_name is not null, first_name asc, last_name desc"
III. Hash example
sort_param.load!("+first_name,-last_name")
=> {"first_name"=>{:nulls=>:first, :direction=>:asc}, "last_name"=>{:direction=>:desc}}
Any other additional column option set in SortParam::Definition
or SortParam.define
will be included in the column's hash value.
For example:
sort_param = SortParam.define do
field :first_name, foo: :bar, nulls: :first
end
sort_param.load!("+first_name")
=> {"first_name"=>{:foo=>:bar, :nulls=>:first, :direction=>:asc}}
sort_param.load!("-first_name:nulls_last")
=> {"first_name"=>{:foo=>:bar, :nulls=>:last, :direction=>:desc}}
IV. Example with explicit nulls sort order
Example in PG mode:
sort_param.load!("+first_name:nulls_last,-last_name:nulls_first", mode: :pg)
=> "first_name asc nulls last, last_name desc nulls first"
Ignoring non-whitelisted sort fields instead of raising error
Use #load
method instead:
sort_param = SortParam.define do
field :first_name
end
sort_param.load("+first_name,+last_name", mode: :pg)
=> "first_name asc"
Output a different field name
Set the :rename
field option to a string value or a Proc to output a different field name.
sort_param = SortParam.define do
field :first_name, rename: 'users.name'
field :last_name, rename: ->(col) { "users.#{col}" }
end
sort_param.load!("+first_name", mode: :pg)
=> "users.name asc"
sort_param.load!("+first_name", mode: :mysql)
=> "users.name asc"
sort_param.load!("+first_name")
=> {"users.name"=>{:direction=>:asc}}
sort_param.load!("+last_name")
=> {"users.last_name"=>{:direction=>:asc}}
Whitelisting multiple fields with the same options at once
Use #fields
instead of #field
:
sort_param = SortParam.define do
fields :first_name, :last_name, nulls: :first, rename: ->(col) { "users.#{col}" }
field :email
end
NOTE: Unlike #field
, #fields
can only accept a Proc for the :rename
option.
Rails example
controller
def index
render json: User.all.order(sort_fields)
end
private
def sort_fields
SortParam.define do
field :first_name
field :last_name, nulls: :first
end.load!(sort_param, mode: :pg)
end
def sort_param
params[:sort].presence || "+first_name,+last_name"
end
We can DRY this up a bit by creating a concern:
controllers/concerns/has_sort_param.rb
module HasSortParam
extend ActiveSupport::Concern
def sort_param(default: nil, &block)
raise ArgumentError.new('Missing block') unless block_given?
definition = SortParam.define(&block)
definition.load!(params[:sort].presence || default, mode: :pg)
end
end
controller v2
def index
render json: User.all.order(sort_fields)
end
private
def sort_fields
sort_param default: '+first_name,-last_name' do
field :first_name
field :last_name, nulls: :first
end
end
Error
Class | Description |
---|
SortParam::UnsupportedSortField | Raised when loading sort string via #load! and a sort field from the parameter isn't included in the whitelisted sort fields. |
Development
After checking out the repo, run bin/setup
to install dependencies. Then, run rake spec
to run the tests. You can also run bin/console
for an interactive prompt that will allow you to experiment.
To install this gem onto your local machine, run bundle exec rake install
.
Contributing
Bug reports and pull requests are welcome on GitHub at https://github.com/jsonb-uy/sort_param.
License
The gem is available as open source under the terms of the MIT License.