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

Question

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

Consider 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

Reflation

Answer 1

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

}

Palpitation

Answer 2

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

Tanto

Answer 3

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

Barnwell

Answer 4

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

Reflation

I would say that for many applications, the database structure being created by Grails doesn't matter because it's fast enough and there's not enough data to require database tuning. I'm working on a Grails app where I haven't even seen the DB structure yet (using in-memory DB). However, when you know that your Grails app will be stressful to the DB, then you better tweak it right from the start, changing domain classes later will be painful otherwise. – Ramble

Why are you specifying the id columns? You should remove the mapping of the id columns entirely. – Abstriction

If you can tell me why I should be doing that it would help my understanding, as I said I'm new to grails. Many developers completely disregard the database and treat it as some dumb storage layer. Consider when the application is live and data starts to accumulate. I want a nice and understandable database design that I can write reports against easily. The reason for the link table is that I want to store an item like a quanity inline with the fields that link the tables together. Consider an order line that makes up an invoice. Hope that all makes sense. – Reflation

Answer 5

Briefly, if Child has exactly one Parent, then in Parent, you put

static hasMany = [children: Child]

and in Child, you put

static belongsTo = [parent: Parent]

(or if you don't want cascading, I think just added "Parent parent" would be sufficient, but this is a rarer case I'd guess)

This creates a bi-direction relationship, in that in a child domain object, you can access the parent object through the property, and in a parent object, you have the children collection.

Normally, this will create a column in Child, holding the id for its Parent. This cannot be the case for many to many, when some sort of link table will be required (and GORM can handle this). So called "one sided" relationships (as you have in your first example) can also create link tables, which is explained a bit by this:

http://grails.1312388.n4.nabble.com/Many-to-many-vs-Many-to-one-td1369336.html

Joeannjoed