Using RoR with a legacy table that uses E-A-V

Question

I'm needing to connect to a legacy database and pull a subset of data from a table that uses the entity-attribute-value model to store a contact's information. The table looks like the following:

subscriberid     fieldid     data
1                2           Jack
1                3           Sparrow
2                2           Dan
2                3           Smith

where fieldid is a foreign key to a fields table that lists custom fields a given customer can have (e.g. first name, last name, phone). The SQL involved is rather hairy as I have to join the table to itself for every field I want back (currently I need 6 fields) as well as joining to a master contact list that's based on the current user.

The SQL is something like this:

select t0.data as FirstName, t1.data as LastName, t2.data as SmsOnly
  from subscribers_data t0 inner join subscribers_data t1 
    on t0.subscriberid = t1.subscriberid
  inner join subscribers_data t2 
    on t2.subscriberid = t1.subscriberid
  inner join list_subscribers ls 
    on (t0.subscriberid = ls.subscriberid and t1.subscriberid = ls.subscriberid)
  inner join lists l
    on ls.listid = l.listid
  where l.name = 'My Contacts'
    and t0.fieldid = 2 
    and t1.fieldid = 3;

How should I go about handling this with my RoR application? I would like to abstracat this away and still be able to use the normal "dot notation" for pulling the attributes out. Luckily the data is read-only for the foreseeable future.

Answer 1

This is exactly what #find_by_sql was designed for. I would reimplement #find to do what you need to do, something like this:

class Contact < ActiveRecord::Base
  set_table_table "subscribers_data"

  def self.find(options={})
    find_by_sql <<EOS
      select t0.data as FirstName, t1.data as LastName, t2.data as SmsOnly
        from subscribers_data t0 inner join subscribers_data t1 
          on t0.subscriberid = t1.subscriberid
        inner join subscribers_data t2 
          on t2.subscriberid = t1.subscriberid
        inner join list_subscribers ls 
          on (t0.subscriberid = ls.subscriberid and t1.subscriberid = ls.subscriberid)
        inner join lists l
          on ls.listid = l.listid
        where l.name = 'My Contacts'
          and t0.fieldid = 2 
          and t1.fieldid = 3;
    EOS
  end
end

The Contact instances will have #FirstName and #LastName as attributes. You could rename them as AR expects too, such that #first_name and #last_name would work. Simply change the AS clauses of your SELECT.

Answer 2

I am not sure it is totally germane to your question, but you might want to take a look at MagicModel. It can generate models for you based on a legacy database. Might lower the amount of work you need to do.