Possible to use Grails derived property to retrieve domain object?

Question

I have domain classes for a Request, a RequestSet (a collection of individual requests submitted by a single user at the same time) and a request Status. I'd like to aggregate the statuses on the requests so that a request set has a status that is determined from the state of its individual requests.

The relevant parts of these domain classes look like this:

class RequestSet {
    Long id
    static hasMany = [requests: Request]
}

class Request {
    Long id
    Status status
}

class Status {
    Long id
    String status
}

I tried adding a derived status property to the RequestSet class like so:

class RequestSet {
    Long id
    Status status
    static hasMany = [requests: Request]

    static transients = ['status']

    static mapping = {
        status formula: '(SELECT MIN(status_id) FROM (SELECT status_id FROM request rq WHERE rq.request_set_id = id))'
    }
}

When I run my app and inspect the properties of the requestSet objects, however, the statuses are all null.

Is what I'm trying to accomplish even possible using derived properties? I could settle for a transient property with a getter but I'd like to be able to use dynamic finders to retrieve requestSets based on their bubbled-up statuses. All the examples I've seen use the result of the formula as is; I'd like to know if I can somehow get Grails to interpret the returned value as an ID for the Status class.

EDIT, 7 Dec 2016: Note that I marked the derived status property as transient. Without doing so I get a ClassCastException from Hibernate.

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'transactionManagerPostProcessor': Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'transactionManager': Cannot resolve reference to bean 'transactionManager_companion' while setting constructor argument with key [1]; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'transactionManager_companion': Cannot resolve reference to bean 'sessionFactory_companion' while setting bean property 'sessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory_companion': Invocation of init method failed; nested exception is java.lang.ClassCastException: org.hibernate.mapping.Formula cannot be cast to org.hibernate.mapping.Column
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)

I suspect even if the formula works I wouldn't be able to use dynamic finders with this property because it's marked as transient. I'll settle for using the formula to compute the id of the status but I'm still having issues with that.

Now my domain class looks like this:

class RequestSet {
    Long id
    Long statusId
    static hasMany = [requests: Request]

    static mapping = {
        statusId formula: '(SELECT MIN(status_id) FROM (SELECT rq.status_id FROM request rq WHERE rq.request_set_id = id))'
    }
}

The SQL generated by the formula when fetching the request sets is

(SELECT MIN(this_.status_id) FROM (SELECT rq.status_id FROM request rq WHERE rq.request_set_id = this_.id)) as formula3_0_ from request_set

and I'm getting an SQLSyntaxErrorException: ORA-00904: "THIS_"."STATUS_ID": invalid identifier. How can I rewrite the formula so that 'this_.' is not prepended to the status_id?

Answer 1

In response to my original question, no, you can't use a formula to derive a property that is another domain class. My numerous failed attempts to do so have led me to this conclusion and the author of this blog seems to think so too. Specifically, "derived properties can be of any type supported by Hibernate" and my Status class is not.

As for my follow-up question, the formula I was using is not supported by HQL according to this answer for a different question. I managed to get the statusId as a derived property using this formula instead:

(SELECT MIN(status.id) FROM status WHERE status.id IN (SELECT rq.status_id FROM request rq WHERE rq.request_set_id = id))

Thanks @Mike for the suggestion to turn on SQL logging. That really helped to figure out what was going on.