sql - GORM Domain Mapping Issue -

i've got bit of complicated domain model i'm trying implement , i'm having trouble. (on top of that, i'm quite new this!)

i have user domain has multiple roles , multiple tests. role domain works great. test domain bit more compilciated though because requires 2 foreign keys instead of 1 in role domain. first foreign key user_id , second uni_id (university id).

the user domain model contains 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 run 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 leave out, groovy         version false     }      static constraints = {     } } 

and 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 not clear, i'm trying pull in university id, test id, , user id table user_test find based on user id tests have taken. there simple way this?

the kinds of errors i'm getting lead me believe reason trying perform actions on table test instead of user_test. example,

unsuccessful: alter table test add uni_id int not null 

i'd able access test , university information corresonding specific user via user.tests.testtype , user.tests.uni.fullname or extent. doing wrong? more importantly, there better way this?! in advance!

edit 1: interesting thought of.. user can have multiple tests, inverse isn’t true. given test never shared among multiple people. think changes things bit.. i'll reading , post if come new.

edit 2: here's 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: trying store test information in test domain model , choose uni name store field in test, getting weird errors when try this. new files 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 i'm getting following error when try run caused by: org.hibernate.mappingexception: missing type or column column[tests_test] on domain[user] referencing[test]

any idea that's about?

ok, 1 issue have you're trying share user-to-test association join table test-to-unit association. that's not going work.

lets @ in database terms. i'm not ascii art expert, hope diagram doesn't make eyes bleed.

user_data (userid) |---|< (user_id) user_test (test_id) >|---| (testid) test 

the diagram above shows database implementation of many-to-many association between user , test domain classes. can see user_data.userid links user_test.user_id , user_test.test_id links test.testid.

now here's starts weird. there 2 different associations between test , uni: bidirectional one-to-one , one-to-many. don't understand that. want illustrate important issue join tables, here is.

test (testid) |---|< (test_id) user_test (uni_id) >|---| (uniid) uni 

because you're using same join table (user_test) 2 different associations you're asking gorm create table this:

user_test - user_id - test_id - unit_id 

gorm won't because join tables supposed have 2 fields. not that, you're defining many-to-many in database terms, , yet bidirectional one-to-one , one-to-many in gorm terms. ouch!

todo

the first change recommend use different join table test-uni association.