Using like to non-string columns in Grails

Question

I'm currenty working on a Grails query service that involves retrieving row/s from the database with a specific criteria called filterText.

List getAdjustmentCodes(params) {
    def filterText = params.filterText
    .
    .
    .
    adjustmentCodeList = AdjustmentCode.findAll{
        or {
            ilike('description', "%$filterText%")
            // ilike('id', "%$filterText%")
        }                       
    }

    return adjustmentCodeList
}

Notice that I've comment out the line ilike('id', "%$filterText%") because when the program reaches that line, it throws an error:

org.springframework.core.convert.ConversionFailedException: Failed to convert from type java.lang.String to type java.lang.Long for value...

This is probably because the id column of that table is of type long:

class AdjustmentCode implements Serializable {
    Long id
    String description
    String type
    String status
    Date statusDate
    String lastUpdateBy
    Date lastUpdateDate

    static mapping = {
        table 'RFADJUSTCDOTB'
        version false
        id column : 'ADJUS_CD'
        description column : 'ADJUS_DESC'
        type column : 'ADJUS_TYPE'
        status column : 'ADJUS_STATCD'
        statusDate column : 'ADJUS_STATDTE'
        lastUpdateBy column : 'ADJUS_LUPDBY'
        lastUpdateDate column : 'ADJUS_LUPDDTE'
    }

    .
    .
    .
}

But I need to use the like operator for this column. For instance, the user wants to search for an adjustment with a code similar to: 00002312123. Is this the right way to use the like operator, or are there any other way. Thank you for answering.

I've seen this post, but it doesn't tell how to use non-strings on a like in an or clause.

Answer 1

Well here is an alternative:

class AdjustmentCode implements Serializable {
    Long id
    String description
    .....
    String idAsString
    static mapping = {
        table 'RFADJUSTCDOTB'
        ...
        idAsString formula('to_char(id)') // Or use an equivalent fn to convert it to a String
}

You could then use ilike as follows:

ilike('idAsString', "%${filterText}%")

It's a lazy way out I suppose, but it should work.

Answer 2

ilike only work on Strings (text). You can't use it on other data types. It doesn't make sense to use insensitive (which is related to Strings). This isn't a limitation of Grails or Hibernate, but rather the way SQL functions.

Use the like operator instead.

This question has been asked before, and has been answered (from a hibernate perspective).