Currently I'm trying, in a rails 3 application, to select all objects where two arrays have some overlap.
I've tried the following:
Contact.where("possible_unique_keys && ?" c.possible_unique_keys)
Which gives:
array value must start with "{" or dimension information
So I tried to convert the latter record into a postgresql array, like so:
Contact.where("possible_unique_keys && string_to_array(?)", c.possible_unique_keys)
Which gives:
operator does not exist: character varying[] && text[]
How so I get both arrays to a format that will correctly evaluate &&?
I think I've solved it for now, and come up with a way to build a query from a Ruby array to a postgresql array (thanks in part to this bit of code written for rails/postgresql interoperability.
Given a list of strings ["bill", "joe", "stu", "katie", "laura"], we have to do a little bit in the way of acrobatics to get postgresql to recognize them. This is a solution to construct the request.
request = "SELECT * from Contacts where possible_unique_keys && \'{"
list.each do |key|
key.gsub("'", "\\'")
if key == c.possible_unique_keys.last
request << "\"#{key}\"}\'"
else
request << "\"#{key}\", "
end
end
dupes = Contacts.find_by_sql(request)