CODEWITHSUNDEEP
hibernategrailsgroovy
Dasma
Dasma

Reputation: 1263

grails sort by length

Can any body help? I am using hibernate combined with Grails.

I want to execute this sql statement either by findby or createCriteria

select user.username, user.email from user where user.username like something order by length(username)

The problem occurs for the syntax order by length(username)

I have tried this but get validation sql syntax error:

def c = User.createCriteria();
List<User> user = c.list  {
    or {
        ilike("username", search)
        ilike("email", search)
    }

    order(length("username"))
    //order("length(username)")
    //order("username.length()")
    //sqlRestriction("order by length(username)")
}

and this

List<User> user = User.findAllByUsernameIlikeOrEmailIlike(search, search, [sort: "length(username)"])

Upvotes: 2

Views: 900

Answers (3)

Emmanuel Rosa
Emmanuel Rosa

Reputation: 9885

With a criteria query you're out of luck because order(String propertyName) cannot access the alias created by sqlProjection(java.lang.String sql, java.lang.String columnAlias, org.hibernate.type.Type type). Here's how to project the username, email, and length of username:

def rows = User.withCriteria {
    or {
        ilike("username", search)
        ilike("email", search)
    }

    projections {
        property('username')
        property('email')
        sqlProjection('length(username) as usename_len', 'name_len', LONG)
    }
}

The criteria query above will return a List of List, with the inner List containing the three columns. However, you cannot sort by name_len as part of the query. You'd have to sort the List in Groovy:

list.sort { it[2] }
// or, and alternative for Groovy >= 2.4.4
list.toSorted { it[2] }

Alternative: HQL

An alternative that can achieve what you're looking for is HQL:

def rows = User.executeQuery('SELECT username, email, length(username) as name_len FROM User ORDER BY name_len')

The HQL query above not only returns the three columns, it also sorts by length(username). Plus, HQL can project the User instance itself. In contrast, a criteria query can only project properties. This means that you can get a List of sorted User instances.

def users = User.executeQuery('SELECT u FROM User as u ORDER BY length(username)')

Upvotes: 3

Anshul
Anshul

Reputation: 697

You can try this way...for ascending order

 List<User> user = User.findAllByUsernameIlikeOrEmailIlike(username, email).sort{it.username.length}

For descending order just change your sort like this

 .sort{i1, i2 -> i2.username.length <=> i1.username.length}

Upvotes: 0

David Trang
David Trang

Reputation: 1434

Can you try this way?

List<User> users = User.withCriteria {
   or {
       ilike("username", search)
       ilike("email", search)
   }
}.sort { it.username.length }

Upvotes: 0

Related Questions