Lets say of I have this query SELECT * FROM table WHERE id IN (1,2,3,4);
In my current use case my ids are in an array like so [1,2,3,4]
How could I do the same query using the array data structure?
i.e. SELECT * FROM table WHERE id IN (myarray);
Edit: this is in ruby :)
Try this:
SELECT * FROM table WHERE id IN (myarray.map {|i| "\"#{i}\""}.join(", "));
You could convert the array to a comma separated string using the myarray.join(',') method.
The final code would look like this:
query = "SELECT * FROM table WHERE id IN (#{myarray.join(',')})"
You might run into some problems if the array values are strings, but it works just fine for integers.
User.where(id: [1, 2, 3, 4, nil]).to_sql
# SELECT "users".* FROM "users"
# WHERE ("users"."id" IN (1, 2, 3, 4) OR "users"."id" IS NULL)
or, if you can't/don't want to use where, you can drop into Arel to get just the IN string:
User.arel_table[:id].in([1,2,3,4]).to_sql
# => "users"."id" IN (1, 2, 3, 4)
though with this, you don't automatically get that nifty nil handling. If you don't have an ActiveRecord Model, but just using ActiveRecord::Base to execute queries in your database (as mentioned in the comments) you can do:
table = Arel::Table.new(:table) # :table is the name of the table in db
table[:id].in([1,2,3,4]).to_sql
# => "table"."id" IN (1, 2, 3, 4)
table.where(table[:id].in([1,2,3,4])).project(Arel.sql('*')).to_sql
# => SELECT * FROM "table" WHERE "table"."id" IN (1, 2, 3, 4)
And, avoiding Arel/ActiveRecord as much as possible, you can just do
ActiveRecord::Base.send(:sanitize_sql, ['id in (?)', [1,2,3,4]])
# => "id in (1,2,3,4)"
arel solution which is probably the must under leveraged library in all of rails (from a developer perspective not an internals perspective it works hard internally)