Grails custom names for composite key in many to many relationship in joinTable (legacy db)

Question

grails/gorm seems to ignore column names on join table on many to many relationship if one domain class has composite id, example:

class Following implements Serializable {
    ...
    static hasMany = [operationTypes:OperationType]
    static belongsTo = OperationType
    static mappedBy = [operationTypes:'followings']

    static mapping = {
        ...
        id composite: ['offer', 'user']
        offer column: 'offer_oid'
        user column: 'user_oid'
        operationTypes joinTable: [name: 'operationtype_following', key: ['favorite_user_offer_oid', 'favorite_user_user_oid']]
    }
}

and:

class OperationType implements Serializable {
    ...
    static hasMany = [offers:Offer, advices:Advice, followings:Following]
    static mappedBy = [followings:'operationTypes']

    static mapping = {
        ....
        followings joinTable: [name: 'operationtype_following', key: 'operationtype_oid']
    }
}

Results in: MappingException: Foreign key (FK_lhri681gwbef5a9y6ylhoakpj:operationtype_following [favorite_user_offer_oid, favorite_user_user_oid,Following_offer_id,Following_user_id])) must have same number of columns as the referenced primary key (favorite_user [user_oid,offer_oid])

So why it not really ignores column names but adds generated column names to the specified ones?

Grails 2.4.3 is used. Any help appreciated

We are having the exact same issue - Did you figure this out? — RRK
No, i had to put normal autoincrement id to that table for now. Just to use in the relationships of grails app... — denis111
OK, so you removed the composite key from the "Following" domain? We are planning to keep the composite key as primary identifier and manage the many-to-many relationship ourselves. — RRK
Yes, i set normal integer as id and (ex-)composite columns just as foreign keys... Can't find a solution to keep the composite key :( But at least older code/apps keep working with this db. — denis111

Manuel Niederkofler Manuel Niederkofler · Accepted Answer · 2016-04-19T07:42:33

I was able to use a composite foreign key in a m:m relationship. this using the following mapping syntax. SITE and MACHINE_FK are the attributes of the join table.

static mapping = {
        mstAttributeList {
            column name: 'SITE'
            column name: 'MACHINE_FK', sqlType: 'nvarchar2'             
        }           
        mstAttributeList joinTable: [name: 'MST_MACHINE_ATTRIBUTE', key: ['MACHINE_FK', 'SITE']]
}

static hasMany = [ mstAttributeList : com.gknsintermetals.mda.master.MstAttribute ]

Grails custom names for composite key in many to many relationship in joinTable (legacy db)

1 Answers