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.
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).
@followed.join(","). That might give you problems if @followed is a blank array. To solve that you could write NOT IN (0#{@followed.join(',')}), resulting in NOT IN (0) if the array is blank.
User.where("id NOT IN (?)", @followed). This uses the built-in escaping functions and ensures that there are not even accidental SQL injections.you can do something like:
@followed = [11,3,24,42]
User.where('id not in (?)', @followed)
User.joins('INNER JOIN ...').where('...').group('').limit(30).order('order_index DESC'). Maybe you should take a look in the tutorial and try to find what is the most proper for you