CODEWITHSUNDEEP
grailslowercase
Jacob
Jacob

Reputation: 4031

Grails lower() in query not working

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

Upvotes: 2

Views: 1179

Answers (3)

user800014
user800014

Reputation:

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.

Upvotes: 2

aiolos
aiolos

Reputation: 4697

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.

Upvotes: 3

Alidad
Alidad

Reputation: 5538

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%' ...")

Upvotes: 1

Related Questions