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"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:
You can get the original query object that CFML generates by setting the returnFormat to query:
This setting can be overridden on a per-instance basis by calling setReturnFormat():
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": "array"
}
};moduleSettings = {
"qb": {
"returnFormat": "query"
}
};var qb = wirebox.getInstance( "QueryBuilder@qb" );
qb
.setReturnFormat( 'query' )
.from( 'users' )
.get()moduleSettings = {
"qb": {
"returnFormat": function( q ) {
return application.wirebox.getInstance(
"name" = "Collection",
"initArguments" = { "collection": q }
);
}
}
};Each query execution method allows for the passing of an options struct. This is the same struct you would pass to queryExecute.
qb allows you to specify default options when creating the QueryBuilder instance using the defaultOptions argument. You can combine this with WireBox to create custom QueryBuilder instances pointing to different datasources and even different grammars.
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:
If you also want to use a non-default SQL Grammar you have to specify this when creating your QueryBuilder.
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"
} );
}
}query.from( "users" )
.get( options = { datasource: "MyOtherDatasourceName" } );var query = wirebox.getInstance( "QueryBuilder@qb" )
.setGrammar( wirebox.getInstance( "MSSQLGrammar@qb" ) );Two interception points are available from QB: preQBExecute and postQBExecute. These fire before and after the queryExecute call, respectively.
The following information is available in the interceptData struct:
The following information is available in the interceptData struct:
The type to return: query or result.
query
Query | null
The query object or null if there isn't one.
result
Struct
The query result struct.
Name
Type
Description
sql
String
The SQL string to execute.
bindings
Struct
The struct of bindings (keys and values) for the query.
options
Struct
Any options to pass along to queryExecute.
returnObject
String
Name
Type
Description
sql
String
The SQL string to execute.
bindings
Struct
The struct of bindings (keys and values) for the query.
options
Struct
Any options to pass along to queryExecute.
returnObject
The type to return: query or result.
String
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 );
} );