How to create a multi-column IN predicate with ActiveRecord?

Question

I have the following 'distances' 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 ║
╚════╩════════════╩════════════╩═════════════════╩═════════════════╩══════════╝

The question is, how can I create the following SQL query (supported by PostgreSQL) with 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 doesn't 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 raises PG::FeatureNotSupported: ERROR: input of anonymous composite types is not implemented

The number of parameters is variable, so I can't just hard-code the query like this:

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)

Am I going to need to drop to plain SQL? :/

Answer 1

I guess my best shot is to build the "where SQL" string myself, flatten and splat 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 call it like:

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 getting me in the right track and to @muistooshort for making the code more readable.

Answer 2

I'm guessing it has to be a little more like this:

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)