CODEWITHSUNDEEP
mysqlruby-on-railsruby
lokesh
lokesh

Reputation: 47

Need to convert an array to Integer for a MySQL query in a Rails API

I have a piece of code which fetches the list of ids of users I follow.

@followed = current_user.followed_user_ids

It gives me a result like this [11,3,24,42]

I need to add these to NOT IN mysql query. Currently, I am getting NOT IN ([11,3,24,42]) which is throwing an error. I need NOT IN (11,3,24,42)

This is a part of a find_by_sql statement, so using where.not is not possible for me in this point.

Upvotes: 0

Views: 206

Answers (2)

Fer
Fer

Reputation: 3347

In rails 4:

@followed = current_user.followed_user_ids # @followed = [11,3,24,42]
@not_followed = User.where.not(id: @followed)

This should generate something like select * from users where id not in (11,3,24,42)

As you comment, you are using find_by_slq (and that is available in all rails versions). Then you could use the join method:

query = "select * from users where id not in (#{@followed.join(',')})"

This would raise mysql errors if @followed is blank, the resulting query would be

select * from users where id not in ()

To solve this whiout specifiying aditional if statements to your code, you can use:

query = "select * from users where id not in (0#{@followed.join(',')})"

Your normal queries would be like:

select * from users where id not in (01,2,3,4)

but if the array is blank then would result in 

select * from users where id not in (0)

which is a still valid sql statement and is delivering no results (which might be the expected situation in your scenario).

Upvotes: 3

Marko Krstic
Marko Krstic

Reputation: 1447

you can do something like:

@followed = [11,3,24,42] 
User.where('id not in (?)', @followed)

Upvotes: 1

Related Questions