1

I need to take an array and use it for a MySQL query.

I tried looking for methods but all of them seem to be related to PHP arrays and not Ruby.

For example, I have terms = ['genetics', 'elderly', 'health'] and I want to use:

con.query "SELECT col1 FROM data1 WHERE MATCH (col2) AGAINST (terms)"

Is this possible?

the Tin Man
  • 158,662
  • 42
  • 215
  • 303
howluocanyougo
  • 13
  • 4

2 Answers2

0

You can just join your terms in your against clause:

terms = ['genetics' , 'elderly', 'health']
con.query "SELECT col1 FROM data1 WHERE MATCH col2 AGAINST ('#{terms.join(' ')}')"

Note that using match/against will almost certainly be more performative than using a series of like clauses. See this StackOverflow answer for more information: Which SQL query is better, MATCH AGAINST or LIKE?.

Check out the MySQL documentation for more information on full text searching (including possible operators): http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html.

Community
  • 1
  • 1
Jacob Brown
  • 7,221
  • 4
  • 30
  • 50
  • Thanks for the suggestion! I did try this but it returned nothing. If it helps, the col2 is text with each row having a paragraph, not just one word. I'm not sure if that is why this is so troublesome I was able to get some results, but only from this: terms = ['genetics'] _terms = terms.map{|x| "'" + x + "'"}.join(',') rs= con.query "SELECT col1 FROM data1 WHERE MATCH col2 against (#{_terms})" as you can see, I can only get a result when the array has one element... – howluocanyougo Jul 02 '13 at 02:29
  • It's not necessary to use `` tags if you need to change your answer. Stack Overflow maintains a history of your edits so we'd be able to see the changes if need-be. – the Tin Man Jul 02 '13 at 14:42
0

I'd highly recommend looking into an ORM, such as Sequel, which makes it very easy to generate the proper query in a DBM independent way.

It allows us to use arrays and hashes conveniently. Here's an example using an array to generate the "where" clause in SQL:

my_posts = posts.where(:category => ['ruby', 'postgres', 'linux'])
# WHERE category IN ('ruby', 'postgres', 'linux')

That particular example is part of the "Filtering Records" section.

In a comment, the OP said:

col2 is text with each row having a paragraph, not just one word.

Then you want a LIKE or regex clause that allows each word to be tested. See the "String search functions" section of "Dataset Filtering " for how Sequel allows you to search inside strings.

The code would be something like:

data1.select(:col1).where(Sequel.like(:col2, terms.map{ |t| "%#{ t }%" } ))

which would generate something like:

SELECT col1 FROM data1 WHERE ((col2 LIKE '%genetics%') OR (col2 LIKE '%elderly%') OR (col2 LIKE '%health%'))
the Tin Man
  • 158,662
  • 42
  • 215
  • 303
  • Check out @howluocanyougo's comment on my answer, which suggests that s/he wants to do a full index search, not a `where/in` query. My initial answer, which you deleted, said pretty much the same thing you say above (using AR instead of Sequel). – Jacob Brown Jul 02 '13 at 15:23
  • Your original answer is still there. You can rollback to it by clicking on the "edited" link. At that point I'd recommend merging the original answer with your updated answer to show both, along with a quote of the OP's comment to clarify why you added the new content. – the Tin Man Jul 02 '13 at 15:32
  • thanks for the suggestion! kardeiz actually was correct in what I was looking for but I'll definitely look into Sequel! – howluocanyougo Jul 02 '13 at 18:04