Gonzalo Pincheira Arancibia Gonzalo Pincheira Arancibia - 1 year ago 144
SQL Question

Ruby on Rails: Multiple conditions in rails based in different fields using like

I have a entity in my database that have multiple fields.
I need search in this entity based if a certain input it's contained in certain fields, by example

Entity Car

  • model_name

  • owner

I want search like this (a pseudo sql idea)

Select * FROM Car WHERE (LIKE model_name = "%maz%" OR LIKE owner = "%maz%")

I've tried with the find method but it's not work

searched_string = "%maz%"
Car.find(:all, :conditions => {model_name: searched_string, owner: searched_string})

How to achieve this?

Answer Source

When using the conditions option in an ActiveRecord query, the resulting SQL will be generated for an exact match. Additionally, each condition you add will be joined using an AND, not an OR.

That being said, the SQL generated by your query above likely looks like this:

SELECT * FROM Car WHERE (model_name = "%maz%" AND owner = "%maz%")

To get the query you are looking for, you'll have to hand-write the SQL for the WHERE clause.

Car.where("model_name ILIKE :q OR owner ILIKE :q", q: "%maz%")

NOTE: I'm using the more modern ActiveRecord syntax above.

Some good news is that Rails 5 will include support for "or" queries.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download