Using Ransackers

colorfulberry edited this page May 27, 2018 · 93 revisions

The premise behind Ransack is to provide access to Arel predicate methods.

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.

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.

Ransackers don't necessarily have to be the convoluted messes that are sometimes seen on Stack Overflow. If they aren't fairly concise or don't use Arel, it's a sign that there might be a better way.

Some examples of using Ransackers follow.

1. Search on the name field reversed:

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

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

3. Search for a key in an hstore column (in this example, searching an hstore 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';

4. 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) %>

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

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

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

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

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

10. Search for a translated value in a jsonb column:

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!

Clone this wiki locally
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.