---
sidebar_position: 6
title: 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 `ransacker`s are an expert feature, and require a thorough understanding of Arel.

## Arel

Here are some resources for more information about Arel:

* [Using Arel to Compose SQL Queries](https://robots.thoughtbot.com/using-arel-to-compose-sql-queries)
* [The definitive guide to Arel, the SQL manager for Ruby](http://jpospisil.com/2014/06/16/the-definitive-guide-to-arel-the-sql-manager-for-ruby.html)
* [Creating Advanced Active Record DB Queries with Arel](https://www.cloudbees.com/blog/creating-advanced-active-record-db-queries-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:_
```ruby
# 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 :_

```ruby
# in the model:
ransacker :created_at do
  Arel.sql('date(created_at)')
end
```
```erb
in the view:
<%= f.search_field(
  :created_at_date_equals, placeholder: t(:date_format)
  ) %>
...
<%= sort_link(@search, :created_at, default_order: :desc) %>
```

```ruby
# 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.

```ruby
# 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.

```ruby
# 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
```ruby
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
```ruby
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:
```ruby
# in the model:
ransacker :id do
  Arel.sql("to_char(id, '9999999')")
end
```
and the same, using MySQL:
```ruby
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:
```ruby
ransacker :id do
  Arel.sql(
    "regexp_replace(
      to_char(\"#{table_name}\".\"id\", '9999999'), ' ', '', 'g')"
  )
end
```
In the view, for all 3 versions:
```erb
<%= 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):_
```ruby
# 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`:
```ruby

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:_

```ruby
# 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:_

```ruby
# 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
```haml
  %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:

```ruby
# 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:
```erb
<%= 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](https://github.com/shioyama/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](https://github.com/shioyama/mobility) called `name` with the value `{'en': "Hello", 'es': "Hola"}`.

```ruby
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!