Data Comparison

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

Well, 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@AgileConsultingLLC.com

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!

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

WHERE 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@AgileConsultingLLC.com