ActiveRecord Use An array of arrays As Parameters

Question

I would like to form the following query:

SELECT tracked_points.* 
FROM tracked_points 
WHERE
(id = 87 AND zone_id = 457)
OR
(id = 88 AND zone_id = 457)

My input to the function will be a nested array of [id, zone_id], something like:

[[87, 457], [88, 457]]

I tried a little trick I saw on the squeel gem site

arr = [[87, 457], [88, 457]]
TrackedPoint.where((["(id = ? AND zone_id = ?)"] * arr.size).join(" OR "), *arr)

but it doesn't like taking a nested array as an argument.

The solution I came up with just compiles a SQL string, but doesn't use any parameter sanitation of prepared statements:

matches = arr.map do |i|
  "(id = #{i[0]} AND zone_id = #{i[1]})"

TrackedPoint.where(matches.join(" OR ")

I have a feeling an answer is possible with squeel, but if there is a pure ActiveRecord answer I'm game to try that out as well.

ActiveRecord is rather limited to create complex queries. Without any external add-ons like squeel I think You won't do any better than generating this string. Even if You will find some interesting better way doing it in SQL, You will still generate sequel string. — Edgars Jekabsons, Oct 10 '13 at 16:03
@EdgarsJekabsons Any idea how I might add parameter sanitizing to this approach? — Tyler DeWitt, Oct 10 '13 at 16:04
Check out this method: http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/Quoting.html#method-i-quote I thin it should be available on connection object, so You should be able to access it like this: `TrackedPoint.connection.quote`. — Edgars Jekabsons, Oct 10 '13 at 16:09
@EdgarsJekabsons I can't find any examples of the syntax on how to use `quote`. Do you know how it is used? — Tyler DeWitt, Oct 10 '13 at 17:03

score 0 · Accepted Answer · answered Oct 17 '13 at 13:31

0

You have to flatten the nested array first.

TrackedPoint.where((["(id = ? AND zone_id = ?)"] * arr.size).join(" OR "), *(arr.flatten))

answered Oct 17 '13 at 13:31

Tyler DeWitt

1 Answers1