bx-orm: Criteria Queries
BoxLang is the AI-native software productivity platform for building, modernizing and running applications, with developers and AI agents working together.
entityCriteria( entityName ) builds a query one method at a time, then runs
it with a terminal method. There is no query string, property names are
checked as you add them, and values are always bound as parameters.
bx-orm 2.x only. It does not exist in bx-orm 1.x: use entityLoad() or
ormExecuteQuery() there (see bx-orm-querying). It uses cborm's method
names, so cborm criteria code reads the same.
users = entityCriteria( "User" )
.isEq( "active", true )
.like( "lastName", "Sm%" )
.order( "lastName" )
.list()
c = User::criteria() // same thing, from the static entity method
How It Works
- Building methods add to the criteria and return it, so they chain.
- Terminal methods run the query and return the result. One criteria can
run several queries.
copy() (alias clone()) branches it.
- Method names are case-insensitive and take positional or named arguments:
isEq( property = "make", value = "Ford" ).
- A misspelled property fails at once with
orm.property.unknown:
Vehicle has no property [mkae]. Did you mean [make]?
- Values are converted to the property's type:
isEq( "id", "42" ) works.
Conditions
All conditions are combined with and.
| Method (aliases) |
Meaning |
isEq( p, v ) (eq) |
p = v; a null value means is null |
ne( p, v ) (isNe) |
p <> v; null means is not null |
isGt, isGe, isLt, isLe |
Also gt, ge/gte, lt, le/lte |
like( p, pattern ) |
You supply the % wildcards |
ilike( p, pattern ) |
Case-insensitive like |
between( p, min, max ) |
Inclusive range |
isIn( p, values ) (in) |
Array, list or subquery; empty matches none |
isNotIn( p, values ) |
The opposite |
isNull( p ), isNotNull( p ) |
Null checks |
isTrue( p ), isFalse( p ) |
Boolean properties |
isEmpty( p ), isNotEmpty( p ) |
To-many collections |
sizeEq( p, n ) ... sizeLe |
Collection size |
eqProperty( p, other ) ... |
Also ne, gt, ge, lt, leProperty |
idEq( id ) |
Id; a struct for a composite id |
sql( fragment, params ) |
Native SQL condition, see below |
where() Shorthands, Groups and Negation
c.where( "make", "Honda" ) // isEq
c.where( "price", ">=", 20000 ) // = != > >= < <= like ilike in
c.where( { make : "Honda", year : 2024 } ) // several isEq; array = isIn
// make = Honda OR make = Toyota
c.anyOf( ( c ) => c.isEq( "make", "Honda" ).isEq( "make", "Toyota" ) )
// (make = Honda AND model like R%) OR year < 2000
c.anyOf( ( c ) => c
.allOf( ( a ) => a.isEq( "make", "Honda" ).like( "model", "R%" ) )
.isLt( "year", 2000 )
)
// several closures are alternatives; each one's conditions must all match
c.anyOf(
( c ) => c.isEq( "make", "Honda" ).isEq( "model", "Civic" ),
( c ) => c.isEq( "make", "Ford" )
)
c.notLike( "make", "Hon%" ) // any condition + "not" prefix
c.notIn( "status", [ "banned", "deleted" ] ) // notBetween, notEq, ...
c.not( ( c ) => c.isEq( "make", "Honda" ).isEq( "model", "Civic" ) )
Aliases: or, $or, orWhere, disjunction for anyOf; and, $and,
conjunction for allOf.
c.restrictions (cborm Style)
c.restrictions builds a condition without adding it. Pass the results to
add(), or(), and() or not(); they mix with closures.
c = entityCriteria( "User" )
r = c.restrictions
c.or( r.isEq( "role", "admin" ), r.isGt( "age", 30 ) )
c.add( r.like( "name", "A%" ), r.isNotNull( "email" ) )
c.not( r.isEq( "status", "banned" ) )
Native SQL and Functions in Paths
// ? params are bound in order; {alias}.column and {property} are expanded
c.sql( "upper({alias}.first_name) = ?", [ "LUIS" ] )
c.sql( "lower({make}) = ? or {model} = ?", [ "ford", "Civic" ] )
// any property argument can be a function call (incl. sqlFunctions)
c.isEq( "year(createdDate)", 2025 ).order( "lower(customer.name)" )
Function arguments must be paths, nested calls, numbers or quoted strings
('none'); pass other values as the condition value.
Joins and Aliases
A dotted path joins automatically: isEq( "manufacturer.name", "Ford" ).
- Conditions use an inner join; inside
anyOf() and not(), and for
ordering and projections, a left join. A path is joined once and reused.
manufacturer.id compares the foreign key with no join. An association can
be compared with an id or an entity: isEq( "manufacturer", 42 ).
- The root alias is
this (this.name).
entityCriteria( "Vehicle" )
.joinTo( "manufacturer", "m" ) // alias: createAlias
.isEq( "m.name", "Honda Motor Co." )
c.leftJoin( "manufacturer", "m" ) // innerJoin, rightJoin, fullJoin
c.joinTo( "manufacturer", "m", "left" ) // inner/left/right/full, c.LEFT_JOIN
// withClause (closure or c.restrictions.*): conditions go in the join's ON,
// so a left join keeps rows without a match
c.joinTo( "manufacturer", "m", "left", ( j ) => j.isTrue( "m.active" ) )
// with{Association}(): unqualified paths start at the association
entityCriteria( "Vehicle" )
.withManufacturer( ( m ) => m.like( "name", "Honda%" ) )
.isEq( "model", "Civic" )
entityCriteria( "Vehicle" )
.withManufacturer( "left" ) // cborm: createCriteria( "manufacturer" )
.like( "name", "Honda%" )
.end()
.isEq( "model", "Civic" )
// load the association with the rows (no N+1)
vehicles = entityCriteria( "Vehicle" ).fetch( "manufacturer" ).list()
Through a to-many condition each root is returned once, and count() counts
each root once.
Subqueries
subquery( entityName, alias ) (alias createSubcriteria,
detachedCriteria): unqualified paths start at the subquery's entity, and
this is the outer root.
c = entityCriteria( "Manufacturer" )
// manufacturers that make a Honda / that have no vehicles
c.exists( c.subquery( "Vehicle", "v" )
.eqProperty( "v.manufacturer", "this" ).isEq( "make", "Honda" ) )
c.notExists( c.subquery( "Vehicle", "v" )
.eqProperty( "v.manufacturer", "this" ) )
// isIn with the subquery's projection
c.isIn( "id", c.subquery( "Vehicle", "v" ).isEq( "make", "Ford" )
.project( ( p ) => p.property( "manufacturer.id" ) ) )
counted = c.subquery( "Vehicle", "v" )
.eqProperty( "v.manufacturer", "this" )
.project( ( p ) => p.rowCount() )
c.subLe( 2, counted ) // at least 2 vehicles
The cborm forms compare a property (propertyEq, propertyGt,
propertyIn, propertyNotIn, ...) or a value (subEq, subLe, subIn,
...) with a subquery. Quantified forms compare with every or some row:
propertyGeAll( "price", prices ), subLtSome( 10, prices ).
Projections and Result Shapes
totals = entityCriteria( "Order" )
.project( ( p ) => p.group( "status" ).count( "id", "orders" )
.sum( "total", "revenue" ) )
.order( "revenue desc" ) // a projection alias works too
.asStruct()
.list() // [ { status, orders, revenue }, ... ]
// cborm form: values are "property[:alias]" lists
c.withProjections( groupProperty = "make", count = "vin:total" )
Projection methods: property, group (groupProperty), sum, avg,
min, max, count, countDistinct, rowCount( [alias] ), id( [alias] ).
One projection gives plain values; several give an array per row, or a
struct per row with asStruct().
| Method |
list() returns |
| (default) |
Entities |
asStruct() |
Structs: projections, or id and plain properties |
asStruct( includes, [opts] ) |
entityToStruct()-style structs |
asQuery() |
A query object |
asStream() |
A Java stream |
asDistinct() |
Distinct rows |
resultTransformer( c.X ) |
cborm constants, see below |
asStruct() dates are ISO 8601 strings, not date objects.
asStruct( "id,name,role.name,orders" ) reads with projection queries,
without loading entities. Not allowed with each() or chunk().
resultTransformer() accepts c.DISTINCT_ROOT_ENTITY (drop repeated
roots), c.ROOT_ENTITY (entities), c.ALIAS_TO_ENTITY_MAP (structs) and
c.PROJECTION, for cborm code. A to-many condition already returns each
root once, so it is rarely needed.
Ordering, Paging and Options
c.order( "lastName" ) // asc by default (alias orderBy)
c.order( "lastName", "desc" )
c.order( "lastName", "asc", true ) // ignore case
c.orderBy( "make desc, model asc" )
c.orderByDesc( "createdDate" )
c.firstResult( 20 ).maxResults( 10 ) // aliases offset/skip, limit/take
Without order(), entity results use the entity's defaultSort.
| Option |
Effect |
cache( [region] ) |
Query cache (alias cacheable); cache( false ) off |
readOnly() |
Loaded entities are never saved |
timeout( seconds ) |
Query timeout |
fetchSize( n ) |
JDBC fetch size |
comment( text ) |
SQL comment |
queryHint( name, value ) |
Hibernate query hint |
lock( [mode], [opts] ) |
Row lock, see below |
Terminal Methods
| Method |
Returns |
list() |
Rows; cborm args list( max, offset, timeout, sortOrder, ... ) |
count( [property] ) |
Matching roots, or distinct values of a property |
exists() |
Whether any row matches |
get( [uniqueFirst] ) |
One row or null; several: orm.query.nonUnique |
getOrFail() |
Like get(); none is orm.notFound |
first() / firstOrFail() |
First row in order, or null / error |
paginate( page, maxRows ) |
{ results, pagination } |
simplePaginate( page, maxRows ) |
{ results, pagination : { hasMore } } |
pluck( property ) |
One property's values |
sum, avg, min, max( p ) |
An aggregate |
each( fn, [size] ) |
Calls fn per row; returns the count |
chunk( size, fn ) |
Calls fn per batch; returns the count |
updateAll( values ) / deleteAll() |
One bulk statement; row count |
page = entityCriteria( "Order" )
.isGe( "createdDate", dateAdd( "d", -30, now() ) )
.orderByDesc( "createdDate" )
.paginate( page = 2, maxRows = 25 )
// pagination : { page, maxRows, offset, totalRecords, totalPages }
entityCriteria( "User" ).isFalse( "verified" ).chunk( 500, ( users ) => {
users.each( ( u ) => u.setReminderSent( true ) )
} )
each() and chunk() read in batches (100 by default for each()), and
flush and clear the session after each batch, so changes are saved and
memory stays flat.
Bulk Updates, Deletes and Locks
count = entityCriteria( "Order" )
.isEq( "status", "pending" )
.isLt( "createdDate", dateAdd( "d", -30, now() ) )
.updateAll( { status : "expired", note : null } )
count = entityCriteria( "Session" ).isLt( "expires", now() ).deleteAll()
transaction {
jobs = entityCriteria( "Job" ).isEq( "status", "new" )
.lock( "write", { skipLocked : true } ) // read, write, force
.maxResults( 10 )
.list()
}
Bulk statements fire no events, do not cascade, skip versions and
autoTimestamp, and leave loaded entities stale. deleteAll() on a
softDelete entity marks rows deleted.
Flow Helpers
entityCriteria( "User" )
.when( len( search ), ( c ) => c.like( "lastName", search & "%" ) )
.unless( showDeleted, ( c ) => c.isFalse( "isDeleted" ) )
.apply( activeUsers ) // reusable: ( c ) => c.isTrue( "active" )
.list()
when() and unless() take an optional third "otherwise" closure;
peek( fn ) runs a closure and keeps chaining.
Seeing the Query
getSQL( [executable], [format] ): the SQL, without running it.
executable = true inlines the values; paging is not included.
getHQL(): the HQL it compiles to. peekSQL( fn ) passes the SQL to a
closure and keeps chaining.
writeDump( c ) / toString(): the calls, HQL, params and SQL.
- cborm SQL log:
startSqlLog(), logSQL( label ), getSqlLog(),
stopSqlLog().
writeOutput( entityCriteria( "User" ).isEq( "active", true ).getSQL( true ) )
Common Pitfalls
- ❌ Calling
entityCriteria() on bx-orm 1.x: it is 2.x only
- ❌
count() then list() on a projected criteria: count() clears the
projections and asStruct()/asQuery() (cborm behavior). Count on a
copy(), or set the shape after counting
- ❌
like( "name", "Sm" ) without %: it is an exact match
- ❌
get() when several rows can match: orm.query.nonUnique; use
first() or get( true )
- ❌
lock() outside transaction {}: orm.argument
- ❌ A
withClause path that needs another join: orm.argument; use the new
alias or earlier aliases, and put other conditions in the where
- ❌
rightJoin / fullJoin on MySQL or MariaDB (no full join support)
- ❌
updateAll() / deleteAll() with maxResults() or firstResult()
- ❌ Expecting
updateAll()/deleteAll() to run events or cascades:
deleteAll() can hit foreign keys, and loaded entities need
entityReload() or ormClearSession()
- ❌ Holding entities across
each()/chunk() batches: the session is
cleared after every batch
- ❌ Running a
subquery() on its own: pass it to exists(), isIn(), ...
- ❌ Reading
asStruct() dates as date objects: they are ISO 8601 strings
- ✅ Paginate with an explicit
order() so pages are stable
- ✅ Use
fetch() for associations you read in a loop
- ✅ Use
getSQL() or writeDump() to check what a criteria will run
1---2name: bx-orm-criteria3description: "Use this skill when building fluent ORM queries in BoxLang with bx-orm 2.x entityCriteria(): conditions (isEq, like, between, isIn, isNull, not* forms), anyOf/allOf groups, c.restrictions, joins and aliases (joinTo/createAlias, withClause, with{Association}), fetch, subqueries, projections, asStruct/asQuery/asStream results, resultTransformer, ordering, paging, terminals (list, get, count, paginate, each, chunk), bulk updateAll/deleteAll, row locking, and inspecting SQL with getSQL()."4metadata:5 source: https://skillmd.com/skills/ortus-boxlang/bx-orm-criteria6---78# bx-orm: Criteria Queries910> BoxLang is the AI-native software productivity platform for building, modernizing and running applications, with developers and AI agents working together.1112`entityCriteria( entityName )` builds a query one method at a time, then runs13it with a terminal method. There is no query string, property names are14checked as you add them, and values are always bound as parameters.1516**bx-orm 2.x only.** It does not exist in bx-orm 1.x: use `entityLoad()` or17`ormExecuteQuery()` there (see `bx-orm-querying`). It uses cborm's method18names, so cborm criteria code reads the same.1920```javascript21users = entityCriteria( "User" )22 .isEq( "active", true )23 .like( "lastName", "Sm%" )24 .order( "lastName" )25 .list()2627c = User::criteria() // same thing, from the static entity method28```2930## How It Works3132- **Building methods** add to the criteria and return it, so they chain.33- **Terminal methods** run the query and return the result. One criteria can34 run several queries. `copy()` (alias `clone()`) branches it.35- Method names are case-insensitive and take positional or named arguments:36 `isEq( property = "make", value = "Ford" )`.37- A misspelled property fails at once with `orm.property.unknown`:38 `Vehicle has no property [mkae]. Did you mean [make]?`39- Values are converted to the property's type: `isEq( "id", "42" )` works.4041## Conditions4243All conditions are combined with `and`.4445| Method (aliases) | Meaning |46| --- | --- |47| `isEq( p, v )` (`eq`) | `p = v`; a `null` value means `is null` |48| `ne( p, v )` (`isNe`) | `p <> v`; `null` means `is not null` |49| `isGt`, `isGe`, `isLt`, `isLe` | Also `gt`, `ge`/`gte`, `lt`, `le`/`lte` |50| `like( p, pattern )` | You supply the `%` wildcards |51| `ilike( p, pattern )` | Case-insensitive `like` |52| `between( p, min, max )` | Inclusive range |53| `isIn( p, values )` (`in`) | Array, list or subquery; empty matches none |54| `isNotIn( p, values )` | The opposite |55| `isNull( p )`, `isNotNull( p )` | Null checks |56| `isTrue( p )`, `isFalse( p )` | Boolean properties |57| `isEmpty( p )`, `isNotEmpty( p )` | To-many collections |58| `sizeEq( p, n )` ... `sizeLe` | Collection size |59| `eqProperty( p, other )` ... | Also `ne`, `gt`, `ge`, `lt`, `leProperty` |60| `idEq( id )` | Id; a struct for a composite id |61| `sql( fragment, params )` | Native SQL condition, see below |6263### `where()` Shorthands, Groups and Negation6465```javascript66c.where( "make", "Honda" ) // isEq67c.where( "price", ">=", 20000 ) // = != > >= < <= like ilike in68c.where( { make : "Honda", year : 2024 } ) // several isEq; array = isIn6970// make = Honda OR make = Toyota71c.anyOf( ( c ) => c.isEq( "make", "Honda" ).isEq( "make", "Toyota" ) )7273// (make = Honda AND model like R%) OR year < 200074c.anyOf( ( c ) => c75 .allOf( ( a ) => a.isEq( "make", "Honda" ).like( "model", "R%" ) )76 .isLt( "year", 2000 )77)7879// several closures are alternatives; each one's conditions must all match80c.anyOf(81 ( c ) => c.isEq( "make", "Honda" ).isEq( "model", "Civic" ),82 ( c ) => c.isEq( "make", "Ford" )83)8485c.notLike( "make", "Hon%" ) // any condition + "not" prefix86c.notIn( "status", [ "banned", "deleted" ] ) // notBetween, notEq, ...87c.not( ( c ) => c.isEq( "make", "Honda" ).isEq( "model", "Civic" ) )88```8990Aliases: `or`, `$or`, `orWhere`, `disjunction` for `anyOf`; `and`, `$and`,91`conjunction` for `allOf`.9293### `c.restrictions` (cborm Style)9495`c.restrictions` builds a condition without adding it. Pass the results to96`add()`, `or()`, `and()` or `not()`; they mix with closures.9798```javascript99c = entityCriteria( "User" )100r = c.restrictions101c.or( r.isEq( "role", "admin" ), r.isGt( "age", 30 ) )102c.add( r.like( "name", "A%" ), r.isNotNull( "email" ) )103c.not( r.isEq( "status", "banned" ) )104```105106### Native SQL and Functions in Paths107108```javascript109// ? params are bound in order; {alias}.column and {property} are expanded110c.sql( "upper({alias}.first_name) = ?", [ "LUIS" ] )111c.sql( "lower({make}) = ? or {model} = ?", [ "ford", "Civic" ] )112113// any property argument can be a function call (incl. sqlFunctions)114c.isEq( "year(createdDate)", 2025 ).order( "lower(customer.name)" )115```116117Function arguments must be paths, nested calls, numbers or quoted strings118(`'none'`); pass other values as the condition value.119120## Joins and Aliases121122A dotted path joins automatically: `isEq( "manufacturer.name", "Ford" )`.123124- Conditions use an **inner** join; inside `anyOf()` and `not()`, and for125 ordering and projections, a **left** join. A path is joined once and reused.126- `manufacturer.id` compares the foreign key with no join. An association can127 be compared with an id or an entity: `isEq( "manufacturer", 42 )`.128- The root alias is `this` (`this.name`).129130```javascript131entityCriteria( "Vehicle" )132 .joinTo( "manufacturer", "m" ) // alias: createAlias133 .isEq( "m.name", "Honda Motor Co." )134135c.leftJoin( "manufacturer", "m" ) // innerJoin, rightJoin, fullJoin136c.joinTo( "manufacturer", "m", "left" ) // inner/left/right/full, c.LEFT_JOIN137138// withClause (closure or c.restrictions.*): conditions go in the join's ON,139// so a left join keeps rows without a match140c.joinTo( "manufacturer", "m", "left", ( j ) => j.isTrue( "m.active" ) )141142// with{Association}(): unqualified paths start at the association143entityCriteria( "Vehicle" )144 .withManufacturer( ( m ) => m.like( "name", "Honda%" ) )145 .isEq( "model", "Civic" )146147entityCriteria( "Vehicle" )148 .withManufacturer( "left" ) // cborm: createCriteria( "manufacturer" )149 .like( "name", "Honda%" )150 .end()151 .isEq( "model", "Civic" )152153// load the association with the rows (no N+1)154vehicles = entityCriteria( "Vehicle" ).fetch( "manufacturer" ).list()155```156157Through a to-many condition each root is returned once, and `count()` counts158each root once.159160## Subqueries161162`subquery( entityName, alias )` (alias `createSubcriteria`,163`detachedCriteria`): unqualified paths start at the subquery's entity, and164`this` is the outer root.165166```javascript167c = entityCriteria( "Manufacturer" )168169// manufacturers that make a Honda / that have no vehicles170c.exists( c.subquery( "Vehicle", "v" )171 .eqProperty( "v.manufacturer", "this" ).isEq( "make", "Honda" ) )172c.notExists( c.subquery( "Vehicle", "v" )173 .eqProperty( "v.manufacturer", "this" ) )174175// isIn with the subquery's projection176c.isIn( "id", c.subquery( "Vehicle", "v" ).isEq( "make", "Ford" )177 .project( ( p ) => p.property( "manufacturer.id" ) ) )178179counted = c.subquery( "Vehicle", "v" )180 .eqProperty( "v.manufacturer", "this" )181 .project( ( p ) => p.rowCount() )182c.subLe( 2, counted ) // at least 2 vehicles183```184185The cborm forms compare a property (`propertyEq`, `propertyGt`,186`propertyIn`, `propertyNotIn`, ...) or a value (`subEq`, `subLe`, `subIn`,187...) with a subquery. Quantified forms compare with every or some row:188`propertyGeAll( "price", prices )`, `subLtSome( 10, prices )`.189190## Projections and Result Shapes191192```javascript193totals = entityCriteria( "Order" )194 .project( ( p ) => p.group( "status" ).count( "id", "orders" )195 .sum( "total", "revenue" ) )196 .order( "revenue desc" ) // a projection alias works too197 .asStruct()198 .list() // [ { status, orders, revenue }, ... ]199200// cborm form: values are "property[:alias]" lists201c.withProjections( groupProperty = "make", count = "vin:total" )202```203204Projection methods: `property`, `group` (`groupProperty`), `sum`, `avg`,205`min`, `max`, `count`, `countDistinct`, `rowCount( [alias] )`, `id( [alias] )`.206One projection gives plain values; several give an array per row, or a207struct per row with `asStruct()`.208209| Method | `list()` returns |210| --- | --- |211| (default) | Entities |212| `asStruct()` | Structs: projections, or id and plain properties |213| `asStruct( includes, [opts] )` | `entityToStruct()`-style structs |214| `asQuery()` | A query object |215| `asStream()` | A Java stream |216| `asDistinct()` | Distinct rows |217| `resultTransformer( c.X )` | cborm constants, see below |218219- `asStruct()` dates are ISO 8601 strings, not date objects.220- `asStruct( "id,name,role.name,orders" )` reads with projection queries,221 without loading entities. Not allowed with `each()` or `chunk()`.222- `resultTransformer()` accepts `c.DISTINCT_ROOT_ENTITY` (drop repeated223 roots), `c.ROOT_ENTITY` (entities), `c.ALIAS_TO_ENTITY_MAP` (structs) and224 `c.PROJECTION`, for cborm code. A to-many condition already returns each225 root once, so it is rarely needed.226227## Ordering, Paging and Options228229```javascript230c.order( "lastName" ) // asc by default (alias orderBy)231c.order( "lastName", "desc" )232c.order( "lastName", "asc", true ) // ignore case233c.orderBy( "make desc, model asc" )234c.orderByDesc( "createdDate" )235c.firstResult( 20 ).maxResults( 10 ) // aliases offset/skip, limit/take236```237238Without `order()`, entity results use the entity's `defaultSort`.239240| Option | Effect |241| --- | --- |242| `cache( [region] )` | Query cache (alias `cacheable`); `cache( false )` off |243| `readOnly()` | Loaded entities are never saved |244| `timeout( seconds )` | Query timeout |245| `fetchSize( n )` | JDBC fetch size |246| `comment( text )` | SQL comment |247| `queryHint( name, value )` | Hibernate query hint |248| `lock( [mode], [opts] )` | Row lock, see below |249250## Terminal Methods251252| Method | Returns |253| --- | --- |254| `list()` | Rows; cborm args `list( max, offset, timeout, sortOrder, ... )` |255| `count( [property] )` | Matching roots, or distinct values of a property |256| `exists()` | Whether any row matches |257| `get( [uniqueFirst] )` | One row or null; several: `orm.query.nonUnique` |258| `getOrFail()` | Like `get()`; none is `orm.notFound` |259| `first()` / `firstOrFail()` | First row in order, or null / error |260| `paginate( page, maxRows )` | `{ results, pagination }` |261| `simplePaginate( page, maxRows )` | `{ results, pagination : { hasMore } }` |262| `pluck( property )` | One property's values |263| `sum`, `avg`, `min`, `max( p )` | An aggregate |264| `each( fn, [size] )` | Calls `fn` per row; returns the count |265| `chunk( size, fn )` | Calls `fn` per batch; returns the count |266| `updateAll( values )` / `deleteAll()` | One bulk statement; row count |267268```javascript269page = entityCriteria( "Order" )270 .isGe( "createdDate", dateAdd( "d", -30, now() ) )271 .orderByDesc( "createdDate" )272 .paginate( page = 2, maxRows = 25 )273// pagination : { page, maxRows, offset, totalRecords, totalPages }274275entityCriteria( "User" ).isFalse( "verified" ).chunk( 500, ( users ) => {276 users.each( ( u ) => u.setReminderSent( true ) )277} )278```279280`each()` and `chunk()` read in batches (100 by default for `each()`), and281flush and clear the session after each batch, so changes are saved and282memory stays flat.283284### Bulk Updates, Deletes and Locks285286```javascript287count = entityCriteria( "Order" )288 .isEq( "status", "pending" )289 .isLt( "createdDate", dateAdd( "d", -30, now() ) )290 .updateAll( { status : "expired", note : null } )291292count = entityCriteria( "Session" ).isLt( "expires", now() ).deleteAll()293294transaction {295 jobs = entityCriteria( "Job" ).isEq( "status", "new" )296 .lock( "write", { skipLocked : true } ) // read, write, force297 .maxResults( 10 )298 .list()299}300```301302Bulk statements fire no events, do not cascade, skip versions and303`autoTimestamp`, and leave loaded entities stale. `deleteAll()` on a304`softDelete` entity marks rows deleted.305306## Flow Helpers307308```javascript309entityCriteria( "User" )310 .when( len( search ), ( c ) => c.like( "lastName", search & "%" ) )311 .unless( showDeleted, ( c ) => c.isFalse( "isDeleted" ) )312 .apply( activeUsers ) // reusable: ( c ) => c.isTrue( "active" )313 .list()314```315316`when()` and `unless()` take an optional third "otherwise" closure;317`peek( fn )` runs a closure and keeps chaining.318319## Seeing the Query320321- `getSQL( [executable], [format] )`: the SQL, without running it.322 `executable = true` inlines the values; paging is not included.323- `getHQL()`: the HQL it compiles to. `peekSQL( fn )` passes the SQL to a324 closure and keeps chaining.325- `writeDump( c )` / `toString()`: the calls, HQL, params and SQL.326- cborm SQL log: `startSqlLog()`, `logSQL( label )`, `getSqlLog()`,327 `stopSqlLog()`.328329```javascript330writeOutput( entityCriteria( "User" ).isEq( "active", true ).getSQL( true ) )331```332333## Common Pitfalls334335- ❌ Calling `entityCriteria()` on bx-orm 1.x: it is 2.x only336- ❌ `count()` then `list()` on a projected criteria: `count()` clears the337 projections and `asStruct()`/`asQuery()` (cborm behavior). Count on a338 `copy()`, or set the shape after counting339- ❌ `like( "name", "Sm" )` without `%`: it is an exact match340- ❌ `get()` when several rows can match: `orm.query.nonUnique`; use341 `first()` or `get( true )`342- ❌ `lock()` outside `transaction {}`: `orm.argument`343- ❌ A `withClause` path that needs another join: `orm.argument`; use the new344 alias or earlier aliases, and put other conditions in the `where`345- ❌ `rightJoin` / `fullJoin` on MySQL or MariaDB (no full join support)346- ❌ `updateAll()` / `deleteAll()` with `maxResults()` or `firstResult()`347- ❌ Expecting `updateAll()`/`deleteAll()` to run events or cascades:348 `deleteAll()` can hit foreign keys, and loaded entities need349 `entityReload()` or `ormClearSession()`350- ❌ Holding entities across `each()`/`chunk()` batches: the session is351 cleared after every batch352- ❌ Running a `subquery()` on its own: pass it to `exists()`, `isIn()`, ...353- ❌ Reading `asStruct()` dates as date objects: they are ISO 8601 strings354- ✅ Paginate with an explicit `order()` so pages are stable355- ✅ Use `fetch()` for associations you read in a loop356- ✅ Use `getSQL()` or `writeDump()` to check what a criteria will run