Reputation: 42207
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
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
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