How to use ruby array in sql server query?

Question

I am having trouble in passing ruby array in sql query. I am using ruby version 2 and 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

My two cents: It isn't best practices to use raw sql code or reference ActiveRecord::Base directly in a controller. It violates MVC and will make your code difficult to maintain by anyone else. — Elvn
– Elvn, Commented Jul 4, 2015 at 14:12

David Ferd · Accepted Answer · 2015-07-06 11:16:07Z

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

ProjectView.where(project: @projects)

edited Jul 6, 2015 at 11:16

answered Jul 4, 2015 at 11:21

David Ferd

572 bronze badges

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

6 Comments

Priyanka Chabbi Over a year ago

Using Model helped me at some point , thanks :) . But how do i pass an array of projects in the above code of yours . Other then using .each method is there any other way ?

David Ferd Over a year ago

Its Rails syntactic sugar that does that for you..if I understood you correctly the above query is your answer. which would generate a sql query like this 'SELECT * FROM project_view WHERE project_id IN (1,2)'

David Ferd Over a year ago

Assuming your @projects is an array of models rails will automatically generate the array of ids to match by

Priyanka Chabbi Over a year ago

Hey David Ferd how do i fetch for array of values ? if my @projects contains more then one project name

David Ferd Over a year ago

from your initial query what is the datatype of the field 'Projects'?

|

Venkat Ch · Accepted Answer · 2015-07-04 08:48:09Z

0

@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.
And your ruby code within string will return Array [1,2,3], so the query fails.

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

answered Jul 4, 2015 at 8:48

Venkat Ch

1,2784 gold badges18 silver badges38 bronze badges

1 Comment

limekin Over a year ago

The whole string is enclosed with double quotes, single quotes are just a part of the normal characters in it.

Collectives™ on Stack Overflow

How to use ruby array in sql server query?

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related