Searching for area code and phone number together

[Rafael C. de Almeida]

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

[vishal singh]

hi,

Make a two model, Lawyer and Phone then in lawyer model add has_any :phones and in phone model add belongs_to :lawyer 

then find lawler like

  

 @lawyer = Lawyer.find(1)   1 is id in lawyer table

then write

 @lawyer.phones (it will fetch all the phone number of this lawyer havind id =1)

 you can add where clause

 @lawyer.phones.where(:area_code => area_code, :number =>

number)

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To post to this group, send email to rubyonra...@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-ta...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

[Rafael C. de Almeida]

On Apr 24, 1:55 am, vishal singh <vishal.singh...@gmail.com> wrote:
> hi,
>
> Make a two model, Lawyer and Phone then in lawyer model add has_any :phones
> and in phone model add belongs_to :lawyer
>
> then find lawler like
>
>  @lawyer = Lawyer.find(1)   1 is id in lawyer table
>
> then write
>  @lawyer.phones (it will fetch all the phone number of this lawyer havind
> id =1)
>  you can add where clause
>  @lawyer.phones.where(:area_code => area_code, :number =>
> 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.

[vishal singh]

Hi,

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

Regards,

Vishal Singh

Ruby On Rails Developer

[Juan Pablo Avello]

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)

[Rafael C. de Almeida]

On Apr 24, 6:06 am, Juan Pablo Avello <xua...@gmail.com> wrote:
> El martes, 24 de abril de 2012 03:56:21 UTC+2, Rafael C. de Almeida
> escribió:
> > 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.

> >     select * from phones where (area_code, number) in (('555',
> > '1234564'), ('533', '12345678'))
>
> > How to translate that to ActiveRecord?
>

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

[Rafael C. de Almeida]

On Apr 24, 5:34 am, vishal singh <vishal.singh...@gmail.com> wrote:
> Hi,
>
> Phone.find(:all, :conditions=>["area_code in ? and number in
> ?",(555,533),(533,12345678)])
>

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

[Rogerio Medeiros]

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

2012/4/24 Rafael C. de Almeida <almei...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To post to this group, send email to rubyonra...@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-ta...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

--
att,

Rogerio

A complicação se descomplica na mesma proporção que fazemos os nós se desatarem ao tecer o conhecimento do saber.

[Rafael C. de Almeida]

On Apr 24, 2:55 pm, Rogerio Medeiros <arge...@gmail.com> wrote:
> 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.

[Walter Lee Davis]

How about using concatenation in your query?

Walter

[Rogerio Medeiros]

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

2012/4/24 Walter Lee Davis <wa...@wdstudio.com>

[Rafael C. de Almeida]

On Apr 24, 3:24 pm, Rogerio Medeiros <arge...@gmail.com> wrote:
>  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.

[Juan Pablo Avello]

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To post to this group, send email to rubyonra...@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-ta...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

I insist on includes():

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.

[Rafael C. de Almeida]

On Apr 25, 2:41 am, Juan Pablo Avello <xua...@gmail.com> wrote:
> 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)

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.