mysql - 向 Sequel 模型添加查找关联和列别名

Question

我想包括来自与主表关联(两个关系)的表的“查找”值。我正在使用遗留数据库。

主表:

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

键/值表:

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

目标是生成如下 SQL:

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

主表的模型:

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

有没有一种优雅的方法可以将这些链接和列别名添加到模型中？

Answer 1

我决定将键/值表添加到模型中:

class KeyValue < Sequel::Model (:key_value)

  set_primary_key [:key]

  # add association
  one_to_many :foo_table

end

然后在第二个模型中添加关联:

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