Domain Object Many-To-Many Relationship in Grails GORM and SQL Relational DB

This is following post series about ‘Domain Object Relationships in Grails GORM and SQL Relational Databases‘ where we look at the details how the relationships build in GORM with how physically structrured in DB while cascading and direction addressed.

In this post, i have a sample of Many-To-Many relationship between entities ‘RentalUnit’ and ‘Review’. A particular RentalUnit may have many Reviews. One guest my have stayed in multiple RentalUnits, so a review written by this guest is linked to many RentalUnits

In our example, there is ‘Save, Update and Delete’ cascading from RentalUnit to Review and ‘none’ cascading from Review to RentalUnit

Class Diagram

From class diagram you can see there is ‘has to’ relationship between RentalUnit and Review entities

Code Snippet

In the above code snippet, the red colored code sets the relationship Many-To-Many. The green colored code sets the cascading

Database Structure

From the ER Diagram above, we see that GORM creates a separated table – RentalUnit_Review that is a child to parent ‘RentalUnit’ and another parent ‘Review’ tables. The IDs of entities ‘Review’ and ‘RentalUnit’ combines the primary key for the new table

Summarized

Our conclusion that GORM creates additional table every single time there are Many-To-Many or One-To-Many relationship.

Other

1. addTo* And removeTo* methods.

Whenever there are Many-To-Many or One-To-Many relationship, GORM provides you with methods – addTo* and removeTo*. These methods does important part – it sets the relationships in both ways. Consider the following:

def review2 = new Review(rentalUnits: [rentalUnit], ...., isApproved: false).save(flush: true) 
review2.addToRentalUnits(rentalUnit2)

In the above code we initialize object ‘review2’ that points to object ‘rentalUnit’ and ‘rentalUnit2’, however.  While object ‘rentalUnit2’ is pointing back to object ‘review2’, the object ‘rentalUnit’ does not. This is because adding object ‘rentalUnit2’ we used method ‘addTo*’ where as for object ‘rentalUnit’ we did not. As result we have incomplete session.

2. “deleted object would be re-saved by cascade”

Entities like ‘Review’ that have references from multiple ‘RentalUnit’ objects while no cascading applied, will cause errors if not handled with care. For example, if you have to delete a review object R1 that is contained by two rentalUnit objects A and B, then you have to first delete the references from A->R1 and B->R1 before deleting the review – R1 itself. Here is example

def r = []
 r += review1.rentalUnits
 r.each {RentalUnit renUnit ->
     renUnit.removeFromReviews(review1)
 }
 review1.delete(flush: true)

In another words, we are doing cascading manually and should not be needed if we have specified cascading from Review to RentalUnit.

If we don’t handle these RentalUnit references as shown above before deleting the review R1, we will receive an error – deleted object would be re-saved by cascade – and the review R1 is not removed. This because GORM deletes the review R1 in session and before flushing, it detects that there exists other references to this review R1, thus , it cannot be remove, instead restores (re-saved) the review R1 back in session.

3. Cascading gets tricky in Many-To-Many relationship

Consider a RentalUnits U1 and U2 with reviews R1 and R2 with associations as following:

U1 -> R1,R2
U2 ->R2

Now, if you wish to remove rentalUnit U1, it will fail even with cascading on ‘delete’ present from RentalUnit->Review as described above. It fails because GORM is executing cascading on all children of rentalUnit that includes all reviews, so it attempts to delete the review R2, however. The review R2 is also referenced by another rentalUnit U2 resulting GORM unable to remove R2, and also U1 as the result.

Leave a Reply

Your email address will not be published. Required fields are marked *