Make OR with ransack

Question

The main quest, but I could not find something clearly on the page or 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 request should look like this:

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

Any help would be greatly appreciated. thanks in advance

Answer 1

Try the following:

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

Answer 2

This may not be the exact answer to your question, but because I searched the right way to perform OR searches in Ransack myself and did not find a good answer, but I was able to solve the problem myself, I thought that I would share the solution.

In the application I'm working on, there is a search page that takes various fields of the Customer model (associated with the DB customers table) as parameters, and then lists the table of these customers that corresponds to the search result, Customers have three different phone numbers, and The previous search page had a different search field for each type of room. I wanted to combine them into one field, which would be convenient to search in all rooms.

There were three field definitions in the database (in these fragments I just 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 the following:

 <%= 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 applies to this point, but the contents of the config/initializers/ransack.rb :

 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 default eq , cont , etc., I can use my custom predicate spaces_match_anything in searches. The predicate does what it says.)

In any case, taking inspiration from the same example as you, I added the following ransacker for the app/models/customer.rb model:

 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 search fields of the three phone numbers on the search page as follows:

 <%= 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 %> 

The number that the user enters in this search field will now match any of the three customer numbers. (Note the protection against the null number, ||"" , in ransacker.)

This has been 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, even if the place is entered in the right place, and even if in the search code field I replace _cont with _spaces_match_anything .

Answer 3

drop it under the search_form_for tag. It is assumed that you use f:

 <%= f.combinator_select %> 

it will generate a selection with two parameters. ALL or ANYTHING. ANYONE will use the OR clause. ALL will use the AND clause.

Answer 4

After digging into the Ransack demo , I did it as follows:

 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))" 

Yes, this is rude and certainly not the best way to do this, but it temporarily saved me. Any other thoughts?