Grails

mapping problem with 2 classes having 1-many and many-many relations

Details

  • Type: Bug Bug
  • Status: Open Open
  • Priority: Major Major
  • Resolution: Unresolved
  • Affects Version/s: 1.0.2
  • Fix Version/s: 2.3-M2
  • Component/s: Persistence
  • Labels:
    None
  • Environment:
    normal grails development env, with postgres instead of hsqldb

Description

I have two domain classes, Task and User, that have many-to-many and one-to-many relations between them.
I cannot put the GORM mapping to work properly. The only way I could put it to work was with hibernate xml mappings.

I tried it in two ways: with and without mappedBy static attribute.

1) Without mappedBy.

class User{
String name
String email

static hasMany = [observedTasks: Task]

static mapping =

{ table 'appuser' observedTasks column:'user_id', joinTable:'user_task' }

}

class Task {
String name
String code

User reporter
User assignee

static def hasMany = [observers: User]
static def belongsTo = [User]

static mapping =

{ observers column:'task_id', joinTable:'user_task' }

}

The bug I identified was that GORM has generated the following join table:
CREATE TABLE user_task
(
user_id int8 NOT NULL,
assignee_id int8,
task_id int8 NOT NULL,
CONSTRAINT fkx FOREIGN KEY (assignee_id) REFERENCES task (id) ,
CONSTRAINT fk2 FOREIGN KEY (task_id) REFERENCES task (id),
CONSTRAINT fk1 FOREIGN KEY (user_id) REFERENCES appuser (id) )
)

The problem is that the column assignee_id should not exist (neither the FK associated with it)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++

2) With mappedBy.

class User{
String name
String email

static hasMany = [observedTasks: Task, reportedTasks: Task, assignedTasks: Task]
static mappedBy = [reportedTasks:'reporter', assignedTasks:'assignee']

static mapping =

{ table 'appuser' observedTasks column:'user_id', joinTable:'user_task' }

}

class Task {
String name
String code

User reporter
User assignee

static def hasMany = [observers: User]
static def belongsTo = [User]

static mapping =

{ observers column:'task_id', joinTable:'user_task' }

}

What happens is that four tables are generated:
appuser
appuser_task (this is the one that is totally crazy. Should not exist at all)
task
user_task (this one is created with a assignee_id column that should not exist)

Am I doing something wrong with these two classes (Task and User)? Or is it a GORM bug?

Tks
Felipe

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Peter Ledbrook added a comment -

Pushing back to 1.0.4. Please attach a sample project that reproduces the problem, if you can.

Show
Peter Ledbrook added a comment - Pushing back to 1.0.4. Please attach a sample project that reproduces the problem, if you can.
Hide
Permalink
Felipe Nascimento added a comment -

Sorry. I am away for a couple of weeks. Cannot attach the sample project for this time being. Will you wait or will you create your own project with these two domain classes?

Show
Felipe Nascimento added a comment - Sorry. I am away for a couple of weeks. Cannot attach the sample project for this time being. Will you wait or will you create your own project with these two domain classes?
Hide
Permalink
Oliver Weichhold added a comment -

Is there any workaround for this? I'm being bitten by this one too.

Show
Oliver Weichhold added a comment - Is there any workaround for this? I'm being bitten by this one too.
Hide
Permalink
Felipe Nascimento added a comment -

The workaround is to use Hibernate HBM files instead of GORM.

For the example above, the hbm files would be:

 
<hibernate-mapping>
  <class name="User" table="appuser">
    <id name="id" column="id" unsaved-value="null">
      <generator class="native"></generator>
    </id>
    
    <version name="version" column="version" type="java.lang.Long"/>
    
    <property name="name" column="name" not-null="true"/>    
    
    <set name="observedTasks" table="user_task" lazy="true" cascade="save-update">
      <key>
        <column name="user_id" not-null="true"/>
      </key>
      <many-to-many class="Task">
        <column name="task_id" not-null="true"/>
      </many-to-many>
    </set>
  </class>
</hibernate-mapping>

 
<hibernate-mapping>
  <class name="Task" table="task">
    <id name="id" column="id" unsaved-value="null">
      <generator class="native"></generator>
    </id>

    <version name="version" column="version" type="java.lang.Long"/>
    <property name="name" column="name" not-null="true"/>

    <many-to-one name="reporter" class="User" column="reporter_id"/>
    <many-to-one name="assignee" class="User" column="assignee_id"/>
    
    <set name="observers" table="user_task" lazy="true" inverse="true">
      <key>
        <column name="task_id" not-null="true"/>
      </key>
      <many-to-many class="User">
        <column name="user_id" not-null="true"/>
      </many-to-many>
    </set>
    
  </class>
</hibernate-mapping>
Show
Felipe Nascimento added a comment - The workaround is to use Hibernate HBM files instead of GORM. For the example above, the hbm files would be: <hibernate-mapping> <class name= "User" table= "appuser" > <id name= "id" column= "id" unsaved-value= "null" > <generator class= "native" > </generator> </id> <version name= "version" column= "version" type= "java.lang.Long" /> <property name= "name" column= "name" not-null= "true" /> <set name= "observedTasks" table= "user_task" lazy= "true" cascade= "save-update" > <key> <column name= "user_id" not-null= "true" /> </key> <many-to-many class= "Task" > <column name= "task_id" not-null= "true" /> </many-to-many> </set> </class> </hibernate-mapping> <hibernate-mapping> <class name= "Task" table= "task" > <id name= "id" column= "id" unsaved-value= "null" > <generator class= "native" > </generator> </id> <version name= "version" column= "version" type= "java.lang.Long" /> <property name= "name" column= "name" not-null= "true" /> <many-to-one name= "reporter" class= "User" column= "reporter_id" /> <many-to-one name= "assignee" class= "User" column= "assignee_id" /> <set name= "observers" table= "user_task" lazy= "true" inverse= "true" > <key> <column name= "task_id" not-null= "true" /> </key> <many-to-many class= "User" > <column name= "user_id" not-null= "true" /> </many-to-many> </set> </class> </hibernate-mapping>
Hide
Permalink
Graeme Rocher added a comment -

There is no scope / time to resolve these remaining lower priority issues for 1.2 so moving to 1.3

for 1.2 final only issues considered blocking will now be fixed

Show
Graeme Rocher added a comment - There is no scope / time to resolve these remaining lower priority issues for 1.2 so moving to 1.3 for 1.2 final only issues considered blocking will now be fixed
Hide
Permalink
Thomas Baur added a comment - - edited

I'd like to push this a little bit.
Suppose I have two domain classes:

class Developer {
    String name
    
    static hasMany = [projects: Project]
}

class Project {
    String name
    
    static belongsTo = Developer
    static hasMany = [members: Developer]
}

then the generated DDL is correct, it generates the required m:n table with the correct foreign keys.

Now if I decide, that each Developer should have a main project,

class Developer {
    String name
    Project mainProject
    
    static hasMany = [projects: Project]
}

then the generated DDL becomes:

create table developer (
	id bigint generated by default as identity (start with 1), 
	version bigint not null, 
	main_project_id bigint not null, 
	name varchar(255) not null, 
	primary key (id));
create table project (
	id bigint generated by default as identity (start with 1), 
	version bigint not null, 
	name varchar(255) not null, 
	primary key (id));
create table developer_projects (
	developer_id bigint not null, 
	project_id bigint not null, 
	primary key (developer_id, project_id));
create table developer_main_project (
	project_id bigint not null, 
	main_project_id bigint not null);
alter table developer add constraint FKFB30E7CA1548DDDF foreign key (main_project_id) references project;
alter table developer_main_project add constraint FKA25128E8DAE22B99 foreign key (project_id) references project;
alter table developer_main_project add constraint FKA25128E8688C56D0 foreign key (main_project_id) references developer;
alter table developer_projects add constraint FK6BB4854FDAE22B99 foreign key (project_id) references project;
alter table developer_projects add constraint FK6BB4854F3BFFE39 foreign key (developer_id) references developer;

which acutally means that additionally to the correct foreign key in the developer table a second m:n table is generated.

Now if I instead decide that each Project should have a lead developer,

class Project {
    String name
    Developer leadDeveloper

    static belongsTo = Developer
    
    static hasMany = [members: Developer]
}

what happens is this:

create table developer (
	id bigint generated by default as identity (start with 1), 
	version bigint not null, 
	name varchar(255) not null, 
	primary key (id));
create table project (
	id bigint generated by default as identity (start with 1), 
	version bigint not null, 
	lead_developer_id bigint not null, 
	name varchar(255) not null, 
	primary key (id));
create table developer_projects (
	developer_id bigint not null, 
	lead_developer_id bigint not null, 
	project_id bigint not null,
	primary key (developer_id, lead_developer_id));
alter table developer_projects add constraint FK6BB4854FDAE22B99 foreign key (project_id) references project;
alter table developer_projects add constraint FK6BB4854F8018254B foreign key (lead_developer_id) references project;
alter table developer_projects add constraint FK6BB4854F3BFFE39 foreign key (developer_id) references developer;
alter table project add constraint FKED904B19D35B9E3C foreign key (lead_developer_id) references developer;

Again, the project table receives the correct foreign key, but this time an additional foreign key is added to the m:n table.

So the behavior obviously changes depending on whether the additional 1-many relationship is added to the owner side of the m:n relationship or the owned side. But I think both behaviors are not really correct.

Show
Thomas Baur added a comment - - edited I'd like to push this a little bit. Suppose I have two domain classes: class Developer { String name static hasMany = [projects: Project] } class Project { String name static belongsTo = Developer static hasMany = [members: Developer] } then the generated DDL is correct, it generates the required m:n table with the correct foreign keys. Now if I decide, that each Developer should have a main project, class Developer { String name Project mainProject static hasMany = [projects: Project] } then the generated DDL becomes: create table developer ( id bigint generated by default as identity (start with 1), version bigint not null , main_project_id bigint not null , name varchar(255) not null , primary key (id)); create table project ( id bigint generated by default as identity (start with 1), version bigint not null , name varchar(255) not null , primary key (id)); create table developer_projects ( developer_id bigint not null , project_id bigint not null , primary key (developer_id, project_id)); create table developer_main_project ( project_id bigint not null , main_project_id bigint not null ); alter table developer add constraint FKFB30E7CA1548DDDF foreign key (main_project_id) references project; alter table developer_main_project add constraint FKA25128E8DAE22B99 foreign key (project_id) references project; alter table developer_main_project add constraint FKA25128E8688C56D0 foreign key (main_project_id) references developer; alter table developer_projects add constraint FK6BB4854FDAE22B99 foreign key (project_id) references project; alter table developer_projects add constraint FK6BB4854F3BFFE39 foreign key (developer_id) references developer; which acutally means that additionally to the correct foreign key in the developer table a second m:n table is generated. Now if I instead decide that each Project should have a lead developer, class Project { String name Developer leadDeveloper static belongsTo = Developer static hasMany = [members: Developer] } what happens is this: create table developer ( id bigint generated by default as identity (start with 1), version bigint not null , name varchar(255) not null , primary key (id)); create table project ( id bigint generated by default as identity (start with 1), version bigint not null , lead_developer_id bigint not null , name varchar(255) not null , primary key (id)); create table developer_projects ( developer_id bigint not null , lead_developer_id bigint not null , project_id bigint not null , primary key (developer_id, lead_developer_id)); alter table developer_projects add constraint FK6BB4854FDAE22B99 foreign key (project_id) references project; alter table developer_projects add constraint FK6BB4854F8018254B foreign key (lead_developer_id) references project; alter table developer_projects add constraint FK6BB4854F3BFFE39 foreign key (developer_id) references developer; alter table project add constraint FKED904B19D35B9E3C foreign key (lead_developer_id) references developer; Again, the project table receives the correct foreign key, but this time an additional foreign key is added to the m:n table. So the behavior obviously changes depending on whether the additional 1-many relationship is added to the owner side of the m:n relationship or the owned side. But I think both behaviors are not really correct.
Hide
Permalink
Thom Pischke added a comment -

+1. We have the same bug in our project. A description, similar to those above, can be found here in the forums:

http://grails.1312388.n4.nabble.com/Strange-mapping-table-with-many-to-may-relation-td4309458.html

Show
Thom Pischke added a comment - +1. We have the same bug in our project. A description, similar to those above, can be found here in the forums: http://grails.1312388.n4.nabble.com/Strange-mapping-table-with-many-to-may-relation-td4309458.html
Hide
Permalink
Thom Pischke added a comment -

GRAILS-5303 looks like a duplicate of this

Show
Thom Pischke added a comment - GRAILS-5303 looks like a duplicate of this

People

  • Assignee:
    Unassigned
    Reporter:
    Felipe Nascimento
Vote (9)
Watch (9)

Dates

  • Created:
    Updated:
    Last Reviewed: