using createCriteria with order based on a belongsTo property

Question

I have the next scheme:

class UserProfile {
    String title
    String firstName
    String lastName 
    static belongsTo = [user:User]
    static constraints = {
            user nullable:true , unique:true
            title nullable:true, blank:true
            firstName blank:false
            lastName nullable:true, blank:true
    }
}

class User {
    String username
    String password
    boolean enabled 
    String email    
    static constraints = {
        username size:6..40, blank:false, nullable: false, unique: true
        email email:true, size:6..40, blank:false, nullable: false, unique: true
        password size:5..64, password:true, blank:false, nullable: false
    }   
    String toString(){username}
}

I need a list of UserProfile ordered by the email that has the user!

I try:

UserProfile.createCriteria().listDistinct({
    if(params.orderBy){
        if(params.orderBy=="email"){
        //the exception says that the element has no such property to both cases    
            order("user.email", ascDesc)
            //order("email", ascDesc)               
        }else{
            order("${params.orderBy}", ascDesc)
        }
    }

})

so when I want to order by the email the exception says that the element has no such property to both cases. Note: ascDesc is a variable String it can be asc or desc

score 1 · Accepted Answer · answered Sep 18 '12 at 18:35

1

If you add

createAlias('user', 'u')

to the top of your criteria closure then you should be able to order by 'u.email'.

answered Sep 18 '12 at 18:35

Ian Roberts

120,891
16
170
183

excellent answer Ian, only it was only necessary to add this library: `import org.hibernate.criterion.CriteriaSpecification` – Isaid Htrs Sep 19 '12 at 17:04
It shouldn't be necessary to add any imports. Your IDE may complain that it doesn't recognise the createAlias method but that's the price you pay for dynamic DSLs - grails will accept it just fine. – Ian Roberts Sep 19 '12 at 17:55

score 1 · Answer 2 · edited Dec 11 '15 at 14:32

1

if (params.orderBy == "email") { 
    user { 
        order ("email", ascDesc) 
    } 
}

should work too without manual alias creation

edited Dec 11 '15 at 14:32

Aiden Zhao

564
5
24

answered Sep 23 '12 at 11:50

zyro

515
2
4

using createCriteria with order based on a belongsTo property

2 Answers2