how generate LIKE on arrays of values in where clause of sql query with rails?

Question

I have an array of value :

words = ['foo', 'bar', 'baz']

I want autogenerate a where clause with LIKE (and not "IN").

What I do for now :

words = params[:content].split(' ').map { |w| "%#{w.strip}%" }

where = []
words.size.times do
  where << 'name LIKE ?'
end

tags = Tag.where(where.join(' OR '), *words)

the correct request SQL is generate :

SELECT `tags`.* FROM `tags` WHERE (name LIKE '%foo%' OR name LIKE '%bar%' OR name LIKE '%baz%')

but it's not realy nice way...

when I want compare array values with equals, we can just do :

 Tag.where(name: words)

There is a possibility to do same thing but not generate IN, but multiple OR LIKE "%VALUE%" ? How?

Laurens · Accepted Answer · 2017-05-05 17:23:52Z

11

In postgresql it works like this:

Tag.where("name iLIKE ANY ( array[?] )", words)

SQL RLIKE (REGEX)

Tag.where("name RLIKE ?", words.join("|"))

SQL select (not very efficient):

Tag.select{ |c| c.name =~ Regexp.new(words.join("|"), true) }

As a scope in Tag.rb (SQL)

scope :ilike_any, -> (words) {  where("name RLIKE ?", words.join("|")) }

That enables you to do:

words = %w(word1 word2 word3)
Tag.ilike_any(words)

edited May 5, 2017 at 17:23

answered Dec 15, 2015 at 18:22

Laurens

2,41023 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matrix Over a year ago

There is a solution to RLIKE multiple properties like "name" and "description" for exemple, in same time?

Collectives™ on Stack Overflow

how generate LIKE on arrays of values in where clause of sql query with rails?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related