CODEWITHSUNDEEP
grailsgrails-orm
Rob Bugh
Rob Bugh

Reputation: 71

How to map a joinTable with a String foreign key

Having problems setting up a JoinTable in Grails 3.

I have two domain classes (A and B) with a many-to-many relationship to each other. In Grails 3 I have defined the domains with a JoinTable mapping. In the database (MySql v5) I have three tables, A, B, and the join table A_B. The B table uses a "varchar" as the primary key sqlType. Table A uses "int" as the primary key sqlType.

I added an A and B to my database along with an entry in a join table to link the two together.

In my test code, when I try to load the instance of A, A.get(id), I get an error indicating the type of the ForeignKey in the JoinTable is unknown.

Domain A:

   static hasMany = [bs: B]
   static mapping = {
       table "A"

       id column: 'id', sqlType: 'int'
       bs joinTable: [name: "A_B", key: "a_id"]
   }

Domain B:

   static hasMany = [as: A]
   static mapping = {
       table "B"

       id column: 'id', sqlType: 'varchar'
       as joinTable: [name: "A_B", key: "b_id"]
   }

So it seems the "varchar" in B or the foreignKey to B in the join table is being interpreted as a Long.

I was able to get around the problem by adding a third domain for the JoinTable, defining the columns.

Domain AB
   Long aId
   String bId

   static mapping = {
       table "A_B"

        id composite: ['aId', 'bId']
        aId column: 'a_id', sqlType: 'int'
        bId column: "b_id", sqlType: 'varchar'
   }

Seems like I should be able to set up a JoinTable in Grails 3 without having to define the JoinTable as a domain. Does anyone know how to do this?

Upvotes: 1

Views: 195

Answers (1)

Rob Bugh
Rob Bugh

Reputation: 71

OK, I solved the issue. It was a oversight on my part.

All it took was to define the 'id' type in domain B, e.g.,

Domain B:

   String id         // <= Declare the id.
   static hasMany = [as: A]
   static mapping = {
       table "B"

       id column: 'id', sqlType: 'varchar'
       as joinTable: [name: "A_B", key: "b_id"]
   }

Upvotes: 2

Related Questions