Two interception points are available from QB: preQBExecute and postQBExecute. These fire before and after the queryExecute call, respectively.

preQBExecute

The following information is available in the interceptData struct:

postQBExecute

The following information is available in the interceptData struct:

Clone

At times you may need to duplicate a query. Using clone you have a performant way to duplicate a query without using the duplicate method.

var q1 = query.from( "users" ).where( "firstName", "like", "Jo%" );
var q2 = q1.clone();
q2.getFrom(); // "users"

Reset

When you need to remove all configuration for a query, you can call the reset method.

var q1 = query.from( "users" ).where( "firstName", "like", "Jo%" );
var q2 = q1.reset();
q2.getColumns(); // "*"

Each query execution method allows for the passing of an options struct. This is the same struct you would pass to queryExecute.

Default Options

qb allows you to specify default options when creating the QueryBuilder instance using the defaultOptions argument.

You can set defaultOptions for the default QueryBuilder (QueryBuilder@qb) in your config/ColdBox.cfc file under moduleSettings.

moduleSettings = {
    "qb": {
        "defaultOptions": {
            "timeout": 60
        }
    }
};

You can also combine this with WireBox to create custom QueryBuilder instances pointing to different datasources and even different grammars.

component {

    function afterAspectsLoad() {
        binder.map( "MyCustomQueryBuilder" )
            .to( "qb.models.Query.QueryBuilder" )
            .initArg( name = "grammar", ref = "AutoDiscover@qb" )
            .initArg( name = "defaultOptions", value = {
                "datasource": "my_custom_datasource" 
            } );
    }

}

Retrieving results from alternative datasources

In Application.cfc you can specify your default datasource which will be used by qb. If you want to retrieve data from other datasources you can specify this in all retrieval functions by using the extra options parameter such as:

query.from( "users" )
    .get( options = { datasource: "MyOtherDatasourceName" } );

If you also want to use a non-default SQL Grammar you have to specify this when creating your QueryBuilder.

var query = wirebox.getInstance( "QueryBuilder@qb" )
    .setGrammar( wirebox.getInstance( "MSSQLGrammar@qb" ) );

Available as an advanced option for framework authors, qb will call out to a column formatter prior to processing a column as part of the SQL query. This allows frameworks like Quick to define queries using aliases and transform them to columns during execution.

You can provide your own column formatter function to qb through the init method or by calling setColumnFormatter. It is a function that takes a column string and returns a string

query.setColumnFormatter( function( column ) {
    return lcase( arguments.column );
} );

returnFormat refers to the transformation your executed query makes (if any) before being returned to you. You can choose one of three return formats:

• "array"

• "query"

• A custom function

By default, qb returns an array of structs as the result of your query. This is the same as specifying array as your returnFormat:

moduleSettings = {
    "qb": {
        "returnFormat": "array"
    }
};

You can get the original query object that CFML generates by setting the returnFormat to query:

moduleSettings = {
    "qb": {
        "returnFormat": "query"
    }
};

This setting can be overridden on a per-instance basis by calling setReturnFormat():

var qb = wirebox.getInstance( "QueryBuilder@qb" );

qb
   .setReturnFormat( 'query' )
   .from( 'users' )
   .get()

If you want complete control over your return result, you can provide a function as a returnFormat. The results of the function will be returned as the results of the builder.

moduleSettings = {
    "qb": {
        "returnFormat": function( q ) {
            return application.wirebox.getInstance(
                "name" = "Collection",
                "initArguments" = { "collection": q }
            );
        }
    }
};