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

Question

I have a domain class

class Hashtag {
    String tag
}

Why

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

works fine but

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

leads to

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

How to write such a request?

Thanks!

Answer 1

I can not answer why using lower()with in()did not help. I read the source , but I do not know AST well enough to understand this.

But to solve your problem you can use a derived property to execute 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()

Answer 2

, - . , cities .

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

Answer 3

I am not sure that it will work according to your needs. But the documentation says that you can use subqueries in a block, as shown below:

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

Please try and let me know if this does not work.

Hope this helps!