Grails Many-to-Many Relationship with a domain which has String id column

Question

How to create many-to-many relationship with a domain whose id column is a string?

UnitObj value examples are: '0','1','001','002','1234' etc. So I can't make the id column(value column in UnitObj) long or integer because of those values.

Any help would be appreciated. Here are the domains:

class UnitObj {
    String value
    String unit
    UnitObj parent

    static constraints = {
        value(unique: true, nullable: false)
        unit(nullable: false)
        parent(nullable: true)
    }

    static mapping = {
        version false
        id generator: 'assigned', name: "value", type: 'string'
        table name: "unit_obj", schema: "db"
    }
}

Here is the one which I want to change.

class UserUnitObj {
    User user
    String unit  //This should be UnitObj not string
    String subUnit //This should be UnitObj not string

    static constraints = {
       user(nullable: false)
       unit(nullable: false)
       subUnit(nullable: true)
    }
    static mapping = {
       version false
       table name: "user_unit_obj", schema: "db"
    }
}

---------EDIT--------

If I change the strings to UnitObj, when trying to create UserUnitObj it gives :

  UserUnitObj u = new UserUnitObj()
  u.user = User.get(userUnitJson.user)
  u.unit = UnitObj.findByValue(userUnitJson.unit.id.toString())
  u.subUnit = UnitObj.findByValue(userUnitJson.subUnit.id.toString())
  u.save(flush: true)

This is the error:

2016-12-05 17:51:14,305 [http-bio-8080-exec-4] ERROR spi.SqlExceptionHelper  - ERROR: column "sub_unit_id" is of type bigint but expression is of type character varying

Hint: You will need to rewrite or cast the expression.
  Position: 79
Error |
org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not execute statement; bad SQL grammar [n/a]; nested exception is org.postgresql.util.PSQLException: ERROR: column "sub_unit_id" is of type bigint but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.

Answer 1

The problem isn't that you are using a String for an assigned id. The problem is because you haven't told UserUnitObj that you are using value instead of id. By default, GORM will always assign the FK to the column named id. If you do something different, you have to tell it. See the documentation here for instructions on how to do that.