One-To-One mapping in Grails

Question

Can someone please help me to figure out how to create domain classes for simple one-to-one mapping in grails !

let say we have 2 tables (oracle):

create table table_a(long_common_id_name number(5) primary key using index, 
         notes varchar2(10 byte), 
         update_seq number(3)not null );

create table table_b (long_common_id_name number(5) primary key using index, 
          extra_notes varchar2(200 byte), 
          update_seq number(3) not null);

alter table table_b add (constraint table_b_fk foreign key (long_common_id_name)
references table_a (long_common_id_name));

I created 2 domain classes:

class TableA {
    static mapping = {
        table 'table_a'
        columns {
            id                  column:'LONG_COMMON_ID_NAME'
            data                column:'NOTES'
            version             column:'UPDATE_SEQ'
        }
    }
    String data
    TableB extraData
}

class TableB {
    static mapping = {
        table 'table_b'
        columns {
            id                  column:'LONG_COMMON_ID_NAME'
            data                column:'EXTRA_NOTES'
            version             column:'UPDATE_SEQ'
        }
    }
    String data
}

This particular definition is not correct. Grails (or Hibernate) builds an incorrect SQL for TableA :

select this_.LONG_COMMON_ID_NAME as LONG1_66_0_, this_.UPDATE_SEQ as UPDATE2_66_0_, this_.NOTES as NOTES66_0_, this_.extra_data_id as extra4_66_0_ from table_a this_

I've tried many things: belongsto , hasManey with unique but nothing seems to work.

Thanks in advance.

Answer 1

Do you have to set up the foreign key in that way? I'd expect a TABLE_B_ID column on TABLE_A.

Anyway, I think to do what you want you'll need to add a line to the mapping closure for extraData.

I'm not sure if this will play nicely with it being the primary key as well

e.g.

 static mapping = {
    extraData column:'LONG_COMMON_ID_NAME'
}

Answer 2

Agree with leebuts, Grails will expect a separate FK ID field in the subsidiary table.

This table setup looks more like table-per-class inheritance. You might be able to create a class TableB that inherits class TableA, where TableA contains Notes and TableB contains "data". Grails will probably also want a discriminator column in TableB, but otherwise it looks like the tables would conform to Grails conventions for inheritance.

Take a look: http://grails.org/GORM+-+Mapping+DSL

Look for the way to turn off table-per-hierarchy

Answer 3

Answering my own question, maybe someone will find it useful ...

I'm writing a quick web-ui against our legacy database, so instead of writing lots of SQLs to answer user questions I want automate it. I can not modify data structure and access through UI will be read only.

Here is solution/workaround that I can live with:

domain/TableA.groovy

class TableA {
    static mapping = {
        table 'table_a'
        columns {
            id                  column:'LONG_COMMON_ID_NAME'
            data                column:'NOTES'
            version             column:'UPDATE_SEQ'
        }
    }    
    // will manually handle persistence of TableB
    static transients = [ 'extraData' ]

    String data
    TableB extraData
}

---

domain/TableB.groovy

class TableB {
    static mapping = {
        table 'table_b'
        columns {
            id                  column:'LONG_COMMON_ID_NAME'
            data                column:'EXTRA_NOTES'
            version             column:'UPDATE_SEQ'
        }

        //actual id is copied from TableA after it is persisted
        id generator:'assigned'
    }

    static transients = [ "parent" ]

    String data
    TableA parent
}

---

the form in "views/tableA/create.gsp" has input fields for TableA and TableB properties, so I will get data for TableA and TableB instances

---

In the "controllers/TableAController.groovy" I manually update/delete/load TableB instance

def list = {    
   ...
   def rv = TableA.list( params)
   rv.each() { it.extraData = TableB.get(String.valueOf(it.id)) }
   ...
}

def show = {
   ....
   tableAInstance.extraData = TableB.get(tableAInstance.id)
   ...
}
def delete = {
   ...
   // delete tableB instance first before tableA
   def tb = TableB.get(tableAInstance.id)
   if (tb) tb.delete()

   tableAInstance.delete()
   ...
}

def save = {
   ....
   // after tableAInstance.save() call
   def tb = new TableB()
   tb.data = params.extraData?.data
   // copy id from tableA instance
   tb.id = tableAInstance.id
   tb.save()
   .....
}