Rails 3 / Ruby: ActiveRecord Find method IN condition Array to Parameters single quote issue

Question

I created a method on a micropost model which takes an array of user_ids. In this method I use the following 'find' method to pull all posts for all of the users within the array.

find(:all, :conditions => ["user_id IN (?)", args.join(',')])

But when ActiveRecord generates the SQL for this query it surrounds the coma delimited list of Ids in single quotes. This causes the query to only pull posts for the first number within the single quotes instead of all the numbers.

SELECT `microposts`.* FROM `microposts` WHERE (user_id IN ('3,4,5')) ORDER BY microposts.created_at DESC

The query should look like this to work correctly but I can't figure out how to get Ruby to convert the Array to a coma delimited list without the quotes, I know it has to be something simple and I am just missing it and can't find anything on Google that fixes this. All the posts I have found use the same .join(',') method to concert the Array.

SELECT `microposts`.* FROM `microposts` WHERE (user_id IN (3,4,5)) ORDER BY microposts.created_at DESC

One more fact which migh assist you in figuring out my issue is the Array of ids which I am passing into the method is being built in the following manner. Not sure if this would affect things.

ids = Array.new
ids.push(current_user.id)
ids = ids + current_user.friends.map(&:id)
ids = ids + current_user.reverse_friends.map(&:id)

Thanks in advance to the one who can help me get back to work ;)

hayesgm · Accepted Answer · 2016-02-19 07:37:19Z

73

Answer updated (twice) to reflect Rails 3+ ActiveRecord syntax

Rails 3+

You can use the following syntax:

Post.where(user_id: [1,2,3])

And thus:

Post.where(user_id: args)

Rails 3 or Rails 4 (Original)

For completeness, the Rails 3 syntax would be:

Post.where("user_id IN (?)", [1,2,3]).to_a

If args is an Array, then:

Post.where("user_id IN (?)", args).to_a

Or, if args is an array of strings:

Post.where("user_id IN (?)", args.map(&:to_i)).to_a

The to_a is optional, but will return the results as an array. Hope this helps.

Rails 2

You should be able to simply pass in an array as the parameter, as follows:

find(:all, :conditions => ["user_id IN (?)", [1,2,3] ] )

Thus, if args is an array of ids, you should simply call:

find(:all, :conditions => ["user_id IN (?)", args ] )

In case you have an issues with types, you can do:

find(:all, :conditions => ["user_id IN (?)", args.map { |v| v.to_i } ] )

and this will ensure each item in the array is an Integer.

edited Feb 19, 2016 at 7:37

answered Aug 9, 2011 at 9:41

hayesgm

9,2362 gold badges40 silver badges41 bronze badges

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

6 Comments

Schleichermann Over a year ago

when using results = find(:all, :conditions => ["user_id IN (?)", args]) the SQL looks as follows SELECT microposts.* FROM microposts WHERE (user_id IN ('---\n- 3\n- 4\n- 5\n')) ORDER BY microposts.created_at DESC

hayesgm Over a year ago

Try using my third query. I think it'll help.

Schleichermann Over a year ago

used the third query and I think it is moving us in the right direction. I then got the error 'undefined method `to_i' for [3, 4, 5]:Array' which lead me to output the array that I am passing in and the inspect on the array is as follows: [[3, 4, 5]]. This leads me to believe that the way I am trying to concatenate the arrays is actually creating an array of arrays rather than a single array. Can you take a look at that method and see if you can tell me what I am doing wrong?

hayesgm Over a year ago

As a quick mention: passing args[0] should work :) To solve the deeper issue, how are you getting from ids to args. You are somehow forming a new array in that process. The ids array should be sound.

Schleichermann Over a year ago

Yup, it was something really simple and easily overlooked. The method containing the Find query was accepting an *args parameter which I in turn was using as the passed in Array of ids. But of course it is an Array of Arrays. So I switched the method from accepting *args and just accepting an array of Ids. Total nub mistake. I really appreciate the help debugging my code, sometimes you can't see the forest through the trees ;)

|

sq1020 · Accepted Answer · 2014-01-31 23:14:48Z

20

In rails 3 and 4, you could also simply pass the array as an argument like so:

Post.where(:user_id => [1,2,3])

And that will give you the same result as

Post.where("user_id IN (?)", [1,2,3])

answered Jan 31, 2014 at 23:14

sq1020

1,0902 gold badges12 silver badges15 bronze badges

1 Comment

djb Over a year ago

The first example is good for handling possible nil values as well. e.g. Post.where(:user_id => [1,2,nil])

Collectives™ on Stack Overflow

Rails 3 / Ruby: ActiveRecord Find method IN condition Array to Parameters single quote issue

2 Answers 2

Rails 3+

Rails 3 or Rails 4 (Original)

Rails 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Rails 3+

Rails 3 or Rails 4 (Original)

Rails 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related