How to fetch records in grails by max date and group at same time

I have a table that looks like this:

id    name      shade        date_created
----  -----    -------      ---------------
1     Red       bright        10-28-2012
2     Orange    light         10-28-2012
3     Red       <null>        10-24-2013
4     Orange    light         10-24-2013

Desired Result:

id    name   value    date_created
----  -----  ------   ---------
3     Red    <null>   10-24-2013
4     Orange light    10-24-2013

What can I do with GORM to get this result?

In pure sql this is the query that gets me the desired result:

SELECT t.name, t.shade, r.MaxTime
FROM (SELECT name, MAX(date_created) as MaxTime
      FROM colorable
      GROUP BY name) r
INNER JOIN colortable t ON t.name = r.name AND t.date_created = r.MaxTime

What I've Tried:

    def c = Color.createCriteria()
    def results = c {
        projections {
            groupProperty("name")
            max("dateCreated")
        }
    }

But I can't figure out how to fetch more columns from the projection? i.e. the shade column

Answers


You can do this with detached criteria if you're using Grails 2.0 or above:

def colors = Color.withCriteria {
    eq "dateCreated", new grails.gorm.DetachedCriteria(Color).build {
        projections {
            min "dateCreated"
        }
    }

    projections {
        property "name"
        property "shade"
        property "dateCreated"
    }
}

The explicit use of the DetachedCriteria class is a bit ugly, but it's not too bad. This query should also be doable as a Where query, but there appears to be a bug which means you can't use '==' with aggregate functions. Once the bug is fixed, you should be able to do:

def colors = Color.where {
    dateCreated == max(dateCreated)
}.property("name").property("shade").property("dateCreated").list()

Note that replacing '==' with '<' works fine.


In HQL, basically you use a object notion instead of table. So assuming that you have the Color domain class:

String hql = " select c from ( select name,"
hql += " max(dateCreated) as maxTime "
hql += " from Color "
hql += " group by name ) as t"
hql += " inner join Color c on c.name = t.name and c.dateCreated = t.maxTime "

def result = Color.executeQuery(hql)

Need Your Help

Oracle PLSQL: xmltype.transform vs xmltransform

sql xslt plsql transform oracle11gr2

Since we have moved the database from 11.2.0.2.0 to 11.2.0.4.0 xmltype.transform function had got a specific behaviour.

Sencha app with Cordova on Xcode

ios xcode cordova

I am trying to build a ios package using sencha and phone gap. I followed the steps here http://vimeo.com/56673251. I am able to build and run the package on ios simulator. However, I can only see ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.