Grails GORM - Lookup Table

Question

General Problem Description

I am having problems transfering my Lookup Table into the GORM (Grails Object Relational Mapping).

Domain Classes

I am using Grails 3.1.4 and my Domain classes are MySystem, MyVendor and Lookup:

class MySystem {
    String productName
    MyVendor vendor
    static belongsTo = [myVendor: MyVendor]
    static mapping = {
        id column: "product_id", generator: "assigned"
        table 'my_system_table'
        version false
    }
    static constraints = {
        productName nullable: true
    }
}

class MyVendor {
    String vendorName
    static hasMany = [mySystems: MySystem]
    static mapping = {
        id column: "vendor_id", generator: "assigned"
        version false
    }
    static constraints = {
        vendorName nullable: true
    }
}

class Lookup implements Serializable {
    Integer systemId
    Integer someIdA
    Integer someIdB
    int hashCode() {
        def builder = new HashCodeBuilder()
        builder.append systemId
        builder.append someIdA
        builder.append someIdB
        builder.toHashCode()
    }
    boolean equals(other) {
        if (other == null) return false
        def builder = new EqualsBuilder()
        builder.append systemId, other.systemId
        builder.append someIdA, other.someIdA
        builder.append someIdB, other.someIdB
        builder.isEquals()
    }
    static mapping = {
        id composite: ["systemId", "someIdA", "someIdB"]
        version false
    }
}

Database Tables

The mysystem table looks like this:

mysystem
product_id vendor_id product_name
1           1           ProductA
2           1           ProductB
3           3           ProductC

...         ...         ...
99          3           ProductAB

The product_id column is the primary_key and one product_id can have exacly one Vendor, which is reflected in the GORM by defining static belongsTo = [myVendor: MyVendor] in the MySystem Domain class.

The myvendor table looks like this:

myvendor
vendor_id vendor_name
1           VendorA
2           VendorB
3           VendorC
...         ...

In this table the primary key is vendor_id and the logical link to the mysystem table is not perceivable by looking at this table. It is only through the GORM where the logical link to the mysystem is made by setting static hasMany = [mySystems: MySystem] as a property.

The lookup table looks like this:

lookup
system_id some_id_a some_id_b
3           99          1       
3           88          1
3           22          1   
...         ...         ...
1           77          1
2           88          1
2           22          1

This so called lookup table has these informal meanings:

The system with the id 3 has the capabilities to fulfill tasks with the ids 99, 88, 22
The system with the id 1 has less capabilites. It can only do the task with the id 88
The system with the id 2 can do the tasks with the ids 77 and 22

As you can see in the corresponding Domain Class Lookup the primary key is the tuple over all columns (system_id,some_id_a,some_id_b)

Raw Database Queries

When I want to find systems which are capable of doing the tasks with the ids 22 and 88 then I can query my database like this:

Select system_id from lookup a where some_id_a= 22 and some_id_b= 1 and 
Exists (Select system_id from lookup where 
        some_id_a= 88 and system_id = a.system_id)

And the results with the example set given in this post would be:

system_id
2
3

When I want to find systems which can do the tasks with the ids 99,88,22 then I can do the following query against my database:

Select system_id from lookup a where some_id_a = 99 and some_id_b = 1 and
       Exists (Select system_id from lookup where 
               some_id_a = 88 and system_id = a.system_id) and 
       Exists (Select system_id from lookup where 
               some_id_a = 22 and system_id = a.system_id)

This query will give me:

system_id
3

My raw queries and also the HQL queries from the code against the database work, but i am unable to find the correct GORM property mappings for the Lookup Domain class, so that building GORM queries using criterias work.

Criteria Database Queries

For example I can have this Service class using criterias in the following fashion:

class MySystemService {
    def myMethod() {
        def criteria = MySystem.createCriteria()
        def results = criteria.list {
            eq("productId", 1)
        }
        return results
    }
}

This will simply give me the product with the id 1 from the database.

What I can't do is the following:

class MySystemService {
    def myMethod() {
        def criteria = MySystem.createCriteria()
        def results = criteria.list {
            lookup{
                eq("systemId", 1)
            }
        }
        return results
    }
}

It gives me this error:

groovy.lang.MissingMethodException: No signature of method: grails.orm.HibernateCriteriaBuilder.lookup() is applicable for argument types: (com.my.package.MySystemService$_getByParams_closure1$_closure2$_closure3) values: [com.my.package.MySystemService$_getByParams_closure1$_closure2$_closure3@59b6da8b]

I know that this because I could not wire the Domain classes correctly together with mappings. due to that Grails cannot generate its boilerplate code for the properties of the Domain classes.

Questions

All of this leads to the questions:

How can I wire the Domain classes correctly so that the exemplified raw queries are being picked up by the GORM criterias?
What would the criteria closure -corresponding to the raw queries- look like in the end?

Emmanuel Rosa Emmanuel Rosa · Accepted Answer · 2016-05-20T17:31:56

In order to use GORM as intended, think in terms of object properties, not table columns. In GORM, domain classes are associated by properties, which influences how Hibernate generates the SQL queries. I go into more detail in this article. Here's what you can do:

Domain classes

class MySystem {
    String productName
    MyVendor vendor
    static hasMany = [lookups: Lookup]
    static belongsTo = [myVendor: MyVendor]
    static mapping = {
        id column: "product_id", generator: "assigned"
        table 'my_system_table'
        version false
    }
    static constraints = {
        productName nullable: true
    }
}

class Lookup implements {    
    Integer someIdA
    Integer someIdB
    static belongsTo = [mySystem: MySystem]
    static constraints = {
        someIdA unique: 'someIdB'
    }
}

Example criteria query

class MySystemService {
    def myMethod() {
        def criteria = MySystem.createCriteria()
        def results = criteria.list {
            lookups {
                eq("someIdA", 1)
            }
        }
        return results
    }
}