If you pass a structure as the last argument to your dynamic finder/counter call, we will consider that by convention to be your query options.

The valid query options are:

• ignorecase : Ignores the case of sort order when you set it to true.

• maxResults : Specifies the maximum number of objects to be retrieved.

• offset : Specifies the start index of the resultset from where it has to start the retrieval.

• cacheable : Whether the result of this query is to be cached in the secondary cache. Default is false.

• cachename : Name of the cache in secondary cache.

• timeout : Specifies the timeout value (in seconds) for the query

• datasource : The datasource to use, it defaults to the application

• sortBy : The HQL to sort the query by

• autoCast : No more casting, let us do auto casting for you

• asStream : Want a stream back instead of the results, no problem!

Here is a more descriptive key set with the types and defaults

A method expression is made up of the prefixes: findBy, findAllBy, countBy followed by the expression that combines a query upon one or more properties:

User.findBy{property}[Conditional=equal][Operator]?{property}[Conditional][Operator]
User.findAllBy{property}[Conditional=equal][Operator]?{property}[Conditional][Operator]
User.countBy{property}[Conditional=equal][Operator]?{property}[Conditional][Operator]

If a conditional keyword is not passed, we assume you want equality. Remember that!

Conditionals

The available conditionals in ColdBox are:

• LessThanEquals - Less than or equal to passed value

• LessThan - Less than to passed value

• GreaterThanEquals - Greater than or equal to passed value

• GreaterThan - Greater than to passed value

• Like - Equivalent to the SQL like expression

• NotEqual - Not equal to the passed value

• isNull - The property must be null

• isNotNull - The property must not be null

• NotBetween - The property value must not be between two values

• Between - The property value must be between two values

• NotInList - The property value must not be in the passed in simple list or array

• inList - The property value must be in the passed in simple list or array

Operators

The only valid operators are:

• And

• Or

The ORM module supports the concept of dynamic finders and counters for ColdFusion ORM entities. A dynamic finder/counter looks like a real method but it is a virtual method that is intercepted by via onMissingMethod. This is a great way for you to do finders and counters using a programmatic and visual representation of what HQL to run.

This feature works on the Base ORM Service, Virtual Entity Services and also Active Entity services. The most semantic and clear representations occur in the Virtual Entity Service and Active Entity as you don't have to pass an entity name around.

users = getInstance( "User" )
    .findAllByLastLoginBetweeninGreaterThan( "01/01/2010" );

users = getInstance( "User" )
    .findAllByLastLoginGreaterThanAndLastNameLike( "01/01/2010", "jo%" );

count = getInstance( "User" )
    .countByLastLoginGreaterThan( "01/01/2010" );

count = getInstance( "User" )
    .countByLastLoginGreaterThanAndLastNameLike( "01/01/2010", "jo%" );

Automatic Casting

Another important aspect of the dynamic finders is that we will AUTO CAST all the values for you. So you don't have to mess with the right Java type, we will do it for you.

Streams

We have also enabled the ability to return a stream of objects if you are using the findAll semantics via cbStreams.

userStream = getInstance( "User" )
    .findAllByLastLoginBetweeninGreaterThan( "01/01/2010", {asStream:true} );

We have three types of dynamic finders and counters:

• findBy : Find ONE entity according to method signature, if more than one record is found an exception is thrown

• findAllBy : Find ALL entities according to method signature

• countBy : Give you a count of entities according to method signature

Let's say you have the following entity:

component persistent="true" name="User" extends="cborm.models.ActiveEntity"{

     property name="id" column="user_id" fieldType="id" generator="uuid";
     property name="lastName";
     property name="userName";
     property name="password";
     property name="lastLogin" ormtype="date";
}

Then we could do the following:

user = getInstance( "User" ).findByLastName( "Majano" );

users = getInstance( "User" ).findAllByLastNameLike( "Ma%" );

users = getInstance( "User" ).findAllByLastLoginBetween( "01/01/2010", "01/01/2012" );

users = getInstance( "User" ).findAllByLastLoginGreaterThan( "01/01/2010" );

users = getInstance( "User" ).findAllByLastLoginGreaterThanAndLastNameLike( "01/01/2010", "jo%" );

count = getInstance( "User" ).countByLastLoginGreaterThan( "01/01/2010" );

count = getInstance( "User" ).countByLastLoginGreaterThanAndLastNameLike( "01/01/2010", "jo%" );

You can also use the virtual entity service instead of active entity.

// Get a virtual entity service via DI, there are many ways to get a virtual entity service
// Look at virtual entity service docs for retrieval
property name="userService" inject="entityservice:userService";

user = userService.findByLastName( "Majano" );

users = userService.findAllByLastNameLike( "Ma%" );

users = userService.findAllByLastLoginBetween( "01/01/2010", "01/01/2012" );

users = userService.findAllByLastLoginGreaterThan( "01/01/2010" );

users = userService.findAllByLastLoginGreaterThanAndLastNameLike( "01/01/2010", "jo%" );

count = userService.countByLastLoginGreaterThan( "01/01/2010" );

count = userService.countByLastLoginGreaterThanAndLastNameLike( "01/01/2010", "jo%" );

If you just use a vanilla Base ORM Service, then the first argument must be the entityName:

// Get a virtual entity service via DI, there are many ways to get a base entity service
// Look at base entity service docs for retrieval
property name="userService" inject="entityservice";

user = userService.findByLastName( "User", "Majano" );