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 ?
“,
[”%#rc.txtSearch#%","%#rc.txtSearch#%"],
rc.offset,
variables.pagingMaxRows
);
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 = [];
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#%”)
.list(sortOrder=“partNumber”,offset=offset,max=max);
var foundRows = c.count();
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.
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%”)
.list(sortOrder=“partNumber”);
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…
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
.like(“partNumber”,"%1234%")
.OR(
c.restrictions.ilike(“description”,"%abc%")
)
.order(“partNumber”,“asc”)
.list(offset=1,max=10);