ColdBox Bundle 3.5.3 - CriteriaBuilder Error

Having this odd issue I can’t seem to understand with the new Criteria Builder.
When searching with offset=0 and max=10 (or max), it works fine. But once I ask for any higher offset, Criteria Builder throws an undefined the error “variables results is undefined”.

I copied my function and the stack trace to a gist;
While viewing, please note that the only arguments I’m passing to get this error are:
sortOrder=‘created desc’
(and I leave these default values as defined in the arguments)

If anyone could point me to why this is happening, I would greatly appreciate it.

what kind of database type? Microsoft SQL Server? MySQL?

Any SQL logging?

It’s MySQL, no query logging - I would have explored that if it hinted the root cause being a database issue, but it’s CB’s CriteriaBuilder.cfc #214 that’s throwing the error. Do you think it’s the query?

Yes, I would look at the logging of the SQL. My guess is that hibernate is returning a null value to results and you at trying to reference it. I’ve had this problem in the past with hibernate and my object being empty/null.

Are you using Railo or ACF?

I agree with Jeremy–turn on SQL logging in dev and leave it on. It is an invaluable help, not only for debugging errors, but also for understanding how CRiteriaBuilder and Hibernate turn your criteria queries into SQL.

Took a while to track it down, but I enabled the general query log and found it, formatted for readability but the query doesn’t error and returns the correct results:

this_.project_id as project1_7755_0_,
this_.user_id as user2_7755_0_,
this_.pattern_name as pattern3_7755_0_,
this_.pattern_id as pattern4_7755_0_,
this_.size_id as size5_7755_0_,
this_.size_name as size6_7755_0_,
this_.size_measurement as size7_7755_0_,
this_.project_status_id as project8_7755_0_,
this_.craft_id as craft9_7755_0_,
this_.total_yards_used as total10_7755_0_,
this_.sortOrder as sortOrder7755_0_,
this_.title as title7755_0_,
this_.made_for as made13_7755_0_,
this_.size_made as size14_7755_0_,
this_.pattern_from as pattern15_7755_0_,
this_.project_note as project16_7755_0_,
this_.date_started as date17_7755_0_,
this_.date_completed as date18_7755_0_,
this_.project_set_id as project19_7755_0_,
this_.created as created7755_0_
this_.updated as updated7755_0_,
this_.ignore_pattern_exists as ignore22_7755_0_,
this_.queue as queue7755_0_,
this_.improvised as improvised7755_0_,
(SELECT COUNT(i.image_id) FROM project_images_jn i WHERE i.project_id = this_.project_id) as formula2739_0_,
(SELECT count(f.entity_id) FROM favorites f where f.entity_id = this_.project_id AND f.entity_name = ‘project’) as formula2740_0_,
(SELECT fn_projectpatterncount(this_.project_id)) as formula2741_0_
projects this_
this_.created desc
LIMIT 12, 12;

Just to eliminate issues, is it possible that your offset exceeds the number of records?

No, but just to be sure:

For more elimination, what if you turn off query caching?

I know this is going to sound odd, but I just found the reason.
If I call count(); after list(), it will error. But if I call count() before list(), it works fine.

I still suspect the reason is the count method of CriteriaBuilder.cfc, specifically lines 210-213 but I don’t know why.

Doesn’t sound odd at all. I ran into the same issue a while back (sorry I didn’t suggest that earlier, I’m doing this on my cracked, cramped mobile).

In my super search method, I had to order my list() and count() methods appropriately so as to avoid this.

Glad you got it working

I think the reason the count fails after the list() is because of the sortOrder being included. If you remove this, does the count work after list() ?