CODEWITHSUNDEEP
ruby-on-railsactiverecord
Gandalf StormCrow
Gandalf StormCrow

Reputation: 26212

Multiple like conditions in active record

I need to check my records against multiple like statements, what is the best way to do so? i.e. 

names = ['alice', 'bob', 'mark']

I've got table lets say People in my database or (Peoples) so I want to select all people who have names such as those 3 above.

found this answer :

https://stackoverflow.com/a/5333281/169277

For using like but I couldn't figure out how to pass array of values instead of single values. any ideas?

UPDATE :

I need like because people have two names

Upvotes: 3

Views: 4399

Answers (3)

Praveen Venkatapuram
Praveen Venkatapuram

Reputation: 89

This will work.
Person.where('name SIMILAR TO ?',"(alice|bob|mark)")

Advanced matching can also be done
Person.where('name SIMILAR TO ?',"%(alice|bob|mark)%")
Above line will also work if name is "jon alice" i.e regex matching.

Refer postgresql documentation : http://www.postgresql.org/docs/9.0/static/functions-matching.html

Upvotes: 0

Pritesh Jain
Pritesh Jain

Reputation: 9146

Try this

names = ['alice', 'bob', 'mark']
Person.where('name REGEXP ?',names.join('|'))

While matching like with multiple values on single column you can use REGEXP instead of LIKE this will generate a sql statement with in clause

to check the sql generated just add .to_sql

Person.where('name REGEXP ?',names.join('|')).to_sql

Upvotes: 3

Dan Reedy
Dan Reedy

Reputation: 1857

There are a number of plugins to that provide for this kind of query, but all you really need is Arel.

You'll need to prepare the query parameters for LIKE using something like this

wildcard_names = names.collect { |name| "%#{name}%" } # => ["%alice%","%bob%","%mark%"]

Then you'd used the #matches_any method of Arel.

people_table = People.arel_table
People.where(people_table[:name].matches_any(wildcard_names))

As PriteshJ mentioned, add to_sql to confirm the query is correct.

Upvotes: 4

Related Questions