Searching for area code and phone number together

Hello

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 which
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. 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'))

How to translate that to ActiveRecord?

Cheers,
Rafael

number)

The thing is that I have a list of possible area codes and numbers.
The way you wrote it won't allow me to search for lawyers who have a
phone inside my list. Unless, I do multiple queries. I suppose there's
no way to translate select * from phones where (area_code, number) in
(('555', '1234564'), ('533', '12345678')) into activerecord. Doing a
for like the one I wrote before is probably the way to go.

Hi,

Phone.find(:all, :conditions=>["area_code in ? and number in
?",(555,533),(533,12345678)])

Regards,
Vishal Singh
Ruby On Rails Developer

Your best option is probably:

Lawyer.includes(:phones).where( :phones => {:area_code => phone[:area_code], :number =>

phone[:number]}) }

Then you may iterate through phones at your will.

Using .includes() preloads related models, phones in this case, and subsequent queries do not actually query the db but the data structure stored in memory (you can easily check this in a console; with .includes() no ‘select *…’ will appear on the logs when accessing a lawyer’s phones)

That doesn't seem to work. First I tried the approach with joins:

1.9.3p194 :071 > lawyers = []
1.9.3p194 :071 > phones = [{area_code:'31', number:'32210412'},
{area_code:'32', number:'32210412'}]
1.9.3p194 :071 > phones.each { |phone| lawyers +=
Lawyer.joins(:phones).where(phones:phone) }
  Lawyer Load (1.0ms) SELECT "lawyers".* FROM "lawyers" INNER JOIN
"phones" ON "phones"."lawyer_id" = "lawyers"."id" WHERE
"phones"."area_code" = '31' AND "phones"."number" = '32210412'
  Lawyer Load (0.4ms) SELECT "lawyers".* FROM "lawyers" INNER JOIN
"phones" ON "phones"."lawyer_id" = "lawyers"."id" WHERE
"phones"."area_code" = '32' AND "phones"."number" = '32210412'

Then I tried it with includes:

    phones.each { |phone| lawyers +=
Lawyer.includes(:phones).where(phones:phone) }

includes' version was more verbose, but made two SQL queries as well.

That wouldn't work. That would match the phone
{area_code:'533',number:'533'}. I want to match only (555, 533) or
(533, 12345678).

Phone.where("area_code in (?) and number in (?)", [440,441,443],
[23233233,23231212,12121212])

That would match phone (440) 12121212. In that situation, I'd like to
match (440) 23233233, (441) 23231212 and (443) 12121212. However I
would not like to match (440) 12121212.

How about using concatenation in your query?

Walter

phones = [{area_code:'31', number:'32210412'},
{area_code:'32', number:'32210412'}]

lawyers = []
phones.each do |phone|
  lawyer << Lawyer.joins(:phones).where('phones.area_code = ? and
phones.number ', phone.area_code, phone.number)
end

Yes, that was the best approach I found, but it's still not the best,
since it makes several SQL queries instead of only the one needed. I
guess active record just isn't flexible enough to create a query such
as select * from phones where (area_code, number) in (('555',
'1234564'), ('533', '12345678')). Maybe that is not supported by all
databases that activerecord supports? I think that approach is
probably the best way to go for now. I was just hoping someone would
come up with something which looks like .where([:area_code, :number]
=> [['31','322210412'], ...]).

I have a lot of experience with SQL, but not much with activerecord.
That's why the activerecord solution strikes me as a bit odd. The
runtime of the multiple queries against the single one probably won't
be too different if I have (area_code, number) index.

Lawyer.includes(:phones).where("(phones.area_code in (:area_codes) and
phones.number = :phone_no)", :area_codes => [31, 32], :phone_no => '
322210412 ')

Or, if phone nos. are different:

phones = [['31', '32221'0412'], ['32','422020202']].map{|q|
"phones.area_code = #{q[0]} and phones.number = #{q[1]}"}.join(' or ')
Lawyer.includes(:phones).where(phones)

Regards.

That one could lead to injection, I think. However, something like
this would work:

phones = [['31', '322210412'], ['32','422020202']]
query = phones.map{|q| "phones.area_code = ? and phones.number
= ?"}.join(' or ')
Lawyer.includes(:phones).where(query, phones.flatten)

This seems to be the best solution so far.