CODEWITHSUNDEEP
rubyactiverecord
peter
peter

Reputation: 42207

multiple where statements combined with OR in Activerecord and Ruby

I would like to do a query with activerecord (not rails) with multiple keywords that are contained in a field (so I have to use LIKE) but I don't know in advance how many keywords there will be.

My query looks like this, Word is my model.

query = ['word1','word2'] #could be more
puts "searching for #{query}"
qwords = Word.none
query.each do |qword|
  puts qwords.where("word like ?", "%#{qword}%").to_sql
  qwords = qwords.where("word like ?", "%#{qword}%")
end

Which gives nothing because the queries are added as AND but I need OR.

searching for ["word1", "word2"]
SELECT "words".* FROM "words"  WHERE (word like '%word1%')
SELECT "words".* FROM "words"  WHERE (word like '%word1%') AND (word like '%word2%')
#<ActiveRecord::Relation []>

I can't use Word.where(word: query) which uses the sql IN keyword because that only works for exact matches.

Is there a solution that doesn't involves concatenating the whole SQL that is needed ?

Upvotes: 0

Views: 954

Answers (2)

peter
peter

Reputation: 42207

Had forgotten about this question and found a solution myself afterward. I now do the following. The problem was caused by using the resultset to do my next query on while like this it is on the whole recordset and the results are added.

@qwords = Word.none
  $query.each do |qword|
    @qwords += Word.where(word: qword)
  end

Upvotes: 1

Travis Smith
Travis Smith

Reputation: 632

query = "word1 word2" #could be more
puts "searching for #{query}"
query_length = query.split.length #calculates number of words in query

Now you can put together the number of SQL queries you need regardless of the number of keywords in your query

Word.where([(['word LIKE ?'] * query_length).join(' OR ')] + (query.split.map {|query| "%#{query}%"}))

This should return

["word LIKE ? OR word LIKE ?", "%word1%", "%word2%"]

for your SQL search

Upvotes: 1

Related Questions