Add look-up associations and column aliases to Sequel model

Question

I would like to include 'look-up' values from a table that is associated (two relationship) with the primary table. I'm working with a legacy database.

Primary table:

CREATE TABLE foo_table (
  id  INT PRIMARY KEY NOT NULL,
  name VARCHAR(255),
  created_key  INT,
  modified_key INT
)

Key/Value table:

CREATE TABLE key_value (
  key  INT PRIMARY KEY NOT NULL,
  name VARCHAR(50)
)

The goal is to generate SQL like:

SELECT foo_table.id, foo_table.name, foo_table.created_key, foo_table.modified_key,
       kv0.name version_created,
       kv1.name version_modified
FROM  foo_table
LEFT OUTER JOIN key_value kv0 on key.created_key=kv0.key
LEFT OUTER JOIN key_value kv1 on key.modified_key=kv1.key

Primary table's model:

class Foo < Sequel::Model (:foo_table)

  set_primary_key [:id]

  # stuck here
  self.left_outer_join(:key_value, :key => :created_key)
  self.left_outer_join(:key_value, :key => :modified_key)

end

Is there an elegant way to add these linkages and column aliases to the model?

Answer 1

I decided to add the key/value table to the model:

class KeyValue < Sequel::Model (:key_value)

  set_primary_key [:key]

  # add association
  one_to_many :foo_table

end

Then add an association in the second model:

class Foo < Sequel::Model (:foo_table)

  set_primary_key [:id]

  # add associations
  many_to_one :created, :class=>:KeyValue, :key => :created_key, :foreign_key => :key
  many_to_one :modified, :class=>:KeyValue, :key => :modified_key, :foreign_key => :key

end