CODEWITHSUNDEEP
ruby-on-railsactiverecord
Rafael Almeida
Rafael Almeida

Reputation: 2397

Rails: Multiple or conditions in ActiveRecord

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.

Upvotes: 21

Views: 37484

Answers (3)

Flavio Wuensche
Flavio Wuensche

Reputation: 10336

I'd expect ActiveRecord to support this syntax for us, but it's already been discussed here and no action seems to have been taken. To work around the issue, you can use something like:

phone_numbers = [['555', '55555555'], ['444', '44444444'], ...]
placeholders = Array.new(phone_numbers.size, '(?)').join(', ')
Phone.where("(area_code, number) IN (#{placeholders}", *phone_numbers)

Upvotes: 1

xjlin0
xjlin0

Reputation: 350

Lawyer.join(:phones).where(
  "(phones.area_code, phones.number) IN ( ('555', '5555555'), ('444', '12345678') )"
)

Upvotes: 5

miked
miked

Reputation: 4499

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 (...)

Upvotes: 19

Related Questions