Grails/GORM Mapping To Non-Standard-Named Foreign Key

Question

I have a table that stores a hierarchy of agents:

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);

I've modeled it as:

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

Each agent may have many properties:

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);

I've modeled that as:

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

I've created a view to easily see the heirarchy of agents:

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;

My problem is in modeling the pathogen view. I've done this:

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

This implies that there is a foreign key 'pathogen_id' in the agent_property table. But, that is not the case. The foreign key is agent_id. I want AgentProperty to relate to Pathogen on agent_id as if there were the constraint:

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

I tried to map the implied property agentProperties to agent_id in my Pathgeon class, something like:

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

but that didn't work.

How do I tell GORM to use agent_property.agent_id as the foreign key?

score 8 · Accepted Answer · answered Jun 20 '13 at 18:07

The solution to my original problem is that I failed to to put agent_id in quotes.

agentProperties column: 'agent_id'

This works now:

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
  }
}

score 1 · Answer 2 · answered Jun 18 '13 at 21:57

1

Your domain classes needs little bit of modification to stick to the design you have in database,

class Agent {
  String agentName
  Agent agentParent

  //agent_id Foreign Key to AgentProperty. Agent has many AgentProperties
  static hasMany = [agentProperties: AgentProperty] 

  static mapping = {
    id column: 'agent_id'
    id generator: 'sequence', params: [sequence: 'agent_id_seq']
  }
}

class AgentProperty {
  String agentPropertyName

  //AgentProperty belongs to an Agent. Cascade delete is enabled
  static belongsTo = [agent: Agent]
  static mapping = {
    id column: 'agent_property_id'
    id generator: 'sequence', params: [sequence: 'agent_property_id_seq']
  }
}

class Pathogen {
  String genus
  String species
  String strain
  String toxin

  //like foreign key pathogen_id in agent table
  static hasMany = [agents: Agent]
}

You can get hold of AgentProperty from Pathogen via Agent.

If I read your question correctly, then this is what you need.

Pathogen hasMany Agents
Agent hasMany AgentProperty

answered Jun 18 '13 at 21:57

dmahapatro

49,365
7
88
117

Thanks dmahapatro. There is no pathogen_id in the agent table, and I can't change that. Also, pathogen-to-agent is not one-to-many. If anything, it would be 1:1. Pathogen is a VIEW in the database. You could say that pathogen hasOne agent where pathogen.id == agent_id, but each agent does not have one pathogen. – Mark Anderson Jun 19 '13 at 19:50
@MarkAnderson Sorry I did not realize `Pathogen` is a db `view`. Do you really need that view any more because you can use criteria query on `Agent` and `AgentProperty` to get what you want. The select query used in the view can be converted to a [Criteria](http://grails.org/doc/2.2.1/ref/Domain%20Classes/createCriteria.html).If you are mandaed to use the view [here is how you can access it](http://stackoverflow.com/questions/425294/sql-database-views-in-grails). – dmahapatro Jun 19 '13 at 19:58
To model that, could I make 'class Pathogn { String genus; String species; String strain; String toxin; Agent agent}; static mapping = [column agent: 'agent_id']} – Mark Anderson Jun 19 '13 at 20:17
@MarkAnderson Yes you can try that, but I am unsure how would that play with a db `view` and not a `table`. – dmahapatro Jun 19 '13 at 20:25

Grails/GORM Mapping To Non-Standard-Named Foreign Key

2 Answers2