How to transfer data to IN query on rails 3

Question

I am working on rails 3 and sqlite db. Using the IN query. The string variable of the elements is currently being passed to the IN request. But when executing this request, it accepts "so that it does not work." How to overcome this situation?

Here is my code

items = "" items << "#{@invitation_user1.id}" << "," << "#{@invitation_user2.id}" << "," << "#{@user1.id}" << "," << "#{@user2.id}" << "," << "#{@user2.id}" << "," << "#{@profile1.id}" << "," << "#{@profile2.id}" @activities = Version.where("item_id IN (?)","#{items}") 

Tried items.to_i, items.to_s, but did not work. In the magazine I see this.

 SELECT "versions".* FROM "versions" WHERE (item_id IN ('19,20,4,1,1,4,1')) 

But I only need

  SELECT "versions".* FROM "versions" WHERE (item_id IN (19,20,4,1,1,4,1)) 

Answer 1

You can just pass an array. Rails is smart enough to handle it:

 items = [ @invitation_user1.id, @invitation_user2.id, @user1.id, @user2.id, @profile1.id, @profile2.id ] @activities = Version.where("item_id IN (?)", items) # or equivalently: @activities = Version.where(:item_id => items) 

This is very important compared to your version, because Rails correctly handles the escaping of all passed values ​​for the database adapter used.

Answer 2

Use an array instead of a string.

for instance

 ids = [ @invitation_user1.id, @invitation_user2.id, @user1.id, @user2.id ] 

Then you can easily find records

 @versions = Version.find_all_by_id(ids) 

This will result in the expected query.

 SELECT "versions".* FROM "versions" WHERE (item_id IN (19,20,4,1)) 

Answer 3

What you are looking for, split :

 [1] pry(main)> a = '19,20,4,1,1,4,1' => "19,20,4,1,1,4,1" [2] pry(main)> a.split(',') => ["19", "20", "4", "1", "1", "4", "1"] [3] pry(main)> a.split(',').map(&:to_i) => [19, 20, 4, 1, 1, 4, 1] 

But, since you are building the "string" manually, it is better to use an array:

 items = Array.new items << @invitation_user1.id << @invitation_user2.id << @user1.id items << @user2.id << @user2.id << @profile1.id << @profile2.id @activities = Version.where(item_id: items) 

Anyway, the way to get the ids is weird, because ... what happens if you add more users? or profiles?

What would I do (seeing as little as possible in your example)

 items = Array.new items << get_invitation_user_ids items << get_user_ids items << get_profile_ids @activities = Version.where(item_id: items) 

and then define these methods, for example:

 def get_invitation_user_ids InvitationUser.select(:id).map(&:id) end ...