How to pass numbers in an array into a SQL statement

Question

So I am attempting to do a SQL query where I need to provide a lot of user id's.

Currently what I am trying to do is this:

following_ids = $redis.smembers(self.redis_key(:following))
Medium.includes([{comments: :user}, :likes, :user]).where("user_id IN (#{ following_ids.each { |id| id } }) OR user_id = :user_id", user_id: id)

Now the following_ids is in this form: ["1", "2", "3"], containing user id's

But the loop I try to do seems to still put the whole array into the SQL statement. I can see this from my server log:

Medium Load (0.3ms)  SELECT "media".* FROM "media" WHERE (user_id IN (["2", "3"]) OR user_id = 1)
PG::SyntaxError: ERROR:  syntax error at or near "["
LINE 1: SELECT "media".* FROM "media" WHERE (user_id IN (["2", "3"])...

What am I doing wrong here?

Yes. It should look something like this: IN (id1, id2, id3... idx), so without the [] brackets — inimene
– inimene, Commented Dec 30, 2014 at 22:52

xlembouras · Accepted Answer · 2014-12-30 23:17:35Z

2

You don't need to complicate things that much. You could simply do

user_ids = $redis.smembers(self.redis_key(:following)) << id
Medium.includes([{comments: :user}, :likes, :user]).where(user_id: user_ids)

answered Dec 30, 2014 at 23:17

xlembouras

8,3254 gold badges35 silver badges44 bronze badges

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

1 Comment

inimene Over a year ago

That is an even better approach. Thank you!

davetakahashi · Accepted Answer · 2014-12-30 23:09:25Z

1

Try changing the interpolated code to:

following_ids.to_s.tr("[]", "")

So in total:

Medium.includes([{comments: :user}, :likes, :user]).where("user_id IN ( #{following_ids.to_s.tr("[]", "") }) OR user_id = :user_id", user_id: id)

UPDATE: See Kaspar's comment below for the preferred 'active record way' to do this.

edited Dec 30, 2014 at 23:09

answered Dec 30, 2014 at 22:56

davetakahashi

4941 gold badge4 silver badges12 bronze badges

2 Comments

inimene Over a year ago

Seems to work well, meanwhile found out that this is possible too actually: Medium.includes([{comments: :user}, :likes, :user]).where("user_id IN (?) OR user_id = ?", following_ids, id). Will see which one I shall use. Thank you for the help!

davetakahashi Over a year ago

@Kaspar Oh yeah totally. That is actually the preferred way to do it! I should have thought of that. Will edit my answer to refer to your comment.

Collectives™ on Stack Overflow

How to pass numbers in an array into a SQL statement

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related