Grails
  1. Grails
  2. GRAILS-2736

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

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.0.2
    • Fix Version/s: None
    • 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

        Hide
        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
        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
        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
        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
        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
        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
        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
        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
          • Votes:
            9 Vote for this issue
            Watchers:
            10 Start watching this issue

            Dates

            • Created:
              Updated:
              Last Reviewed:

              Development