GORM Domain Mapping Issue

Question

I've got a bit of a complicated domain model I'm trying to implement and I'm having some trouble. (On top of that, I'm quite new to all this!)

I have a User domain which has multiple roles and multiple tests. The Role domain works great. The Test domain is a bit more compilciated though because it requires two foreign keys instead of just 1 like in the Role domain. The first foreign key is the user_id and the second is a uni_id (university ID).

The User domain model contains the following

class User {

    static hasMany = [roles:Role, tests:Test]

    Integer userId
    ...

    static mapping = {
        table 'user_data'
        id generator: 'assigned', name: 'userId', type: 'long'
        userId column: 'user_id'
        version false

        roles joinTable:[name:'user_role', key:'user_id']

        tests joinTable:[name:'user_test', key:'user_id'] // Here is where I run into trouble
    }

    static constraints = {
    }
}

The Test domain contains

class Test {

    static belongsTo = User
    static hasMany = [users:User]
    static hasOne = [uni:Uni]

    Integer testId // primary key
    String testType 

    static mapping = {
        table 'test'
        id generator: 'assigned', name: 'testId', type: 'long'
        testId column: 'test_id'
        users joinTable:[name:'user_test', key:'test_id']
        uni joinTable:[name:'user_test', key:'test_id'] // If I leave this out, everything is groovy
        version false
    }

    static constraints = {
    }
}

and the Uni domain contains

class Uni {

    static belongsTo = Test
    static hasMany = [tests:Test]

    Integer uniId // primary key
    String shortName 
    String fullName 

    static mapping = {
        table 'uni'
        id generator: 'assigned', name: 'uniId', type: 'long'
        uniId column: 'uni_id'
        version false

        tests joinTable:[name:'user_test', key:'uni_id']
    }

    static constraints = {
    }
}

If its not clear, what I'm trying to do is pull in the University ID, Test ID, and User ID to a table user_test to find based on the User ID which tests they have taken. Is there a simple way to do this?

The kinds of errors I'm getting lead me to believe that for some reason it is trying to perform all actions on the table test instead of user_test. For example,

Unsuccessful: alter table test add uni_id int not null

I'd like to be able to access the test and university information corresonding to the specific user via user.tests.testType and user.tests.uni.fullName or something to that extent. What am I doing wrong? More importantly, is there a better way to do this?! Thanks in advance!

Edit 1: something interesting I just thought of.. a user can have multiple tests, but the inverse isn’t true. A given test will never be shared among multiple people. So I think that changes things a bit.. I'll do some reading and post if I come up with anything new.

Edit 2: Here's the Role domain

class Role {

  static belongsTo = User
  static hasMany = [users:User]

  Integer roleId
  String shortName
  String roleName
  Integer roleLevel

  static mapping = {
    table 'role'
    id generator: 'assigned', name: 'roleId', type: 'long'
    roleId column: 'role_id'
    users joinTable:[name:'user_role', column:'user_id', key:'role_id']
    version false
  }

    static constraints = {
    }
}

Edit 3: I am now trying to store all test information in the Test domain model and simply choose the Uni name to store as a field in Test, but am getting weird errors when I try this. My new files look like this

class User {

    static hasMany = [roles:Role, tests:Test]

    Integer userId

    static mapping = {
        table 'user_data'
        id generator: 'assigned', name: 'userId', type: 'long'
        userId column: 'user_id'
        version false

        roles joinTable:[name:'user_role', key:'user_id']
    }

    static constraints = {
    }
}

and

class Test {

    static belongsTo = User

    Integer testId // primary key
    Integer testTypeId
    String testTypeName
    String testUni
    Date testDate

    static mapping = {
        table 'test'
        id generator: 'assigned', name: 'testId', type: 'long'
        testId column: 'test_id'
        version false
    }

    static constraints = {
    }
}

but now I'm getting the following error when I try to run it Caused by: org.hibernate.MappingException: Missing type or column for column[tests_test] on domain[User] referencing[Test]

Any idea what that's about?

Answer 1

Finally got everything working (after a bit of modification in terms of the domain model)

class User {

    static hasMany = [roles:Role, tests:Test]

    Integer userId

    static mapping = {
        table 'user_data'
        id generator: 'assigned', name: 'userId', type: 'long'
        userId column: 'user_id'
        version false

        roles joinTable:[name:'user_role', column:'role_id', key:'user_id']
    }

    static constraints = {
    }
}

and

class Test {

    User user
    Integer testId // primary key
    String testType
    String testUni
    Date testDate

    static mapping = {
        table 'test'
        id generator: 'assigned', name: 'testId', type: 'long'
        testId column: 'test_id'
        version false
    }

    static constraints = {
    }
}

with

class Uni {

    Integer uniId // primary key
    String shortName 
    String fullName 

    static mapping = {
        table 'uni'
        id generator: 'assigned', name: 'uniId', type: 'long'
        uniId column: 'uni_id'
        version false
    }

    static constraints = {
    }
}

So now what I'm doing is selecting the university from a drop down tab in my GSP and just saving it in Test as the string testUni. Then, the big change was removing all joinTables between the three and adding User user to Test. I'm still a little fuzzy on why what I was doing before didn't work, but I won't complain about a working app!