CODEWITHSUNDEEP
grailsgrails-orm
jonnybot
jonnybot

Reputation: 2453

Putting entries from a list property into a statically mapped joinTable

I have a domain class called FoapRequest. I want one of the properties called "approver" to be a list of integers. Order matters, so I've defined the class as described by http://grails.org/doc/latest/guide/GORM.html#sets,ListsAndMaps as a list:

class FoapRequest {
    Integer requester
    Integer subject
    List approver
    static hasMany = [foap:FOAP, newFoap:NewFoap, approver:Integer]
...

Just for clarification, FOAP and NewFoap are two other domain objects.

I need to map this class to a particular table in the Oracle database, so I also specify a static mapping with a join table:

static mapping = {
    table 'OUR_SCHEMA.FOAP_REQUEST_TABLE
    id column : 'ID',  generator:'sequence', params: [sequence:'OUR_SCHEMA.FOAP_REQUEST_SEQ']
    requester column : 'REQUESTER'
    subject column : 'SUBJECT'
    approver indexColumn: [name: "APPROVER_IDX"], generator:'sequence', params: [sequence:'OUR_SCHEMA.APPROVER_SEQ'], 
    joinTable: [name:'OUR_SCHEMA.APPROVER_TABLE',
    key: 'ASSOCIATED_REQUEST',
    column: 'APPROVER_PIDM',
    type: "integer"
    ]

However, when I try to create a new instance of the FoapRequest object, I get the following error: Invalid column type

The console displays the following: Error 2012-08-01 12:29:31,619 [http-bio-8080-exec-9] ERROR errors.GrailsExceptionResolver - SQLException occurred when processing request: [POST] /FOAPauth/foapRequest/saveFoapRequests - parameters:

I am certain that the issue lies with the jointable. The domain model didn't include the joinTable originally- approver was just an Integer type (I realized too late that I was going to need to track multiple approvers).

Here's the SQL for creating the APPROVERS table:

CREATE TABLE "OUR_SCHEMA"."APPROVER_TABLE"
    (
    "APPROVER_IDX"        NUMBER(*,0) NOT NULL ENABLE,
    "ASSOCIATED_REQUEST"  NUMBER(*,0) NOT NULL ENABLE,
    "APPROVER_PIDM"       NUMBER(8),
    );

I'd prefer to avoid creating an Approver domain class if at all possible, since all I really need to keep track of are the integer identifiers.

Upvotes: 2

Views: 437

Answers (1)

jonnybot
jonnybot

Reputation: 2453

So, after much janking with join tables, I determined that the best way to deal with my needs was to simply create an Approver object in my domain model.

class Approver {

Integer pidm
String  approvalDecision
Date lastUpdated
Date dateCreated

static belongsTo = [foap: FOAP]
}

To be honest, I'm not really sure why I was trying so hard to avoid this. Possibly because my DBAs use a version control system for table definitions that I find a hair annoying. :)

Regardless, a simple one-to-many relationship between domain classes met all my needs, no join table required.

For those who are still burning to know, I did manage to get a statically mapped join table working using a Map, which was more appropriate for my needs (though not as appropriate for them as a new domain class, and not nearly as simple).

I ended up doing it in a different domain object- FOAP instead of FoapRequest:

import java.util.Map
class FOAP {
...
Map approvalData
...
static mapping = {
    table 'OURSCHEMA.FOAP_TABLE'
    id column : 'ID',  generator:'jpl.hibernate.util.TriggerAssignedIdentityGenerator'
    fund column : 'FUND'
    org column : 'ORG'
    chartOfAccounts column : 'CHART_OF_ACCOUNTS'
    permissionType column: 'PERMISSION_TYPE'
    foapRequest column: 'REQUEST_ID'
    version column : 'VERSION'
    approvalData joinTable: [name:'OURSCHEMA.FOAP_APPROVERS',
        key: 'FOAP'
    ]
}

For the table definition, I used the column names similar to those in my original question. 

CREATE TABLE "OUR_SCHEMA"."APPROVER_TABLE"
(
"FOAP"          NUMBER(*,0) NOT NULL ENABLE,
"APPROVER_IDX"  VARCHAR2(255),
"APPROVER_DLT"  NUMBER(8),
);

The IDX column was the map object's key, the DLT column its value. I'd recommend against this approach, for anyone who can avoid it. Creating a new domain object is much simpler.

Upvotes: 1

Related Questions