Grails GORM database mapping for unidirectional 1:n relationship

Question

I am currently trying to create a new Grails application based on a legacy MySQL database. The application should only read information. The concrete DB schema uses for the specific domain classes a table per class hierarchy structure, as well as a property class to add new needed information to these classes.

Currently I cannot retrieve the property information for a transation. There is no exception, but I also cannot access the field properties. One problem I may face is, that the word properties is a keyword for Grails for the domain fields. But I need to use it because of the specific legacy table naming.

The legacy tables are named transaction and transaction_properties. One transcation can have muliple transaction_properties. The association is done via the key transaction_id in the transaction_properties table.

Table transaction

id  bigint(20)
transaction_id  varchar(255) (bad naming here, transaction_id is used to store additional meta information)

Table transaction_properties

transaction_id  bigint(20) -> referencing to transation.id
property_value  varchar(255)
property_key    varchar(32)
etc.

Domain class Transaction

class Transaction {

static hasMany = [properties : TransactionProperty]

static constraints = {
    //   transactionProperty unique: true
}

static mapping = {
    table "transaction"
    version false
    columns {
        id column : "id"
        beginDate column : "start"
        endDate column : "end"
        type column : "DTYPE"
        amount column : "total_amount"
        metaId column : "transaction_id"
        purchase column : "purchase_id"
        service column : "service_id"
        origin column : "origin_id"
        properties column : "id"
    }

}

Long id
Date beginDate
Date endDate
String type
String amount
String metaId

Purchase purchase
Origin origin
Service service
  etc.
  }

Domain class TransactionProperty

  class TransactionProperty {

static mapping = {
    table "transaction_properties"
    version false
    columns {
        id name : "transaction_id"
        key column : "property_key"
        value column : "property_value"
    }
}

String value
String key
Long id

def asString(){
    return "${key} = ${value}"
}
   }

Can't you just change hasMany = [properties: ...] to [transactionProperties: ...]? You don't have any columns mapped to the word "properties". It just tells Grails what the name of the collection should be. — Gregg
When I do so, I get the following Exception. Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'kt-testing.transaction_transaction_properties' doesn't exist — user1829893

James Kleeh James Kleeh · Accepted Answer · 2012-12-17T20:13:13

Your code is a giant mess.

You need to add a static belongsTo = [transaction: Transaction] in your TransactionProperty domain class. This will tell grails to use the foreign key in that table instead of wanting a join table.

You also do not need to specify Long id in either table. That is done by default in Grails.

Remove these column mappings as well in the Transaction domain class:

id column : "id"
properties column : "id"

In TransactionProperty, it technically does not have a primary key (unless you are hiding it from us), so you will have to use a composite key of property_key and property_value.

id name : "transaction_id"

should be:

id composite: ['key', 'value']

Read up on the additional requirements of this here: http://grails.org/doc/latest/guide/GORM.html#5.5.2.5%20Composite%20Primary%20Keys

My best attempt at fixing your classes:

Transaction:

class Transaction {

static hasMany = [properties : TransactionProperty]

static constraints = {
}

static mapping = {
    table "transaction"
    version false
    columns {
        beginDate column : "start"
        endDate column : "end"
        type column : "DTYPE"
        amount column : "total_amount"
        metaId column : "transaction_id"
        purchase column : "purchase_id"
        service column : "service_id"
        origin column : "origin_id"
    }

}

Date beginDate
Date endDate
String type
String amount
String metaId

Purchase purchase
Origin origin
Service service

}

TransactionProperty:

import org.apache.commons.lang.builder.HashCodeBuilder

class TransactionProperty implements Serializable {

static belongsTo = [transaction: Transaction]

static mapping = {
    table "transaction_properties"
    version false
    columns {
        key column : "property_key"
        value column : "property_value"
    }
}

String value
String key


def toString(){
    return "${key} = ${value}"
}

boolean equals(other) {
    if (!(other instanceof TransactionProperty)) {
        return false
    }

    other.key == key && other.value == value
}

int hashCode() {
    def builder = new HashCodeBuilder()
    builder.append key
    builder.append value
    builder.toHashCode()
}
}

Grails GORM database mapping for unidirectional 1:n relationship

1 Answers