[SOLVED] Criteria - searching against two columns concatenated

Criteria - searching against two columns concatenated

I am in need of a way to search against two concatenated columns with a Criteria in a grails project that I've been brought onto. The two columns make up a subject code for a University; a three-alpha-character code and a three-digit number. e.g. AAA123.

My research to date hasn't revealed any straight forward solutions because I have the following requirements:

I need to use Criteria for the PagedResultList as the UI (Javascript/Ajax) works off the paged list and totalCount.
I need to be able to use a wild card search, if the user searches for the alpha code (all subjects starting with 'AAA') or the specific subject ('AAA123').

e.g. subj_code = '%AAA%' or crse_numb = '%123%' or subj_code || crse_numb = '%AAA123%'

What I've found so far is that:

a) With Criteria, I cannot concatenate the columns (unless I've missed something)

b) I cannot use transients to join the columns

c) I cannot use findAll or where because they do not return PagedResultList.

If anyone knows of how to perform this using criteria or returning a PagedResultList, I'd be forever grateful.

Solution

In this formula property can help you because formula can participate in queries and it is transient by default.

Steps-

Create a formula property and concatenate your strings there.
use this formula property in your criteria query.

Use this post for writing formula property.

Hope this help