how to use ilike with Integer in grails

Question

I use EasyGrid plugin and must find values where integer field like '%001%'

initialCriteria {
    ilike('id', "%"+params.id+"%")
}

But ilike doesn't work with Integer. How to do it?

I tried to do:

    initialCriteria {
        ilike('id'.toString(), "%"+params.id+"%")
    }

    initialCriteria {
        ilike('str(id)', "%"+params.id+"%")
    }

but it's not work.

Did you try to parse it as `String`? `ilike('id', "%" + String.valueOf(params.id) + "%");` — ifloop, Apr 07 '14 at 12:39

score 5 · Accepted Answer · answered Apr 07 '14 at 12:59

5

If id is an integer in the database, then ilike doesn't really make much sense and there is probably a better way to do what you are trying to do (like adding a type field or something to the domain object, and filter by type)

However, you should be able to do something like this (untested):

initialCriteria {
    sqlRestriction "cast( id AS char( 256 ) ) like '%001%'"
}

answered Apr 07 '14 at 12:59

tim_yates

167,322
27
342
338

Not working. No signature of method: grails.gorm.DetachedCriteria.sqlRestriction() is applicable for argument types: (java.lang.String) values: [cast( temId AS char( 256 ) ) like '%001%'] – user3387291 Apr 08 '14 at 06:53
Since this is stated not to work, but has been accepted, would anyone care to update the answer? – WhyNotHugo Jul 14 '15 at 20:43
As they accepted it, I assume it did work, and the comment is wrong? – tim_yates Jul 14 '15 at 22:37

score 1 · Answer 2 · answered Aug 12 '15 at 09:55

Following criteria not working if you search from your textbox when user search any text character by mistake like 12dfdsf as your searchable id. It will give you an exception

initialCriteria {
    ilike('id', "%"+params.id+"%")
}

For better use you can use following criteria

initialCriteria {
   sqlRestriction "id like '%${params?.id}%'"
}

score 0 · Answer 3 · answered Apr 07 '14 at 13:06

0

You could do:

String paddedId = params.id.toString().padLeft(3,'0')

initialCriteria {
    ilike('id', "%$paddedId%")
}

answered Apr 07 '14 at 13:06

Manuel Vio

506
3
6

2

I don't think that will work as id is an integer in the domain – tim_yates Apr 07 '14 at 19:09
You're right, I focused on params.id conversion, forgetting the actual db column type. – Manuel Vio Apr 08 '14 at 07:02

score 0 · Answer 4 · answered Apr 08 '14 at 08:08

0

The solution offered by tim_yates with the sqlRestriction would work in version 1.5.0 of easygrid. One of the main differences from 1.4.x is that the gorm datasource no longer uses DetachedCriteria, but Criteria - which maps directly to Hibernate's Criteria API.

So you can try it on the last version. (Keep in mind that the upgrade might break your existing grids. There's also many other changes)

Another small observation is that 'initialCriteria' is not the right place to do stuff like that. (it's not wrong, but there is a 'globalFilterClosure' property for applying column independent filters)

answered Apr 08 '14 at 08:08

tudor

131
2

Where I can see documentation or example about 'globalFilterClosure'? – user3387291 Apr 08 '14 at 09:23
That would be here: https://github.com/tudor-malene/Easygrid. It behaves exactly like a normal filterClosure. The main difference is that it doesn't belong to any column. It's applied every time you request data. – tudor Apr 08 '14 at 13:08

score 0 · Answer 5 · answered Apr 08 '14 at 08:43

0

I mixed the code posted by @tim_yates and mine:

String paddedId = params.id.toString().padLeft(3,'0')

def crit = Book.withCriteria {
    sqlRestriction "lpad(cast( id AS char( 256 ) ), 3, '0') like '%${paddedId}%'"
}

I've tried with a h2 in-memory db and it works, but I am not sure about two things:

the real usefulness of that
lpad syntax consistence across all db engines

YMMV

answered Apr 08 '14 at 08:43

Manuel Vio

506
3
6

Note that it could be also written as `sqlRestriction "lpad(cast( id AS char( 256 ) ), 3, '0') = '$paddedId'"`. – Manuel Vio Apr 08 '14 at 08:58

how to use ilike with Integer in grails

5 Answers5

Linked