ORM Like Query

I have an instance where I need to perform a like search. My first instinct was to use executeQuery

rc.devices = vproService.executeQuery("
SELECT v FROM ***** v
WHERE partNumber LIKE ? OR description LIKE ?

This is great because it gives me the results I am looking for but there is a problem. I need to know how many total results there are to setup paging.Without running a second query minus the offset and max rows I don’t know of a way to do this. So my next thought was to use criteria.

var restrictions = vproService.getRestrictions();
var criteria = [];

rc.devices = vproService.criteriaQuery(criteria=criteria,sortOrder=“partNumber”,offset=rc.offset,max=variables.pagingMaxRows,asQuery=false);

rc.foundrows = vproService.criteriaCount(criteria);

This works great because I can get a count using criteriaCount but I am still left with one issue. I am only searching 1 column in the example but in my app i need to do an OR on 2 different columns. If I add another criteria


It doesn’t seem to like that. Now not only will it search the description column, but it won’t search the partNumber column either. What is a programmer to do? haha

Dan, you can use the new criteria builder if you like or look at the samples in ContentBox where we do exactly what you need.

var c = newCriteria();
var devices = c.like(“partNumber”, “%#rc.txtSearch#%”)
.like(“description”), “%#rc.txtSearch#%”)
var foundRows = c.count();

Luis F. Majano
Ortus Solutions, Corp

ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Blog: http://www.luismajano.com
IECFUG Manager: http://www.iecfug.com

Social: twitter.com/lmajano facebook.com/lmajano

What version is this in? I am on 3.1 and I am getting an error

Could not find the ColdFusion component or interface Criteria.


Dan, if you are using offset and max rows the maximum return rows will be your count.

or you can just get an ArrayLen() to get the number of returned records.

If you have a maxrows of 10 then arrayLen(products) will be 10? Not sure I am following you.


Sorry had to re-read the question, execute query as you know will return an Array. So you can do an ArrayLen() to get the number of results.

Unless there is some hidden meaning to what you want to do, I am assuming you want to do pagination over the results?

If that is the case, then you would do two queries one for getting the count which can be achieved with HQL, then the second would be to then get the first 10 using the offset and maxrow.

I am assuming that is what you are wanting to do?

Yes… and it is what I wanted to avoid somehow, It just feels dirty running the same query 2x ;D

So I ran the criteria example and its doing an AND search… is there anyway to make this an OR search? If I just use the description like it works fine but I really need a way to search multiple columns.

var c = newCriteria();
var devices = c
.like(“partNumber”, “%abc%”)
.like(“description”, “%abc%”)

this_.id as id24_0_,

Well technically your not, it does seem like it. But the first you only need to do a select count(somecolumn) where as the second query is to get the actual results.

I think there is a major difference if you do it this way…

Yes Dan use the or operator method. Every argument is a restriction.

C.or( c.restrictions.like(), etc)


According to the docs and your examples this should work but I can’t get it to do anything. If I use either the (or/and) coldbox throws an error.

var c = newCriteria();
var devices = c

Application Execution ExceptionError Type: Autowire.AutowireException : [N/A]

Error Messages: Error autowiring *****.****. Invalid CFML construct found on line 13 at column 26. ColdFusion was looking at the following text:


The CFML compiler was processing:

  • An expression beginning with c, on line 11, column 31.This message is usually caused by a problem in the expressions structure.
  • A script statement beginning with var on line 11, column 17.
  • A script statement beginning with public on line 8, column 9

Not sure if that's a copy/paste example or not, but you misspelled
"like" in your restrictions


should be

c.restrictions.like("description","%abc%") (no i)


Not according to the docs

ilike(required string property, required string propertyValue)

I'm not seeing the API docs for 3.5, but you might still try like
instead of ilike. Here's an example from the Wiki:

var criteria = ArrayNew(1);
ArrayAppend(criteria, Restrictions.like("lastName","M%"));
prc.example4 = authorService.criteriaQuery(criteria);

var criteria = ArrayNew(1);
ArrayAppend(criteria, Restrictions.ilike("lastName","s%"));
prc.example5 = authorService.criteriaQuery(criteria);


It isn't clear to me whether that is a typo and both should be the
same or whether there is a difference in the functions. Over on the
Hibernate side, I don't see anything called ilike but definitely
instances of like:

Based on that, I'd give "like" a try and see what happens and
hopefully we'll get some clarification from Luis.


Ilike is a case insensitive search while like is case sensitive.