Searching for area code and phone number together

rubyonrails-talk
1

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

2

number)

3

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.

4

Hi,

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

Regards, Vishal Singh Ruby On Rails Developer

5

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)

6

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.

7

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

8

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

9

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.

10

How about using concatenation in your query?

Walter

11

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

12

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.

13

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.

14

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.