Many-to-Many link tables in grails (GORM) / hibernate

Question

Hi Guys, I'm playing aroud with Grails and am finding the ORM stuff tedious because I don't fully understand what I'm doing when it comes to domain classes. I'm hoping someone can put me back on track

Consdier the the following

Test Job One:Many Hardware Used on Job Many:One Physical Hardware

...this is analogous to the classic Order, OrderLine, Product scenario seen in university DB examples

I've created the following domain classes

class Job
{
  String jobName
  String jobDescription
}

class HardwareOnJob
{
   static hasMany = [  jobs:Job, physicalHardware:PhysicalHardware ]
   static belongsTo = Job

   String role
}

class PhysicalHardware
{
  String assetName
  String model
  String os 
}

The question I need to ask is why does Grails create me two extra tables in my database rather than using the link entity/domain class I've defined. For instance Grails creates hardware_on_job_job and hardware_on_job_physical_hardware in the database.

Using the scaffolded controllers I can enter some hardware, enter a job and then enter link the two together. The question I have is why does it create these two extra tables rather than use the domain object (HardwareOnJob) I've specified.

Any help/guidance would be very much appreciated as going nuts looking at this and trying new things. Btw I'm on grails version 1.2.1

Answer 1

When using one to many or many to many relationships grails creates a join table containing the ID's of the objects in the relationship. You can avoid using the join table by telling grails to use a foreign key in a one to many relationship. To my knowledge there is no way to avoid using the automatically created join table in a many to many relationship. For more info see sections 5.2.1.2 and 5.2.1.3 of this as well as this

Answer 2

Ok so after playing around I've come up with the following structure

class Job 
{ 
  String jobName 
  String jobDescription 

  static mapping = {
     id column:"jobId"
  }

  static hasMany = [hardware:HardwareOnJob]
} 

class HardwareOnJob 
{
   String role 
   Job job
   PhysicalHardware hardware


  static mapping = {
     id column:"hardware_on_job_id"
  }

} 

class PhysicalHardware 
{ 
  String assetName 
  String model 
  String os  

  static mapping = {
     id column:"physical_hardware_id"
  }

  static hasMany = [hardwareOnjob:HardwareOnJob]
} 

Does this look sensible to everyone else? The database structure that has been created looks a lot more friendly and has only the three tables I expect.

Interested to hear peoples thoughts as I come from a relational background. I'm looking at the object creation as a means to give a clear db design from the perspective of keeping it simple to report against.

Comments welcome

Answer 3

Have a look at the joinTable keyword which:

Customizes the join table used for undirectional one-to-many, many-to-many and primitive collection types

Here is the example from the user guide:

class Book {
    String title
    static belongsTo = Author
    static hasMany = [authors:Author]

    static mapping = {
        authors joinTable:[name:"mm_author_books", key:'mm_book_id' ]
    }
}
class Author {
    String name
    static hasMany = [books:Book]

    static mapping = {
        books joinTable:[name:"mm_author_books", key:'mm_author_id']
    }

}

Answer 4

consider using an explicit association class/table. see the membership class in http://www.grails.org/Many-to-Many+Mapping+without+Hibernate+XML

a side benefit is scaffolding for the association class (you won't get this without an explicit association class).