我在数组上有如下所示的数组,
skills = ['ruby','Ruby on Rails'];
我正在尝试在 mysql 中传递数组,条件如下
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(', ')})")
但是它不起作用,怎么能将数组传递到哪里 In 条件。
我收到错误
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)
你正在将数组的字符串表示传递给MySQL,这不起作用。您需要将数组中的值插入到查询中。这可以通过转义技能并加入它们来完成:
skills.map { |s| "'#{s}'" }.join(', ')
这将产生 'ruby', 'Ruby on Rails'
,这是 IN 语句的有效参数。
然而,更好的方法是根本不编写原始SQL,而是依靠ActiveRecord来生成它。这是更易于维护和可读的方法。
Question.joins(:quest_answers).where(category: skills)
将数组传递给where
会自动将其转换为子集条件。