Hello,
I am having trouble trying to get a data table comparison working... I
have two database tables with their own models. Both contain phone
number information. What I need the program to do is to read every phone
number from every row from Table1 and compare that number to every
number in every row in Table2. It then needs to put that information in
two variables. One containing all the entries that match in both tables
and one containing all the entries that do not match. I am very new to
RoR and I can do this no problem in PHP, but my boss wants it in Ruby...
I have a "compare" controller in which all this should take place. I
have tried using find_by_sql with no luck... I think my big problem is
simply not defining variables properly or something. My controller looks
like this: Btw, "bes" is one table (holding Blackberry phone info) and
"prov" is another table (holding info from our wireless provider).
def index
@bes = Bes.find(:all)
@prov = Import.find(:all)
@provnumber = Import.prov_service_number
@matches = Bes.find_by_sql("SELECT bes_phonenumber number,
bes_displayname name FROM bes WHERE bes_phonenumber = @provnumber")
end
My view has something like this:
<% for match in @matches -%>
<% @matches %>
<tr>
<td><%= match.name %></td>
<td><%= match.number %></td>
<td><%= match.prov_number %></td>
</tr>
<% end %>
Thanks for all your help! I appreciate it!
- Jeff
--
Posted via http://www.ruby-forum.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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
On Feb 7, 2008, at 6:19 PM, Jeff Miller wrote:> Hello, > I am having trouble trying to get a data table comparison working... I > have two database tables with their own models. Both contain phone > number information. What I need the program to do is to read every > phone > number from every row from Table1 and compare that number to every > number in every row in Table2. It then needs to put that information > in > two variables. One containing all the entries that match in both > tables > and one containing all the entries that do not match. I am very new to > RoR and I can do this no problem in PHP, but my boss wants it in > Ruby... > I have a "compare" controller in which all this should take place. I > have tried using find_by_sql with no luck... I think my big problem is > simply not defining variables properly or something. My controller > looks > like this: Btw, "bes" is one table (holding Blackberry phone info) and > "prov" is another table (holding info from our wireless provider). > > def index > @bes = Bes.find(:all) > @prov = Import.find(:all) > > @provnumber = Import.prov_service_number > @matches = Bes.find_by_sql("SELECT bes_phonenumber number, > bes_displayname name FROM bes WHERE bes_phonenumber = > @provnumber") > end > > My view has something like this: > > <% for match in @matches -%> > <% @matches %> > <tr> > <td><%= match.name %></td> > <td><%= match.number %></td> > <td><%= match.prov_number %></td> > </tr> > <% end %> > > Thanks for all your help! I appreciate it! > - JeffWell, you can''t get much help if you don''t provide enough information. Perhaps if you showed what the models look like or the columns from your tables. As a start, an ActiveRecord model called "Be" would keep its records in a table named ''bes''. class Be < ActiveRecord::Base end If you had SQL that gave you the results that you wanted, you could either get a list of bes.id or skip the whole model thing and jump straight to the adapter: @matches = Be.connection.select_all("SELECT bes.bes_phonenumber AS number, bes.bes_displayname AS name, prov.number AS prov_number FROM bes JOIN prov ON bes.bes_phonenumber = prov.number") which would give you an array of hashes that are keyed by ''number'', ''name'', and ''prov_number'' <table> <thead> <tr> <th>Name</th> <th>Number</th> <th>ProvNumber</th> </tr> </thead> <tbody> <% for match in @matches -%> <tr> <td><%= match[''name''] %></td> <td><%= match[''number''] %></td> <td><%= match[''prov_number''] %></td> </tr> <% end -%> </tbody> </table> But if this doesn''t get you closer to a solution, ask again WITH MORE DETAIL. In particular, you probably need to do an OUTER JOIN to find the records that lack a match in the other table. If you need to get the unmatched records in both directions, you likely need two separate OUTER JOIN queries. -Rob Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Thank you very much for that detailed response! It clarified a lot for
me. Sorry I didn''t include enough information... I am very new to RoR
and I haven''t used any kind of MVC type framework before. Both my
models
are empty (I don''t really know what I would put in them...) and table1
is called ''bes'' and table2 is called
''imports''. Everything seems to be
working great! I tried to get the OUTER JOIN sql statement to work, but
it actually gives me both matches and non-matches, instead of just
non-matches. I know its not really a Ruby deal, but do you know the
correct statement? I''ve been trying to look it up, but no luck so far.
@nonmatches = Bes.connection.select_all("SELECT bes.bes_phonenumber AS
number, bes.bes_displayname AS name, imports.prov_service_number AS
prov_number FROM bes LEFT OUTER JOIN imports ON bes.bes_phonenumber =
imports.prov_service_number")
Thanks dude! I really appreciate your help!
--
Posted via http://www.ruby-forum.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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
On Feb 7, 2008, at 9:04 PM, Jeff Miller wrote:> > Thank you very much for that detailed response! It clarified a lot for > me. Sorry I didn''t include enough information... I am very new to RoR > and I haven''t used any kind of MVC type framework before. Both my > models > are empty (I don''t really know what I would put in them...) and table1 > is called ''bes'' and table2 is called ''imports''. Everything seems to be > working great! I tried to get the OUTER JOIN sql statement to work, > but > it actually gives me both matches and non-matches, instead of just > non-matches. I know its not really a Ruby deal, but do you know the > correct statement? I''ve been trying to look it up, but no luck so far. > > @nonmatches = Bes.connection.select_all("SELECT bes.bes_phonenumber AS > number, bes.bes_displayname AS name, imports.prov_service_number AS > prov_number FROM bes LEFT OUTER JOIN imports ON bes.bes_phonenumber > imports.prov_service_numberWHERE imports.prov_service_number IS NULL> ") > > > Thanks dude! I really appreciate your help! > --You need to select only those rows that exist in the result due to the addition of the null record from the other table (because of the OUTER). It may not be something easily looked up, but it should be easy enough to understand and apply later. -Rob Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---