|
Using Static Lookup Tables
By: Bruce Bahlmann - Contributing Author (your
feedback
is important to us!)
In rails development, ActiveRecord provides several methods to map
relationships between database tables. So, whether you are writing a rails
application from scratch, or building on top of an existing database,
support
exists to utilize one or more tables to host lookup data. The lookup tables
provide the benefit of increased separation between common lists of items
(which can be used across multiple applications) and the application
specific data which uses them. It also allows these common lists of items to
be separately maintained so one can for example change the spelling of one
of the list items without needing to find instances of that list item used
elsewhere because a convenient index of the item is what is stored there
rather than the actual text of the item.
Fortunately, Ruby on Rails (RoR)
provides an easy way to hook up static lookup tables. In this article, you
will see the required steps to program this as well as details regarding updates
required to views that are also impacted by the resulting relationships. Here is
how it works:
[app/models/classification.rb]
class Classification < ActiveRecord::Base
establish_connection :apps
set_table_name "global_classification"
has_many :inventory
...
First you need to define the relationships required for rails to understand
how to tie the data together. In this example, two different models/tables are
connected up: Inventory and Classification. Since this application is an
example of a rails application written overtop an existing database you will
notice some additional database connection information in there (establish_connection
and set_table_name) which take the place of formal field declarations one
would need if you were connecting to an existing database. The important
thing to note here is the stated relationship: classifications
has_many :inventory - meaning
there are many inventory records which share the same classification. Here
is what the classification table looks like:
This static lookup table connects up to the inventory database which
is described next:
[app/models/inventory.rb]
class Inventory < ActiveRecord::Base
establish_connection :apps
set_table_name "global_itinventory"
set_primary_key :nKeyID
set_inheritance_column :ruby_type
pluralize_table_names = false
default_scope :order => 'lmodified DESC'
belongs_to :classification, :foreign_key => :classification_id
...
This too represents a model connecting to an existing database. Note,
however the key to this definition is that of the stated association with
the classification model: belongs_to :classification, :foreign_key => :classification_id
This association tells the inventory model where to find the detail of
its classification field (column). ONE OTHER IMPORTANT NOTE is that rails
follows a strict naming convention and if you don't follow it you will start
to see errors like:
ActiveRecord::AssociationTypeMismatch
After pointing the rails application to the existing databases, one other
necessary step was completed which involved renaming the classification
column from classification to classification_id. It is VITAL that the
pointer (or reference) column name end with
_id or else many bad things
will happen. Spent several days trying to make it all work (which it never
fully did), yet within a few minutes of renaming the column to
classification_id and commenting out all the sandbag code written to attempt
to make the column named classification work, everything
(create, update, index) just worked without the extra code. Here is what the inventory table looks
like:
With these associations in place, we are now able to easily access the data
across the now joined tables.
[app/controllers/inventories_controller.rb]
class InventoriesController < ApplicationController
# GET /inventories
# GET /inventories.json
def index
@inventories = Inventory.find(:all,:include=>:classification)
...
In the controller, you might notice a new switch in the find statement
which uses the option include:.
Using include is purely optional - the find will work without it. However, a
find involving a join of static lookup table(s) without using include will
pay a performance penalty. That is because using the include switch enables
rails to use eager loading (a form of caching) that is applied to the static
lookup table that prevents rails from performing a SQL call for each record
in the inventory to join it with its corresponding classification record.
Some people refer to this as the dreaded 1+N problem. Instead, with eager
loading, the number of SQL queries in our example is reduced to just 2.
Implementing this change, allows the lookup table data to become
available for display in the views. Note that the added relationship adds a
few wrinkles to displaying the data. Here are some examples to help changes
necessary within views to display the new associations that were just
created:
[app/views/inventories/index.html.erb]
<table cellpadding="0" cellspacing="0" border="0" class="display" id="example">
<thead>
<tr>
<th>LSI Asset Tag</th>
<th>Classification</th>
</tr>
</thead>
<tbody>
<% @inventories.each do |inventory| %>
<tr>
<td><%= link_to inventory.tagNum, edit_inventory_path(inventory) %></td>
<td><%= inventory.classification.nil? ? "" : inventory.classification.name %></td>
</tr>
<% end %>
</tbody>
</table>
...
Since inventory.classification represents an association rather than a
normal field (column), one must add the name of the field (column) within
the associated lookup table to display the desired field [inventory.classification.name]. For further
clarification, here is what the data looks like when it comes into the index
view:
[<%= @inventory.to_json %>]
{"manufacturer_id":4,"nKeyID":3072,"location":"185","lmodified":1317232651,
"data":"{\"macaddress\":\"\"}","purchaseOrder":"","costCenter":"0","cost":"123",
"assignedTo":"","upload":"","tagNum":"1234","notes":"","project":"",
"disposition":"3","serialNum":"123","classification_id":4,"purchaseDate":9,
"warrantyDuration":11,"modelNum":"123","vendorNum":"3","uploadMIME":""}
...
Notice in the case of classification_id, what is shown is the id of the
classification table entry corresponding to the value (if any) selected.
From this value/association, you can use the provided object to display the
classification name associated with the id given because the other values
have been eager loaded.
Another view shows the main web form used for creating and editing inventory and
you pass the various classifications into this view to display as a drop
down select. In such a form type view, when you edit existing inventory
entries you want to show the classification associated with each entry as
selected.
[app/views/inventories/_form.html.erb]
<%= form_for(@inventory) do |f| %>
<div class="field">
<%= f.label :classification %>
<%= f.collection_select :classification_id, @classifications, :id, :name,
{:include_blank => true, :selected => @inventory.classification_id} %>
</div>
<% end %>
...
To accommodate the additional association you created, a collection
select is used which displays the list of available classifications as a
drop down, properly assigns a value (index id) for each of the
classifications in the list, and displays the classification selected in a
prior saved record.
Here is what your select should look like in the
html source:
[app/views/inventories/_form.html.erb]
<select id="inventory_classification_id" name="inventory[classification_id]">
There is a lot more techniques defined here between the lines that may be
useful to others, but rather than just show a simple example like you mostly
find on the Internet, real world examples always rule.
Can Birds-Eye.Net help you or your Company?
Receive your Birds-Eye.Net articles and white
papers hot off
the presses by adding our RSS feed to your reader.
|
|