multiple where statements combined with OR in Activerecord and Ruby

Question

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 ?

Forgive my ignorance, but what is Word.none ? I've not seen that before. — Beartech
– Beartech, Commented Apr 3, 2015 at 15:25
Have you seen this page? Looks like a way to do it with .to_sql specifically for an array of terms. coderwall.com/p/dgv7ag/… It also links back to an older SO question that directs the OP to consider AREL. — Beartech
– Beartech, Commented Apr 3, 2015 at 15:32
@Beartech: it's the opposite from .all and use it here so that i can queue the relations together, starting with an empty one, would you lite to make an answer from the Arel solution with my code as example ? — peter
– peter, Commented Apr 3, 2015 at 15:57
Just a thought, what if you have your array of choices as rows in a table? Call it qtab, then you could do something along: qwords.where("word like ? , "qtabs.word"). Does passing it a table.column reference work any different than just an array? I'm thinking there might be something there because it's happening at the SQL level and not the ActiveRecord level? Depends on your DB I would guess. — Beartech
– Beartech, Commented Apr 4, 2015 at 14:24

Travis Smith · Accepted Answer · 2015-05-20 07:24:46Z

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

not bad but take a look at my own solution which is more simple

peter · Accepted Answer · 2015-05-20 08:06:51Z

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

I'm sorry, Do you know how to use this with paging? I'm getting error undefined method page' for #<Array:0x007fd90713caf8>` when using @items.page after this code. I'm new to ruby

Collectives™ on Stack Overflow

multiple where statements combined with OR in Activerecord and Ruby

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related