Grails/GORM 映射到非标准命名的外键

Question

我有一个存储代理层次结构的表:

create table agent (
  agent_id int not null,
  agent_name varchar(255),
  agent_parent_id,
  constraint pk_agent primary key (agent_id));

alter table agent 
  add constraint fk_agent_agent foreign key (agent_parent_id) references (agent_id);

我将其建模为:

class Agent {
  String agentName
  Agent agentParent
  static mapping = {
    id column: 'agent_id'
    id generator: 'sequence', params: [sequence: 'agent_id_seq']
  }
}

每个代理可能有许多属性:

create table agent_property (
  agent_property_id int not null,
  agent_property_name varchar(255),
  agent_id int,
  constraint pk_agent_property primary key (agent_property_id));

alter table agent_property (
  add constraint fk_agent_property_agent foreign key (agent_id) references agent(agent_id);

我将其建模为:

class AgentProperty {
  String agentPropertyName
  static hasOne = [agent: Agent]
  static mapping = {
    id column: 'agent_property_id'
    id generator: 'sequence', params: [sequence: 'agent_property_id_seq']
  }
}

我创建了一个 View 来轻松查看代理的层次结构:

create view pathogen as
  select c.agent_id as id, a.agent_name as genus, b.agent_name as species, c.agent_name as strain, d.agent_name as toxin
  from agent a 
  left join agent b on a.agent_id = b.agent_parent_id
  left join agent c on b.agent_id = c.agent_parent_id
  left join agent d on c.agent_id = d.agent_parent_id
  where a.agent_parent_id is null;

我的问题在于对病原体 View 进行建模。我已经这样做了:

class Pathogen {
  String genus
  String species
  String strain
  String toxin
  static hasMany = [agentProperties: AgentProperty]
}

这意味着 agent_property 表中有一个外键“pathogen_id”。但事实并非如此。
外键是 agent_id。
我希望 AgentProperty 与 agent_id 上的 Pathogen 相关，就好像存在约束一样:

alter table agent_propery 
  add constraint fk_agent_property_pathogen foreign key (agent_id) references pathogen (id);

我试图将隐含的属性 agentProperties 映射到我的 Pathgeon 类中的 agent_id，例如:

static mapping = {
  agentProperties column: agent_id  // or AgentProperty.agent
}

但这没有用。

我如何告诉 GORM 使用 agent_property.agent_id 作为外键？

Answer 1

我原来问题的解决方案是我没有将 agent_id 放在引号中。

agentProperties column: 'agent_id'

这现在有效:

class Pathogen {
  String genus
  String species
  String strain
  String toxin

  static hasMany = [agentProperties: AgentProperty]

  static mapping = {
    // use agent_id to releate to AgentProperty
    agentProperties column: 'agent_id'
  }
}

class AgentProperty {
  String agentPropertyName

  static belongsTo = [agent: Agent]
  static hasOne = [pathogen: Pathogen]

  static mapping = {
    id column: 'agent_property_id'
    id generator: 'sequence', params: [sequence: 'agent_property_id_seq']
    // use agent_id to relate to Pathogen
    pathogen column: 'agent_id', insertable: false, updateable: false
  }
}