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
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)
On Tue, Apr 24, 2012 at 7:26 AM, Rafael C. de Almeida
On Apr 24, 1:55am, vishal singh [email protected] wrote:
@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.
Hi,
Phone.find(:all, :conditions=>[“area_code in ? and number in
?”,(555,533),(533,12345678)])
Regards,
Vishal Singh
Ruby On Rails Developer
On Tue, Apr 24, 2012 at 11:07 AM, Rafael C. de Almeida
<[email protected]
On Apr 24, 6:06am, Juan P. Avello [email protected] wrote:
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.
El martes, 24 de abril de 2012 03:56:21 UTC+2, Rafael C. de Almeida
escribi:
Lawyer.join(:phones).where(:area_code => area_code, :number =>
However, I don’t want to make many queries. This is how I would do a
Rafael
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)
Phone.where(“area_code in (?) and number in (?)”, [440,441,443],
[23233233,23231212,12121212])
2012/4/24 Rafael C. de Almeida [email protected]
–
You received this message because you are subscribed to the Google G.
“Ruby on Rails: Talk” group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
–
att,
Rogerio
A complicao se descomplica na mesma proporo que fazemos os ns se
desatarem ao tecer o conhecimento do saber.
On Apr 24, 2:55pm, Rogerio M. [email protected] 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.
How about using concatenation in your query?
Walter
On Apr 24, 5:34am, vishal singh [email protected] 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).
On Apr 24, 3:24pm, Rogerio M. [email protected] 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.
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 D. [email protected]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
–
att,
Rogerio
A complicao se descomplica na mesma proporo que fazemos os ns se
desatarem ao tecer o conhecimento do saber.
On 24 April 2012 20:32, Rafael C. de Almeida [email protected]
wrote:
Yes, that was the best approach I found, but it’s still not the best,
That’s why the activerecord solution strikes me as a bit odd. The
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.
On Apr 25, 2:41am, Juan P. Avello [email protected] 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.