How to pass rails array mysql Where IN condition

Question

I have on array like below,

 skills = ['ruby','Ruby on Rails'];

I am trying to pass array in mysql where condition like below

 questions =  MysqlConnection.connection.select_all("
                   SELECT questions.*,quest_answers.* FROM `questions` 
                   INNER JOIN `quest_answers` ON `quest_answers`.`question_id` = 
                  `questions`.`id` where questions.category IN (#{skills.join(', ')})")

But it did not work,How can pass an array to where In condition.

Error I am getting

  Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on rails,  Ruby)' at line 1:  SELECT questions.*,quest_answers.* FROM `questions` INNER JOIN `quest_answers` ON `quest_answers`.`question_id` = `questions`.`id` where questions.category IN (Ruby on rails,  Ruby)

Why don't you use ActiveRecord?

Marek Lipka
– Marek Lipka

2019-01-14 10:29:34 +00:00
Commented Jan 14, 2019 at 10:29 — Marek Lipka
– Marek Lipka, Commented Jan 14, 2019 at 10:29

jdno · Accepted Answer · 2019-01-14 10:47:51Z

2

You are passing a string representation of the array to MySQL, which doesn't work. You need to insert the values in the array into the query. This can be done by escaping the skills, and joining them:

skills.map { |s| "'#{s}'" }.join(', ')

This produces 'ruby', 'Ruby on Rails', which is a valid argument for the IN statement.

A better approach however is to not write the raw SQL at all, but rely on ActiveRecord to generate it. This is the more maintainable and readable approach.

Question.joins(:quest_answers).where(category: skills)

Passing an array to where converts it automatically into a subset condition.

answered Jan 14, 2019 at 10:47

jdno

4,3841 gold badge25 silver badges27 bronze badges

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

5 Comments

Manjunath C Over a year ago

Hi jdno, Your answer solved my problem, Thank you very much.

jdno Over a year ago

What approach did you take? Raw SQL or ActiveRecord?

Manjunath C Over a year ago

First I tried with ActiveRecord but it was giving data from only first table, so I took raw SQL approach. Thank you

engineersmnky Over a year ago

@ManjunathC using ActiveRecord can give you both sets of data using includes(:quest_answers).references(:quest_answers) this will execute essentially the same as your raw sql but will also convert the return values to true AR objects.

Manjunath C Over a year ago

Okay @engineersmnky thanks for your suggestion,I will try this.

Collectives™ on Stack Overflow

How to pass rails array mysql Where IN condition

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related