Grails lower() in query not working

Question

Let's say I have code like this:

def c = Account.createCriteria()
def results = c {
    between("balance", 500, 1000)
    eq("branch", "London")
    or {
        like("holderFirstName", "Fred%")
        like("holderFirstName", "Barney%")
    }
    maxResults(10)
    order("holderLastName", "desc")
}

I want to use lower() function to transforming data to lower case

def c = Account.createCriteria()
def results = c {
    between("balance", 500, 1000)
    eq("branch", "London")
    or {


        like("lower(holderFirstName)", "Fred%")
        like("lower(holderFirstName)", "Barney%")


    }
    maxResults(10)
    order("holderLastName", "desc")
}

My code doesn't work. What is the correct syntax? I have a problem with umlauts so I don't want to use ilike

http://stackoverflow.com/questions/16538466/grails-postegresql-and-umlauts — Jacob, May 14 '13 at 13:11

aiolos · Answer 1 · 2013-05-14T14:04:22.087

Don't know which lower function you'd like to use but I guess you want to fetch data based on holderFirstName property ignoring the case.

Here you could use ilike, which is an case-insensitive like:

def c = Account.createCriteria()
def results = c.list {
    between("balance", 500, 1000)
    eq("branch", "London")
    or {
        ilike("holderFirstName", "Fred%")
        ilike("holderFirstName", "Barney%")
    }
    maxResults(10)
    order("holderLastName", "desc")
}

By the way - you missed to call list() on your criteria ...

Update

You could try to add a formula to your domain class like this:

static mapping = {
   lowerFirstName formula: "lower(holder_first_name)"

}

and change the property in your criteria to lowerFirstName:

like("lowerFirstName", "fred%") // changed 'Fred%' to 'fred%'

Code is not tested but should work.

You should also need to add lowerFirstName in your entity class. — Tushar Saxena, Apr 22 '19 at 12:03

score 2 · Accepted Answer · answered May 14 '13 at 14:05

2

To use database functions in a criteria you need to use sqlRestriction() that add's restrictions directly to the generated sql.

def c = Account.createCriteria()
def results = c.list {
...
  sqlRestriction("lower(holder_first_name) like '%%'")
}

Note that with this you use your column name, and not attribute name.

answered May 14 '13 at 14:05

This work fine and just how I wanted. But I have another problem if the first letter is an german umlaut no results are found although a record is the database – Jacob May 15 '13 at 07:20

Alidad · Answer 3 · 2013-05-14T13:14:02.370

1

If you are trying to compare for case insensitivity, another option is to use ilike for that purpose. Ilike is similar to Like, but its case insensitive. here

If you do not want to use ilike (as added to the question), I think you alternative approach is executeQuery and hql.

Account.executeQuery(" select * from Account where .... or (lower(holderFirstName) = 'Fred%' ...")

edited May 14 '13 at 13:14

answered May 14 '13 at 13:03

Alidad

5,463
1
24
47

Can't I somehow make it work with the default criteria I have. I dont get it it just a function call and it doesn't work. – Jacob May 14 '13 at 13:19
I am not sure how to do it in createCriteria other than ilike, maybe others have a better solution. – Alidad May 14 '13 at 13:26

Grails lower() in query not working

3 Answers3