Can I create a custom criteria query like

Question

I have a legacy PostgreSql table that contains columns with multiple values. I want to select rows that contain any of the values in my search.

-- Example query
select * from stuff where ARRAY['Value A', 'Value X'] && regexp_split_to_array(thing, '\|');

Can I generate this type of where condition from Grails 2.5.1 GORM 4.x criteria query?

FYI: I have seen the "Grails Postgresql Extensions Plugin" but I can't change my column(s) definition at this time.

Answer 1

You can use a sqlRestriction in a criteria to add arbitrary SQL conditions. It's mentioned at the bottom of the node reference for createCriteria().

Stuff.withCriteria {
    sqlRestriction "ARRAY['Value A', 'Value X'] && regexp_split_to_array(thing, '\|')"
}