Grails Relation m:n:n

Question

We work with an existing mysql database under grails which contains a m:n relation. No problem here. But now we are in the situation that i have to add a second n relation entry, which links to the same table and has to be associated to the first n entry.

If it were for the database, I would simply create a table which looks like:

field m_id -> links to m table
field n_id1 -> links to n table
field n_id2 -> links to n table

But how can this be represented in a grails domain class?

Possibly the answer can already be found somewhere, but the searches I did were not successful, maybe due to lack of search term creativity.

EDIT:

Trying to clarify the question: we have a many-to-many relation, but with two items on one side, which have to maintain association to each other (and it also must be clear which is for example the original and which is the replacement item), so they can not be seperated into two separate entries into the relation.

Hmm... try to think of racing car drivers nominating for a series of races, and every nomination has to contain the driver and his substitute. Races would be m (left hand), driver would be n1 and substitute would be n2. (It was really hard work to find an example...)

EDIT:

By coincidence I found this question which addresses the same problem but also left off rather unsolved.

Answer 1

Take a look at the documentation, is pretty clear: http://grails.github.io/grails-doc/2.5.0/guide/GORM.html#gormAssociation

Anyway I'm not sure I understand what you're trying to do, but let's try.

If it's a one-to-one relation, you could simply do something like this:

class DomainA {

    DomainB domainB
    DomainC firstDomainC
    DomainC secondDomainC
}

class DomainB {
}

class DomainC {
}

That would create the following fields in the "domain_a" table:

• "domain_b_id"
• "first_domain_c_id"
• "second_domain_c_id"

If it's a one-to-many relationship from DomainA to both DomainB and DomainC with two differentiated collections of DomainC in DomainA, you must have two different DomainA properties in DomainC to be able to map it.

The example with Airport and Flight in the documentation:

class Airport {
    static hasMany = [outboundFlights: Flight, inboundFlights: Flight]
    static mappedBy = [outboundFlights: "departureAirport",
                   inboundFlights: "destinationAirport"]
}
class Flight {
   Airport departureAirport
   Airport destinationAirport
}

The flight needs to Airport properties in order to be able to distinguish which one is mapping the right hasMany collection in Airport.

EDIT: for the race-driver example

Grails supports many-to-many relations but one of the ends must be the principal one and the other must additionally have a belongsTo to it. Though this is not the case, since neither race belongs to driver nor driver belongs to race. I would use relation with a property: "drives" with a property "mainDriver". That cannot be mapped directly, you need to use a domain for the relation:

class Race {
    static hasMany = [ participants: DriverRace ]

    def mainDrivers() {
        getDrivers( false )
    }

    def substitutes() {
        getDrivers( false )
    }

    private getDrivers( mainDriver ) {
        DriverRace.withCriteria {
            eq( "race", this )
            eq( "mainDriver", mainDriver )
        }.collect { it.driver }
    }
}

class Driver {
    static hasMany = [ races: DriverRace ]
}

class DriverRace {
    static belongsTo = [ race: Race, driver: Driver ]

    boolean mainDriver

}

Answer 2

We thought we were able to solve the problem by inserting a second reference to domain/table n in the mapping property of (left hand side) domain m. grails then seems to put a second reference to n on the right hand side of the relation. But that turned out to be a hopeful weekend dream.