Ransackers

Add custom search functions

The main premise behind Ransack is to provide access to Arel predicate methods. Ransack provides special methods, called ransackers, for creating additional search functions via Arel.

A ransacker method can return any Arel node that allows the usual predicate methods. Custom ransackers are an expert feature, and require a thorough understanding of Arel.

Arel

Here are some resources for more information about Arel:

Ransacker methods enable search customization and are placed in the model. Arguments may be passed to a ransacker method via ransacker_args (see Example #6 below).

Ransackers, like scopes, are not a cure-all. Many use cases can be better solved with a standard Ransack search on a dedicated database search field, which is faster, index-able, and scales better than converting/ransacking data on the fly.

Example Ransackers

Search on field

Search on the name field reversed:

# in the model:
ransacker :reversed_name, formatter: proc { |v| v.reverse } do |parent|
  parent.table[:name]
end

Search using Datetime

Convert a user string input and a database datetime field to the same date format to find all records with a datetime field (created_at in this example) equal to that date :

# in the model:
ransacker :created_at do
  Arel.sql('date(created_at)')
end

in the view:
<%= f.search_field(
  :created_at_date_equals, placeholder: t(:date_format)
  ) %>
...
<%= sort_link(@search, :created_at, default_order: :desc) %>

# config/initializers/ransack.rb
Ransack.configure do |config|
  config.add_predicate 'date_equals',
    arel_predicate: 'eq',
    formatter: proc { |v| v.to_date },
    validator: proc { |v| v.present? },
    type: :string
end

2.1

It seems to be enough to change the model only, but don't forget to define the type that will returned as well.

# in the model:
ransacker :created_at, type: :date do
  Arel.sql('date(created_at)')
end

2.2. Postgresql with time zones

If you're using different time zones for Rails and Postgresql you should expect to have some problems using the above solution. Example:

Rails at GMT -03:00
Postgresql at GMT -00:00 (UTC)

A timestamp like 2019-07-18 01:21:29.826484 will be truncated to 2019-07-18. But for your Rails application 2019-07-18 01:21:29.826484 is 2019-07-17 22:21:29.826484 at your time zone (GMT -03:00). So it should be truncated to 2019-07-17 instead.

So, you should convert the timestamp to your current Rails time zone before extracting the date.

# in the model:
ransacker :created_at, type: :date do
  Arel.sql("date(created_at at time zone 'UTC' at time zone '#{Time.zone.name}')")
end

Note that Time.zone.name should return a time zone string suitable for Postgresql.

Postgres columns

Search on a fixed key in a jsonb / hstore column:

In this example, we are searching a table with a column called properties for records containing a key called link_type.

For anything up to and including Rails 4.1, add this to your model

ransacker :link_type do |parent|    
  Arel::Nodes::InfixOperation.new('->>', parent.table[:properties], 'link_type')
end

When using Rails 4.2+ (Arel 6.0+), wrap the value in a build_quoted call

ransacker :link_type do |parent|    
  Arel::Nodes::InfixOperation.new('->>', parent.table[:properties], Arel::Nodes.build_quoted('link_type'))
end

In the view, with a search on link_type_eq using a collection select (for example with options like 'twitter', 'facebook', etc.), if the user selects 'twitter', Ransack will run a query like:

SELECT * FROM "foos" WHERE "foos"."properties" ->> 'link_type' = 'twitter';

To use the JSONB contains operator @> see here: [[PostgreSQL JSONB searches]].

Type conversions

Convert an integer database field to a string in order to be able to use a cont predicate (instead of the usual eq which works out of the box with integers) to find all records where an integer field (id in this example) contains an input string:

Simple version, using PostgreSQL:

# in the model:
ransacker :id do
  Arel.sql("to_char(id, '9999999')")
end

and the same, using MySQL:

ransacker :id do
  Arel.sql("CONVERT(#{table_name}.id, CHAR(8))")
end

A more complete version (using PostgreSQL) that adds the table name to avoid ambiguity and strips spaces from the input:

ransacker :id do
  Arel.sql(
    "regexp_replace(
      to_char(\"#{table_name}\".\"id\", '9999999'), ' ', '', 'g')"
  )
end

In the view, for all 3 versions:

<%= f.search_field :id_cont, placeholder: 'Id' %>
...
<%= sort_link(@search, :id) %>

Concatenated fields

Search on a concatenated full name from first_name and last_name (several examples):

# in the model:
ransacker :full_name do |parent|
  Arel::Nodes::InfixOperation.new('||',
    parent.table[:first_name], parent.table[:last_name])
end

# or, to insert a space between `first_name` and `last_name`:
ransacker :full_name do |parent|
  Arel::Nodes::InfixOperation.new('||',
    Arel::Nodes::InfixOperation.new('||',
      parent.table[:first_name], ' '
    ),
    parent.table[:last_name]
  )
end
# Caveat: with Arel >= 6 the separator ' ' string in the
# preceding example needs to be quoted as follows:
ransacker :full_name do |parent|
  Arel::Nodes::InfixOperation.new('||',
    Arel::Nodes::InfixOperation.new('||',
      parent.table[:first_name], Arel::Nodes.build_quoted(' ')
    ),
    parent.table[:last_name]
  )
end

# works also in mariadb
ransacker :full_name do |parent|
  Arel::Nodes::NamedFunction.new('concat_ws',
    [Arel::Nodes::SqlLiteral.new("' '"), parent.table[:first_name], parent.table[:last_name]])
end

# case insensitive lookup
ransacker :full_name, formatter: proc { |v| v.mb_chars.downcase.to_s } do |parent|
  Arel::Nodes::NamedFunction.new('LOWER',
    [Arel::Nodes::NamedFunction.new('concat_ws',
      [Arel::Nodes::SqlLiteral.new("' '"), parent.table[:first_name], parent.table[:last_name]])])
end

Passing arguments

Passing arguments to a ransacker: Arguments may be passed to a ransacker method via ransacker_args:

class Person
  ransacker :author_max_title_of_article_where_body_length_between,
  args: [:parent, :ransacker_args] do |parent, args|
    min, max = args
    query = <<-SQL
      (SELECT MAX(articles.title)
         FROM articles
        WHERE articles.person_id = people.id
          AND CHAR_LENGTH(articles.body) BETWEEN #{min.to_i} AND #{max.to_i}
        GROUP BY articles.person_id
      )
    SQL
    Arel.sql(query)
  end
end

# Usage
Person.ransack(
  conditions: [{
    attributes: {
      '0' => {
        name: 'author_max_title_of_article_where_body_length_between',
        ransacker_args: [10, 100]
      }
    },
    predicate_name: 'cont',
    values: ['Ransackers can take arguments']
  }]
)

=> SELECT "people".* FROM "people" WHERE (
     (SELECT MAX(articles.title)
        FROM articles
       WHERE articles.person_id = people.id
         AND CHAR_LENGTH(articles.body) BETWEEN 10 AND 100
       GROUP BY articles.person_id
     )
   LIKE '%Ransackers can take arguments%')
   ORDER BY "people"."id" DESC

Dropdowns

Adding the attribute values associated with a column name to a searchable attribute in a dropdown options (instead of a traditional column name coming from a table). This is useful if using an associated table which is acting as a join table between a parent table and domain table. This will cache the data as the selections:

# in the model:
Model.pluck(:name).each do |ground|
  ransacker ground.to_sym do |parent|
    Arel::Nodes::InfixOperation.new('AND',
      Arel::Nodes::InfixOperation.new('=', parent.table[:gor_name], ground),
      parent.table[:status]
    )
  end
end

# This will not include the column names in the dropdown
def self.ransackable_attributes(auth_object = nil)
  %w() + _ransackers.keys
end

Testing for existence

Testing for the existence of a row in another table via a join:

# in the model:
ransacker :price_exists do |parent|
  # SQL syntax for PostgreSQL -- others may differ
  # This returns boolean true or false
  Arel.sql("(select exists (select 1 from prices where prices.book_id = books.id))")
end

In the view

  %td= f.select :price_exists_true, [["Any", 2], ["No", 0], ["Yes", 1]]

Associations

Performing a query on an association with a differing class name:

Say we have a model "SalesAccount", which represents a relationship between two users, one being designated as a "sales_rep". We want to query SalesAccounts by the name of the sales_rep:

# in the model:
class SalesAccount < ActiveRecord::Base
  belongs_to :user
  belongs_to :sales_rep, class_name: :User

# in the controller:
  # The line below would lead to errors thrown later if not for the
  # "joins(:sales_reps)".
  @q = SalesAccount.includes(:user).joins(:sales_rep).ransack(params[:q])
  @sales_accounts = @q.result(distinct: true)

In the view:

<%= f.search_field :sales_rep_name_start %>

Search on translations

Search for a translated value in a jsonb column:

Note: There is also a gem, Mobility Ransack, which allows you to search on translated attributes independent of their storage backend.

This will work with any jsonb data type. In this case I have a column translated with Mobility called name with the value {'en': "Hello", 'es': "Hola"}.

ransacker :name do |parent|    
  Arel::Nodes::InfixOperation.new('->>', parent.table[:name], Arel::Nodes.build_quoted(Mobility.locale))
end

If using Rails 4.1 or under, remove the build_quoted call.

You can then search for name_eq or name_cont and it will do the proper SQL.

Please feel free to contribute further code examples!

Add custom search functions​

Arel​

Example Ransackers​

Search on field​

Search using Datetime​

2.1​

2.2. Postgresql with time zones​

Postgres columns​

Type conversions​

Concatenated fields​

Passing arguments​

Dropdowns​

Testing for existence​

Associations​

Search on translations​