grails use order on createcriteria

Question

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
    }

  } 
}

score 3 · Accepted Answer · edited May 23 '17 at 11:44

3

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
        }

    } 
}

edited May 23 '17 at 11:44

Community

1
1

answered Jul 13 '13 at 19:37

kpater87

1,190
12
31

With "property("date")" I have no exception but, the list that I fetched contains dates and no ids. I didn't precise it but I want to have a list of ids ordered by their dates. – Jils Jul 13 '13 at 20:42
I don't understand sth: 1. What is the CP? 2. Why your id is not uniqe...? 3. If your id is not uniqe it means that the same id could have a few different dates. So by which one you would like to sort? – kpater87 Jul 13 '13 at 21:16
Sorry for my second and third questions. They were stupid. I haven't noticed that you are joining data from two tables. I've modified my answer. So now it should work fine for you. – kpater87 Jul 15 '13 at 07:14
But I don't understand why the projections are returning only last property from the list. Similar problems you can find in this questions: [CreateCriteria with projections does not select all columns](http://stackoverflow.com/questions/15250974/createcriteria-with-projections-does-not-select-all-columns); [Grails Projections not returning all properties and not grouped](http://stackoverflow.com/questions/13911615/grails-projections-not-returning-all-properties-and-not-grouped) – kpater87 Jul 15 '13 at 07:33
Also in grails JIRA you can find this issue: [GRAILS-10072](http://jira.grails.org/browse/GRAILS-10072?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel) – kpater87 Jul 15 '13 at 07:39
Thanks very much! that work, I will try this Service for other associations. – Jils Jul 15 '13 at 17:48

grails use order on createcriteria

1 Answers1