Make an OR with ransack

Question

A basic questión, but I couldn't find something clear in the project page or the wiki. I have the following code:

field = "secre"
Position.search( {:description_cont => field, :code_cont => field}).result(:distinct => true).to_sql
 => "SELECT DISTINCT `positions`.* FROM `positions`  WHERE ((`positions`.`description` LIKE '%secre%' AND `positions`.`code` LIKE 0))"

But my query should be like:

 => "SELECT DISTINCT `positions`.* FROM `positions`  WHERE ((`positions`.`description` LIKE '%secre%' OR `positions`.`code` LIKE 0))"

Any help would be appreciated. Thanks in advance

Hamper

Have you tried using brackets [] instead of the hash {} syntax? – Skate

@Skate The method doesn't accept arrays – Hamper

Answer 1

Try the following:

Position.search( {:description_or_code_cont => field}).result(:distinct => true).to_sql

Unpolled

+1 This is the answer to the original question. Also, for doing OR on a has_x relationship's attributes, the syntax wasn't immediately obvious, so I'm putting this here for posterity. Assuming a Teacher has_one Syllabus that has attributes foo and bar: Teacher.search( :syllabus_foo_or_syllabus_bar_eq => "val" ) – Heteronym

Answer 2

This might not be an exact answer to your question, but because I googled around for the correct way to do OR searches in Ransack myself, and didn't find a good answer, but did manage to solve the problem myself, I thought I'd share the solution.

In an application I'm working on, there's a search page that takes as parameters the various fields of model Customer (linked to DB table customers), and then lists a table of those customers match the search result. The customers have three different phone number fields, and the search page previously had a different search field for each type of numbers. I wanted to combine them into one field that would conveniently search within all the numbers.

The database had these three field definitions (in these snippets, I've only changed some identifier names):

mysql> desc customers;
+--------------------------+------------------+------+-----+---------+----------------+
| Field                    | Type             | Null | Key | Default | Extra          |
+--------------------------+------------------+------+-----+---------+----------------+
...
| customer_phone_a         | varchar(50)      | YES  |     | NULL    |                |
| customer_phone_b         | varchar(50)      | YES  |     | NULL    |                |
| customer_phone_c         | varchar(50)      | YES  |     | NULL    |                |
+--------------------------+------------------+------+-----+---------+----------------+

Previously, the search page (file app/views/customers/index.html.erb) contained this:

<%= search_form_for @search do |f| %> <!-- this is a Ransack-provided form -->
  <div class="field">
...
    <%= f.label :customer_phone_a_spaces_match_anything, "Phone number A is or contains:" %>
    <%= f.text_field :customer_phone_a_spaces_match_anything %>

    <%= f.label :customer_phone_b_spaces_match_anything, "Phone number B is or contains:" %>
    <%= f.text_field :customer_phone_b_spaces_match_anything %>

    <%= f.label :customer_phone_c_spaces_match_anything, "Phone number C is or contains:" %>
    <%= f.text_field :customer_phone_c_spaces_match_anything %>
...
  </div>

  <div class="actions">
    <%= f.submit "Search", class: "btn btn-large btn-primary" %>
  </div>

<% end %> <!-- search_form_for -->

(This is beside the present point, but the contents of file config/initializers/ransack.rb are:

Ransack.configure do |config|

  config.add_predicate 'spaces_match_anything',
  :arel_predicate => 'matches', # so we can use the SQL wildcard "%"
  # Format the incoming value: add the SQL wildcard character "%" to the beginning and the end of
  # the string, replace spaces by "%", and replace multiple occurrences of "%" by a single "%".
  :formatter => proc {|v| ("%"+v.gsub(" ", "%")+"%").squeeze("%")}

end

This means that in addition to Ransack's default eq, cont, etc., I can use my custom predicate spaces_match_anything in searches. The predicate does what it says.)

Anyway, taking inspiration from the same example you did, I added the following ransacker to model app/models/customer.rb:

ransacker :all_phones do |parent|
  Arel::Nodes::InfixOperation.new('||',
    Arel::Nodes::InfixOperation.new('||',
      Arel::Nodes::InfixOperation.new('||', parent.table[:customer_phone_a]||"", ' '),
                                    parent.table[:customer_phone_b]||"", ' '),
                                  parent.table[:customer_phone_c]||"")
end

Finally, I replaced the three phone number search fields in the search page by this:

<%= f.label :customer_phone_a_or_customer_phone_b_or_customer_phone_c_cont, "Phone number is or contains:" %>
<%= f.text_field :customer_phone_a_or_customer_phone_b_or_customer_phone_c_cont %>

A number that a user inputs in this search field will now match any of the three numbers of a customer. (Notice the guard against null number, ||"", in the ransacker.)

This is tested to work with Ruby v1.9.3 and Rails v3.2.8. The input parameter will not match the end of one number and the beginning of the next one, not even if a space is input in the right place, and not even if in the search field code, I replace _cont by _spaces_match_anything.

Lorelle

Teemu, your answer saved me after hours of trying to get a similar thing to work!!!! Thanks! – Bouse

Answer 3

throw this under your search_form_for tag. This is assuming that you're using f:

<%= f.combinator_select %>

it'll generate a select with two options. ALL or ANY. ANY will use an OR clause. ALL will use an AND clause.

Machree

Answer 4

for now we can use or-query pretty simple with ransack:

Position.ransack(description: 'secret').result.or(Position.ransack(code: 0).result)

Coati

I'm glad things have improved the last 7 years for ransack :) – Hamper

Answer 5

After digging for a while into the Ransack demo code, I did it as follow:

field = "secre"
q= {
  "m"=>"or", 
  "c"=>{
    "0"=>{
      "a"=>{
        "0"=>{
          "name"=>"description"
        }
      }, 
      "p"=>"cont", 
      "v"=>{
        "0"=>{
          "value"=>field
        }
      }
    }, 
    "1"=>{
      "a"=>{
        "0"=>{
          "name"=>"code"
        }
      }, 
      "p"=>"cont", 
      "v"=>{
        "0"=>{
          "value"=>field
        }
      }
    }
  }
}
Position.search(q).result(:distinct => true).to_sql
 => "SELECT DISTINCT `positions`.* FROM `positions`  WHERE ((`positions`.`description` LIKE '%secre%' OR `positions`.`code` LIKE 0))"

Yeah, it's gross and for sure it shouldn't be the best way to do it, but it saved me temporarily. Any other thoughts?

Hamper