How to create an IN predicate with multiple columns using ActiveRecord?

Question

I have the following distance table:

╔════╦════════════╦════════════╦═════════════════╦═════════════════╦══════════╗ ║ id ║ origin_lat ║ origin_lng ║ destination_lat ║ destination_lng ║ distance ║ ╠════╬════════════╬════════════╬═════════════════╬═════════════════╬══════════╣ ║ 1 ║ 1.234567 ║ 2.345678 ║ 3.456789 ║ 4.567890 ║ 10 ║ ║ 2 ║ 5.678901 ║ 6.789012 ║ 7.890123 ║ 8.901234 ║ 20 ║ ╚════╩════════════╩════════════╩═════════════════╩═════════════════╩══════════╝ 

Question: how can I create the following SQL query (supported by PostgreSQL) using ActiveRecord and Arel if necessary:

 SELECT * FROM distances WHERE (origin_lat, origin_lng) IN ((1.234567, 2.345678), (5.678901, 6.789012)) AND (destination_lat, destination_lng) IN ((3.456789, 4.567890), (7.890123, 8.901234)); 

I tried this, but it does not work:

 Distance.where('(origin_lat, origin_lng) IN (?) AND (destination_lat, destination_lng) IN (?)', [[1.234567, 2.345678], [5.678901, 6.789012]], [[3.456789, 4.567890], [7.890123, 8.901234]]) 

It generates this:

 SELECT "distances".* FROM "distances" WHERE ((origin_lat, origin_lng) IN ('--- - 1.234567 - 2.345678 ','--- - 5.678901 - 6.789012 ') AND (destination_lat, destination_lng) IN ('--- - 3.456789 - 4.56789 ','--- - 7.890123 - 8.901234 ')) 

And picks up PG::FeatureNotSupported: ERROR: input of anonymous composite types is not implemented

The number of parameters is a variable, so I cannot simply program the query as follows:

 Distance.where('(origin_lat, origin_lng) IN ((?,?),(?,?)) AND (destination_lat, destination_lng) IN ((?,?),(?,?))', 1.234567, 2.345678, 5.678901, 6.789012, 3.456789, 4.567890, 7.890123, 8.901234) 

Will I need to switch to plain SQL?: /

Answer 1

I think my best snapshot is to build the line “where is SQL”, smooth and break the arguments, so I created this method:

 class Distance < ActiveRecord::Base def self.distance_matrix(origins, destinations) return false if origins.empty? || destinations.empty? where_sql = '(origin_lat, origin_lng) IN (' where_sql << (['(?, ?)'] * origins.length).join(', ') where_sql << ') AND (destination_lat, destination_lng) IN (' where_sql << (['(?, ?)'] * destinations.length).join(', ') << ')' where(where_sql, *origins.flatten, *destinations.flatten) end end 

and name it like this:

 Distance.distance_matrix([[1.234567, 2.345678], [5.678901, 6.789012]], [[3.456789, 4.567890], [7.890123, 8.901234]]) 

And it works: D

Thanks to @BradWerth for entering the correct track and @muistooshort to make the code more readable.

Answer 2

I guess this should be a little bigger:

Element.where ('(origin_lat, origin_lng) IN ((?,?), (?,?)) AND (destination_lat, destination_lng) IN ((?,?), (?,?))', 1.234567, 2.345678, 5.678901, 6.789012, 3.456789, 4.567890, 7.890123, 8.901234).