Grails where-query using joda-time

[Hans Bogaards]

Hi All,

A couple of weeks ago I posted the following question on StackOverflow, but haven't gotten any useful replies.
Maybe you can help:
----

We are using Grails 2.3.11 with the joda-time plugin 1.4.

I was trying to use a where query like this:
Form.where { year(dateCreated) == currYear }.list()

In this currYear is an integer and dateCreated is of type org.joda.time.DateTime

The result is this Exception:

org.springframework.dao.InvalidDataAccessResourceUsageException: Unsupported function [year] defined in query for property [datum] with type [class org.joda.time.DateTime]
    at org.grails.datastore.gorm.finders.DynamicFinder.applyDetachedCriteria(DynamicFinder.java:440)
    at grails.gorm.DetachedCriteria.withPopulatedQuery_closure10(DetachedCriteria.groovy:1201)
    at org.grails.datastore.gorm.GormStaticApi.withDatastoreSession_closure23(GormStaticApi.groovy:699)
    at org.grails.datastore.mapping.core.DatastoreUtils.execute(DatastoreUtils.java:302)
    at org.grails.datastore.gorm.AbstractDatastoreApi.execute(AbstractDatastoreApi.groovy:37)
    at org.grails.datastore.gorm.GormStaticApi.withDatastoreSession(GormStaticApi.groovy:698)
    at grails.gorm.DetachedCriteria.withPopulatedQuery(DetachedCriteria.groovy:1185)
    at grails.gorm.DetachedCriteria.list(DetachedCriteria.groovy:845)
    at grails.gorm.DetachedCriteria.list(DetachedCriteria.groovy:844)

It seems that functions like year() are not supported when using joda-time. Is this correct? Or am I doing something wrong?

How hard would it be at add support for this in the joda-time plugin? Or somewhere else?

When I change the dateCreated to a java.util.Date it works correctly.

We have the mapping in our Config.groovy:

// Added by the Joda-Time plugin:
grails.gorm.default.mapping = { "user-type" type: org.jadira.usertype.dateandtime.joda.PersistentDateTime, class: org.joda.time.DateTime }

--

Hans

[Jeff Scott Brown]

On August 1, 2014 at 4:09:27 AM, Hans Bogaards (hbog...@gmail.com) wrote:

Hi All,

A couple of weeks ago I posted the following question on StackOverflow, but haven't gotten any useful replies.
Maybe you can help:
----

We are using Grails 2.3.11 with the joda-time plugin 1.4.

I was trying to use a where query like this:
Form.where { year(dateCreated) == currYear }.list()

In this currYear is an integer and dateCreated is of type org.joda.time.DateTime

The result is this Exception:

org.springframework.dao.InvalidDataAccessResourceUsageException: Unsupported function [year] defined in query for property [datum] with type [class org.joda.time.DateTime]
    at org.grails.datastore.gorm.finders.DynamicFinder.applyDetachedCriteria(DynamicFinder.java:440)
    at grails.gorm.DetachedCriteria.withPopulatedQuery_closure10(DetachedCriteria.groovy:1201)
    at org.grails.datastore.gorm.GormStaticApi.withDatastoreSession_closure23(GormStaticApi.groovy:699)
    at org.grails.datastore.mapping.core.DatastoreUtils.execute(DatastoreUtils.java:302)
    at org.grails.datastore.gorm.AbstractDatastoreApi.execute(AbstractDatastoreApi.groovy:37)
    at org.grails.datastore.gorm.GormStaticApi.withDatastoreSession(GormStaticApi.groovy:698)
    at grails.gorm.DetachedCriteria.withPopulatedQuery(DetachedCriteria.groovy:1185)
    at grails.gorm.DetachedCriteria.list(DetachedCriteria.groovy:845)
    at grails.gorm.DetachedCriteria.list(DetachedCriteria.groovy:844)

It seems that functions like year() are not supported when using joda-time. Is this correct?

Yes, I believe so. 







JSB

-- 
Jeff Scott Brown 
jbrown@pivotal.io

Find The Cause ~ Find The Cure 
http://www.autismspeaks.org/ 

[Hans Bogaards]

How hard would it be at add support for this in the joda-time plugin?
Or somewhere else?

--
Hans

--
Sent from my Moto G with K-9 Mail. Please excuse my brevity.

[Jeff Scott Brown]

On August 2, 2014 at 1:43:55 PM, Hans Bogaards (hbog...@gmail.com) wrote:

How hard would it be at add support for this in the joda-time plugin?
Or somewhere else?
--

I don’t think it would be very easy to do this in the plugin without making some changes to the code that currently compiles “where” queries.  However, I think making the changes there instead of in the plugin would likely be pretty straight forward.  If you like, file a feature request in the form of a JIRA and one of us will dig into it and possibly implement a solution. 

[Fabio Issamu Oshiro]

Try to put sql formula config inside domain mapping:

Integer year

static constraints = {

year nullable: true

}

static mapping = {

year formula: 'YEAR(SETTLEMENT_DATE)' // database specific?

}

full domain code: https://github.com/fabiooshiro/grails-reporting-js/blob/master/grails-app/domain/grails/reporting/js/Sale.groovy

On Sunday, August 3, 2014 12:45:51 AM UTC-3, jbrown wrote:

On August 2, 2014 at 1:43:55 PM, Hans Bogaards (hbog...@gmail.com) wrote:

How hard would it be at add support for this in the joda-time plugin?
Or somewhere else?
--

I don’t think it would be very easy to do this in the plugin without making some changes to the code that currently compiles “where” queries.  However, I think making the changes there instead of in the plugin would likely be pretty straight forward.  If you like, file a feature request in the form of a JIRA and one of us will dig into it and possibly implement a solution. 

JSB

-- 
Jeff Scott Brown 
jbr...@pivotal.io

[Jeff Scott Brown]

On August 3, 2014 at 3:21:04 PM, Fabio Issamu Oshiro (fabio....@gmail.com) wrote:

Try to put sql formula config inside domain mapping:

Integer year

static constraints = {

year nullable: true

}

static mapping = {

year formula: 'YEAR(SETTLEMENT_DATE)' // database specific?

}

I don’t think that addresses the original question which is about using the year() method inside of a “where” query when the property is a joda date.

JSB

-- 
Jeff Scott Brown 
jbrown@pivotal.io