Installation & Usage
To start a new query, instantiate a new Builder:
wirebox.getInstance('[email protected]')
.By default, qb uses a generic Grammar. You can specify your specific grammar in ColdBox by setting the
defaultGrammar
in your moduleSettings
.The grammars provided by qb are:
- MySQLGrammar
- OracleGrammar
- PostgresGrammar
- SqlServerGrammar
- SQLiteGrammar
If you are not using WireBox, make sure to wire up the
Builder
object with the correct grammar:var grammar = new qb.models.Grammars.MySQLGrammar();
var builder = new qb.models.Query.QueryBuilder( grammar );
Here are the full configuration settings you can use in the module settings:
moduleSettings = {
qb : {
"defaultGrammar": "[email protected]",
"defaultReturnFormat": "array",
"preventDuplicateJoins": false,
"strictDateDetection": true,
"numericSQLType": "CF_SQL_NUMERIC",
"integerSQLType": "CF_SQL_INTEGER",
"decimalSQLType": "CF_SQL_DECIMAL",
"autoAddScale": true,
"autoDeriveNumericType": true,
"defaultOptions": {},
"sqlCommenter": {
"enabled": false,
"commenters": [
{ "class": "[email protected]", "properties": {} },
{ "class": "[email protected]", "properties": {} },
{ "class": "[email protected]", "properties": {} }
]
},
"shouldMaxRowsOverrideToAll": function( maxRows ) {
return maxRows <= 0;
}
}
}
QB binds all parameters by default and guesses the SQL type based on passed values. The default SQL type for numeric values is
CF_SQL_NUMERIC
, which is a floating point number, for the widest compatibility. This can cause performance problems with large recordsets in some database engines. You can provide a different default in coldbox.cfc
if you wish to override this setting:moduleSettings = {
qb = {
defaultGrammar = "[email protected]",
numericSQLType = "CF_SQL_BIGINT"
}
};
Note: These instructions assume a basic knowledge of FW/1, a working FW/1 application structure with qb installed in the/subsystems
directory (manually or via CommandBox), and a database configured to run with your application.
Once the application structure is setup, now we need to wire up qb to a bean factory using DI/1.
First we will add a mapping in
Application.cfc
.this.mappings = {
"/qb" = expandPath("./subsystems/qb")
};
Next we need to tell DI/1 where qb's components are and how to reference them for later use in the application. We can do so by defining the configuration settings in the
variables.framework.subsystems
struct in Application.cfc
. The example below makes use of a load listener to declare each component instance and pass in any constructor arguments.qb = {
diLocations = "/qb/models",
diConfig = {
loadListener = function( di1 ) {
di1.declare( "BaseGrammar" ).instanceOf( "qb.models.Query.Grammars.Grammar" ).done()
.declare( "MySQLGrammar" ).instanceOf( "qb.models.Query.Grammars.MySQLGrammar" ).done()
.declare( "QueryUtils" ).instanceOf( "qb.models.Query.QueryUtils" ).done()
.declare( "QueryBuilder" ).instanceOf( "qb.models.Query.QueryBuilder" )
.withOverrides({
grammar = di1.getBean( "MySQLGrammar" ),
utils = di1.getBean( "QueryUtils" ),
returnFormat = "array"
})
.asTransient();
}
}
}
Now that everything is configured, you can launch your application with CommandBox by entering
start
in the terminal or use whatever method you're accustomed to.To access qb from your application's code, you can call on it by using
getBeanFactory()
.// Create an instance of qb
builder = getBeanFactory( "qb" ).getBean( "QueryBuilder" );
// Query the database
posts = builder.from( "Posts" ).get();
posts = builder.from( "Posts" ).where( "IsDraft", "=", 0 ).get();
Last modified 10d ago