CODEWITHSUNDEEP
grailsgrails-ormgrails-3.0
Igor
Igor

Reputation: 85

Grails gorm 'where' query with 'lower()' and 'in' operator

I have a Domain class

class Hashtag {
    String tag
}

Why

Hashtag.where { lower(tag) == "#london" }.list()

works ok, but

Hashtag.where { lower(tag) in [ "#london", "#paris" ] }.list()

results in

org.springframework.dao.InvalidDataAccessResourceUsageException: Unsupported function [lower] defined in query for property [hashtag] with type [class java.lang.String]

How to write such query properly?

Thanks!

Upvotes: 4

Views: 1721

Answers (3)

Igor
Igor

Reputation: 85

Answering my own question, I've found alternative way to do case-insensitive comparison - using Criteria. An advantage of this method is that values in cities also could be mixed cases.

def cities = ["#LONdon", "#PAris"]
Hashtag.createCriteria().list {
    or {
        cities.each { eq("tag", it, [ignoreCase: true])
    }
}

Upvotes: 1

Emmanuel Rosa
Emmanuel Rosa

Reputation: 9895

I can't answer why using lower() with in() did not work. I read the source but I don't know ASTs well enough to understand it.

But, to solve your problem you can use a derived property to perform the lower().

class Hashtag {
    String tag
    String loweredTag

    static mapping {
        loweredTag formula: 'lower(tag)'
    }
}

Then you can use the derived property in the where query:

Hashtag.where { loweredTag in [ "#london", "#paris" ] }.list()

Upvotes: 2

Vinay Prajapati
Vinay Prajapati

Reputation: 7546

I'm not sure if it will work as per your need. But documentation says that you may use subqueries in where block like below:

Hashtag.where { lower(tag).of{} in [ "#london", "#paris" ] }.list()

Please try it and let me know if it doesn't work.

Hope it helps!

Upvotes: 0

Related Questions