Mapping Join Table that contains multiple joins

[predhead]

I have a legacy table that acts as a join table between multiple tables. For example if I have tables for people, dogs, cars, houses, etc. then there is just one table "relationships" that maps the many to many relationships for:
people to dogs
people to people
people to cars
cars to houses
etc...

The join table has two ids, as well as a value that indicates the type of relationship, for example:
people_dogs
people_people
people_cars
cars_people
....


I don't mind creating separate domain classes for each relationship if I have to, but how I do I tell it to only pull the values out of the join table based on the "type"?

Is there any way do to this? BTW this data is all read-only, I don't ever do saves, updates or deletes so cascading is irrelevant,.

[burtbeckwith]

If possible you should refactor the table. Since you're mixing types you can't use foreign keys, and even worse - you're most likely doing full table scans for each query because there's no way an index would be used by the query optimizer. If you're using the table for other apps or reporting, you could create a view from the split tables that looks like the current table.

Assuming this isn't possible, you can do this with a somewhat funky domain class for the relationships table. There's no way to do it with regular GORM since you don't have foreign keys.

The domain class has a compound primary key, so it has to implement Serializable and define a hashCode and equals method.

import org.apache.commons.lang.builder.HashCodeBuilder

class Relationships implements Serializable {

   Long leftId
   Long rightId
   String reltype

   static constraints = {
      reltype inList: ['people_dogs', 'people_people', 'people_cars', 'cars_people']
   }

   boolean equals(other) {
      if (!(other instanceof Relationships)) {
         return false
      }

      other.leftId == leftId &&
         other.rightId == rightId &&
         other.reltype == reltype
   }

   int hashCode() {
      def builder = new HashCodeBuilder()
      if (leftId) builder.append(leftId)
      if (rightId) builder.append(rightId)
      if (reltype) builder.append(reltype)
      builder.toHashCode()
   }

   static Set findRelated(instance, String reltype, Class otherType, boolean left) {
      def ids = left ?
         Relationships.findAllByReltypeAndRightId(reltype, instance.id)*.leftId :
         Relationships.findAllByReltypeAndLeftId(reltype, instance.id)*.rightId
      otherType.getAll ids
   }

   static Relationships relate(left, right, String reltype, boolean flush = false) {
      def rel = new Relationships(leftId: left.id, rightId: right.id, reltype: reltype)
      rel.save(flush: flush)
      rel
   }

   static boolean unrelate(left, right, String reltype, boolean flush = false) {
      Relationships instance = Relationships.findWhere(
            leftId: left.id, rightId: right.id, reltype: reltype)
      instance ? instance.delete(flush: flush) : false
   }

   static mapping = {
      id composite: ['leftId', 'rightId', 'reltype']
      version false
   }
}

To relate two instances, you won't have any dynamic methods like person.addToDogs(dog). Instead call the relate() method in Relationships, e.g. Relationships.relate(person, dog, 'person_dog'). To disassociate (the equivalent of removeFrom() use unrelate(). Since the table is read-only you can remove these two methods though.

To find the associated instances for a domain class instance (the equivalent of person.dogs, which returns a Set of Dog instances for a given Person), use the findRelated() method, e.g. Relationships.findRelated(person, 'person_dog', Dog, false). Since everything's weakly defined, you need to specify the reltype string, but also the class of the collection instances, and a boolean indicating which side to look at. If the instance ids are in the 'leftId' column use true, and if they're in the 'rightId' column use false. So to find a dog's people, use Relationships.findRelated(dog, 'person_dog', Person, true).

[predhead]

Unfortunately I can't refactor that table, I can't touch the original data structure at all since we get it from an outside source.

However, what my DBA has done is create views that look like separate join tables for relationships that are queried often and those make GORM happy. It's an extra layer on the database, but it works fine.

I'm going to add your findRelated query for all the other relationships that are only used rarely so that we don't have to create NxN views.

I appreciate your help, this database has been a pain to deal with, and that's only the structure. The actual data stored is even worse.