So look up tables are fun. Well, maybe not fun, but they are good for database integrity and all that jive. But how to do them cleanly in Rails?
Firstly, lets talk about database schemas. There are 2 primary ways to do lookup tables. One is to have a lookup table for each field. You end up with something like:
lookup_genders
-------------
int id
varchar description
lookup_states
-----------
int id
varchar description
This is a bit messy, as you can end up with a gazillion of these suckers in your database. Now, if you aren’t a normal form nazi, you can break the rules a bit and do something like:
lookups
-------
int id
varchar description
varchar field_name
Now, this is populated with data like:
[1, 'Male', 'gender']
[2, 'Female', 'gender']
[3, 'KY', 'states']
I’m using the latter for my rails app. Now when displaying the data in pure text form, I want the value of the id to be translated into its cooresponding description. So in the rails model that contains a lookup, I override the method to return that field with this (I’ll use gender as my example from here on):
# for reading
def gender
# first we get the name of the function we're operating within.
# this is done with the ruby Kernel#caller() method.
db_field = caller(0)[0].match(/`(.*)'/)[1]
@lookup = Lookup.find(:all, :conditions => [ "field_name = ?", db_field])
for i in @lookup
if self[db_field] == i.id
return i.description
end
end
return 'lookup error!'
end
Lookup.find just searches through our lookup table (it’s an empty model in rails).
Cool right? Now what about putting the data back in the database? Well the best way to deal with lookup data is with a dropdown. So I have the following.
In /app/helps/application_helper.rb:
def lookup_dropdown(parent_record, lookup_field)
name = parent_record + "[" + lookup_field + "]"
lookup = Lookup.find(:all, :conditions => [ "field_name = ?", lookup_field])
render_partial("shared/lookup_dropdown", {:name => name, :values => lookup})
end
In /app/views/shared, add the file _lookup_dropdown.rhtml with the following contents:
<select name=<%= name %>>
<% for i in values %>
<option value="<%= i.id %>"><%= i.description %></option>
<% end %>
</select>
Now, anywhere where you want a dropdown for this lookup/field you can use:
<%= lookup_dropdown "inmate", "gender" %>
Tada!
