Rails: Multiple or conditions in ActiveRecord

Question

I have two tables: Lawyer and Phone. Phone is separated into area code and number. A lawyer has many phones. I want to produce a query that searches for lawyers who have a phone matching a phone from a list of phones.

If I had only one phone I could search it like this:

Lawyer.join(:phones).where(:area_code => area_code, :number => number)

The problem is that I have a list with more than one area code. So I really want to do something like this:

lawyers = []
phones.each { |phone| lawyers += Lawyer.join(:phones).where(:area_code => phone[:area_code], :number => phone[:number]) }

However, I don't want to make many queries. Can't it be done in a single query statement?

Edit: This is how I would do a similar thing using SQL alone (assuming the list of numbers was [{:area_code=>'555', :number=>'1234564'}, {:area_code=>'533', :number=>'12345678'}])

select * from phones where (area_code, number) in (('555', '1234564'), ('533', '12345678'))

If someone can get that translated into ActiveRecord, that'd be great.

Answer 1

If you pass an array of area_codes, AR will build an IN condition. So you could use your original search and use arrays instead:

where area_codes is an array of area_codes and numbers an array of numbers:

 Lawyer.join(:phones).where(:area_code => area_codes, :number => numbers)

or:

 Lawyer.join(:phones).where("phones.area_code IN (?) AND phones.number IN (?)", area_codes, numbers)

Should yield:

 SELECT * from lawyers JOIN phones ON phones.lawyer_id = lawyers.id WHERE phones.area_code IN (...) AND phones.number IN (...)