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 %>
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.
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")
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.