I try to use CreateCriteria to fetch some ids. Since ListDistinct not support pagination, I found a solution in the web to resolve this issue. http://ondrej-kvasnovsky.blogspot.fr/2012/01/grails-listdistinct-and-pagination.html
But I when I tried to fetch elements with sort and order I had an exception:
"Order by expression "THIS_.DATE" must be in the result list in this case; SQL statement:..."
My code:
class MyClassA {
Date date
static hasMany = [userList:User]
}
class User {
String login
}
class ResponseService {
def load(offset, max ) {
def idList = MyClassA.createCriteria().list (max: max, offset: offset) {
projections { distinct ( "id" ) }
userList{
eq("login","toto")
}
order("date","desc")
}
if( idList ) {
// fetch all Responses based on selected IDs
def results = MyClassA.getAll( idList )
return results
}
}
}
The probem probably is that in your result there is no date column. I haven't tested it but after looking on this question: What is the sql query for this? (comment to first answer) I think that adding
property("date")
to your projections can help.
---------------- EDIT ----------------------------
Here is full ResponseService class for your problem. I've added also property("id") to your projections clause.
class ResponseService {
def load(offset, max ) {
def idList = MyClassA.createCriteria().list (max: max, offset: offset) {
projections {
distinct ( "id" )
property("date")
property("id")
}
userList{
eq("login","toto")
}
order("date","desc")
}
if( idList ) {
// fetch all Responses based on selected IDs
def results = MyClassA.getAll( idList )
return results
}
}
}