如何在sql server查询中使用ruby数组？

I am having trouble in passing ruby array in sql query. I am using ruby version 2 and rails 4.0.2.

我在sql查询中传递ruby数组时遇到了麻烦。我使用ruby版本2和rails 4.0.2。

i have a array which contains project names which is passed by the view page and i am reading it through params in my function in the controller.

我有一个数组,其中包含由视图页面传递的项目名称,我通过控制器中的函数中的参数读取它。

This is my function :

这是我的功能:

def fetch_projects()

@projects = params[:projects]

#this is my sql query

@query = " select * from project_view where Projects in '#{@projects.map { |s| s }}'"

@results = ActiveRecord::Base.connection.select_all(@query)

end

I am not able to execute the @query. Thanks in advance

我无法执行@query。提前致谢

2 个解决方案

#1

Avoid writing raw sql. You are already using a gem called active record that does much more than just running your queries(it keeps it safe to execute & optimizes your queries) and without the hassel of innitiating your connection. You can instead do

避免编写原始sql。您已经在使用一个名为活动记录的gem,它不仅仅是运行您的查询(它可以保证您执行和优化查询的安全性)并且不需要创建连接。你可以这样做

ProjectView.where(project: @projects)

#2

@query = " select * from project_view where Projects in '#{@projects.map { |s| s }}'"

I can see two problems here,

我在这里可以看到两个问题,

String interpolation does not work with single quetoes. So use #{} directly in the query string.

字符串插值不适用于单个quetoes。所以直接在查询字符串中使用#{}。

And your ruby code within string will return Array [1,2,3], so the query fails.

并且字符串中的ruby代码将返回Array [1,2,3],因此查询失败。

Why don't you try this in Rails way, For ex: ProjectView.joins(:projects).where(projects: {id: @projects.pluck(:id)})

你为什么不用Rails方式尝试这个,例如:ProjectView.joins(:projects).where(projects:{id:@ projects.pluck(:id)})

#1

ProjectView.where(project: @projects)

#2

@query = " select * from project_view where Projects in '#{@projects.map { |s| s }}'"