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

Question

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!

Answer 1

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()