get

The get method is the most common method used for retrieving results. It executes using the configured QueryBuilder and returns the results.

get can also take a list or array of columns to use as a shortcut. If any are passed, those columns will be used instead of any columns previously set on the QueryBuilder.

If you just need to retrieve a single row from the database table, you may use the first method. This method will return a single record (a Struct by default). If no row is found an empty Struct will be returned by default.

If you don't even need an entire row, you may extract a single value from each record using the values method. The values method will return the column of your choosing as a simple array.

An expression can also be passed to values:

The values method will return the expression given for each row as a simple array.

This method is similar to values except it only returns a single, simple value. Where values calls get under the hood, this method calls first.

If no records are returned from the query, one of two things will happen. If the throwWhenNotFound boolean is set to true, a RecordCountException will be thrown. Otherwise the defaultValue provided to the method will be returned.

An expression can also be passed to value:

The value method will return the expression given for the first row found.

Large datasets can be broken up and retrieved in chunks. This allows you to work with a subset of results at once to keep your memory footprint under control.

chunk can be called on any query like you would call get. You can stop the retrieving and processing early by returning false from the callback.

Generates a pagination struct along with the results of the executed query. It does this by calling both count and forPage.

Generates a simple pagination struct along with the results of the executed query. It does so without getting a count of the number of records the query would return. This can be desirable for performance reasons if your query count is rather large. It instead determines if there are more records by asking for one more row that your specified maxRows. If the number of rows returned exceeds your specified maxRows then the pagination returns hasMore: true. The results will always contain your specified maxRows (or less, if there aren't enough records).

A pagination collector is the name given to the struct returned from calling the method. It can be a struct or a component. It needs one function defined and will be passed the following parameters.

You can set your custom pagination collector either in the constructor using the paginationCollector argument or by calling setPaginationCollector on a query builder instance.

By default, qb ships with as its pagination collector. The return format of cbpaginator is the example shown above.

In qb 8.4.0 the simplePaginate method was added. This uses a new method on the paginationCollector.

The following methods all have the same return value:

{
    "result": "Value of the `result` parameter to `queryExecute`",
    "query": "Return value of running `queryExecute` - a CFML query object"
}

insert

You can insert a single record by passing a struct:

You can specify any options such as the SQL type by passing a struct with the parameters you would pass to .

Raw values can be supplied to an insert statement.

Multiple rows can be inserted in a batch by passing an array of structs to insert.

Inserts data into a table while ignoring duplicate key conflicts.

Inserts data into a table using a subquery as the source.

You can also pass in an array of column names to avoid aliasing in your source query.

Alternatively, the source can be defined as a QueryBuilder object:

Specifies columns to be returned from the insert query.

Updates a table with a struct of column and value pairs.

You can specify any options such as the SQL type by passing a struct with the parameters you would pass to .

Any constraining of the update query should be done using the appropriate statement before calling update.

You can update a column based on another column using a raw expression.

Null values can be inserted by using queryparam syntax:

if you are using full null support the following (easier) syntax is also allowed:

Subselects can be used to update values by passing a closure as the value

You can also pass a builder instance in place of the closure.

qb will correctly format JOIN clauses in your UPDATE statements for your database grammar.

Adds values to a later , similar to .

Performs an update statement if the configured query returns true for exists. Otherwise, performs an insert statement.

If an update statement is performed qb applies a limit( 1 ) to the update statement.

If the configured query returns 0 records, then an insert statement is performed.

An upsert is a batch operation that either inserts or updates a row depending on if a target match is found. If a row is matched with the target column(s), then the matched row is updated. Otherwise a new row is inserted.

The update clause in a upsert can also accept raw values, making it very useful for tracking data like statistics.

A source callback or QueryBuilder instance can be used instead of explicit values. This allows you to do upserts across tables or subqueries.

To do this, provide a source that is either a function to configure a new QueryBuilder instance or an already configured QueryBuilder instance. Then specify the columns that will be affected as an array of strings to values.

Deletes all records that the query returns.

The id argument is a convenience to delete a single record by id.

The query builder also provides a variety of aggregate methods such as count, max, min, and sum. These methods take the headache out of setting up these common aggregate functions.

When executing any of the aggregate functions, any where restrictions on your query will still be applied.

Instead of returning a query, these methods return a simple value.

exists

Returns true if the query returns any rows. Returns false otherwise.

Returns an integer number of rows returned by the query.

Returns the maximum value for the given column.

Returns the minimum value for the given column.

Returns the sum of all returned rows for the given column.

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.

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

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

This method allows you to create a table object.

The majority of the work comes from calling methods on the Blueprint object. A Blueprint defines the and for your tables.

Example:

This would convert to the following SQL in MySQL:

Only one table can be created at a time. If you wanted to create multiple tables, you would call create multiple times.

The callback argument is where you define the schema of your table. It is passed a Blueprint object. This is commonly aliased as table in the callback. Blueprint defines the field, index and constraint methods to build your table. You can find a comprehensive list of all available methods here for and here for .

A query builder is a stateful, transient object. That means that if you want to execute two different queries, you need two separate instances of QueryBuilder.

// This will cause you pain and grief...

var user = query.from( "users" )
  .where( "username", rc.username )
  .first();

var posts = query.from( "posts" ).get();
// This will error because `username` is not a column in `posts`.

As such, be careful when injecting QueryBuilder in to a component. If the component is a singleton, you will need to create the QueryBuilder inline or use a provider. This applies to ColdBox handlers as well.

component {

    property name="query" inject="QueryBuilder@qb";

    function create( event, rc, prc ) {
        query.table( "posts" )
            .where( "id", rc.id )
            .update( event.getOnly( [ "body" ] ) );
    }

}

While the above may seem innoculous, it can run in to issues as multiple requests come in to your application. Each request is sharing the same query builder instance and subsequent requests will have unintended results as the where clause keeps growing request after request.

The solution is to either create the QueryBuilder inline, ensuring that each request has its own query to execute:

component {

    function create( event, rc, prc ) {
        getInstance( "QueryBuilder@qb" )
            .table( "posts" )
            .where( "id", rc.id )
            .update( event.getOnly( [ "body" ] ) );
    }

}

Or to use a WireBox provider to create a new query each time it is accessed:

component {

    property name="query" inject="provider:QueryBuilder@qb";

    function create( event, rc, prc ) {
        query.table( "posts" )
            .where( "id", rc.id )
            .update( event.getOnly( [ "body" ] ) );
    }

}

newQuery

Once you have access to a QueryBuilder instance, you can create a new query using the same datasource, utils, returnFormat, paginationCollector, columnFormatter, and defaultOptions as the current QueryBuilder instance.

We welcome all types of contributions!

The most common type of contribution is to fix an incorrect SQL generation for a database grammar.

To debug what SQL is being ran, you can always call toSQL on any QueryBuilder or SchemaBuilder object. Additionally, you can listen to the preQBExecute interception point for the generated SQL.

Each of the database grammars have two tests — {Grammar}QueryBuilderSpec.cfc and {Grammar}SchemaBuilderSpec.cfc. These tests run the same qb syntax across the different grammars. In each test are methods that return SQL strings like so:

If you find an issue with the SQL generated from a grammar, please file a pull request with the correct SQL in these tests. It's okay if you don't submit a fix as well. (But we'd greatly appreciate it!) Doing so will help expedite the fix.

If you want to add support for a new database grammar, simply copy these two tests from an existing grammar, rename them, change the getBuilder method to return your new grammar, and fill out the SQL as it should be. That will guide your implementation to be 100% compatible with the other grammars in qb.

Custom Parameter Types

When passing a parameter to qb, it will infer the sql type to be used. If you pass a number, CF_SQL_NUMERIC will be used. If it is a date, CF_SQL_TIMESTAMP, and so forth. If you need more control, you can pass a struct with the parameters you would pass to cfqueryparam.

query.from( "users" )
    .where( "id", "=", { value = 18, cfsqltype = "CF_SQL_VARCHAR" } );

SELECT *
FROM `users`
WHERE `id` = ?

This can be used when inserting or updating records as well.

query.table( "users" )
    .insert( {
        "id" = { value 1, cfsqltype = "CF_SQL_VARCHAR" },
        "age" = 18,
        "updatedDate" = { value = now(), cfsqltype = "CF_SQL_DATE" }
    } );

INSERT INTO `users`
    (`id`, `age`, `updatedDate`)
VALUES
    (?, ?, ?)

Strict Date Detection

By default, qb will try to determine if a variable is a date using the built-in isDate function. This can have some interesting effects with different formatted strings. You can opt in to stricter date detection which will check the underlying Java class of the value to determine if the value is a date. This is more accurate, but does require you to specifically pass date instances instead of strings. For this reason, it is currently opt-in to not break existing applications. It is likely to become the default in the next major version of qb.

You can opt in to stricter date detection by setting strictDateDetection = true in your moduleSettings in config/ColdBox.cfc.

By default, qb will use the CF_SQL_NUMERIC SQL type when it detects a numeric binding. You can specify your own default SQL type to use with numeric values using the numericSQLType setting in your moduleSettings in config/ColdBox.cfc.

In some combinations of database grammars and CFML engines, the scale argument on a cfqueryparam would default to 0. This would cause issues when attempting to insert a floating point number, even when using the correct SQL type (i.e., CF_SQL_DECIMAL) . In 8.5.0, qb now automatically calculates a scale based on the value provided if the value is a floating point number. This can be disabled by setting autoAddScale in your ColdBox config or passing autoAddScale = false when instantiating your QueryBuilder instance.

Bindings are the values that will be sent as parameters to a prepared SQL statement. This protects you from In CFML, this uses to parameterize the values.

If you need to inspect the bindings for the current query you can retrieve them in order using the getBindings method.

This method returns the current bindings in order to be used for the query.

You can also retrieve the bindings associated to their corresponding types.

This method returns the current bindings to be used for the query associated to their corresponding types.

Common Table Expressions (CTEs) are powerful SQL concept that allow you to create re-usable temporal result sets, which can be referenced as a table within your SQL. CTEs are available in many common database engines and are available in latest versions of all of the support grammars.

CTEs come in two basic types:

• Non-recursive — These are statements that do not reference themselves, in simplified terms they are like a derived table that can be referenced by a user-defined name.

• Recursive — Recursive CTEs reference themselves and are generally used for creating hierarchical data—such as creating a parent/child relationship within a table.

While all of the grammars currently support CTEs, there is enough difference between the various databases implementations of CTEs that unless your CTEs are fairly basic, using CTEs within your project will most likely tie your project to a specific database, unless you account for the differences in your code.

However, CTEs are can be extremely useful to solve certain use cases.

To add CTEs to your queries, you have two methods available:

• with() — Allows you to define a non-recursive CTE.

• withRecursive() — Allows you to define a recursive CTE.

You can build a CTE using a function:

Alternatively, you can use a QueryBuilder instance instead of a function:

A single query can reference multiple CTEs:

Here is an example of building a recursive CTE using SQL Server which would return all parent/child rows and show their generation/level depth:

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.

Debugging a Single Query

toSQL

Returns the SQL that would be executed for the current query.

The bindings for the query are represented by question marks (?) just as when using queryExecute. qb can replace each question mark with the corresponding cfqueryparam-compatible struct by passing showBindings = true to the method.

If you want to show the SQL that would be executed for the update, insert, updateOrInsert, or delete methods, you can pass a toSQL = true flag to those methods. Please see those individual methods for more information.

Executes a callback with a clone of the current query passed to it. Any changes to the passed query is ignored and the original query returned.

While not strictly a debugging method, tap makes it easy to see the changes to a query after each call without introducing temporary variables.

A shortcut for the most common use case of tap. This forwards on the SQL for the current query to writeDump. You can pass along any writeDump argument to dump and it will be forward on. Additionally, the showBindings argument will be forwarded on to the toSQL call.

Starting in 2.0.0 you can view all your qb queries for a request. This is enabled by default if you have qb installed. Make sure your debug output is configured correctly and scroll to the bottom of the page to find the debug output.

qb is set to log all queries to a debug log out of the box. To enable this behavior, configure LogBox to allow debug logging from qb's grammar classes.

ColdBox Interception Points can also be used for logging, though you may find it easier to use LogBox. See the documentation for for more information.

A TableIndex can be created directly from a Blueprint or from a existing Column. The TableIndex includes methods for further configuring the index which is required when defining foreign keys.

references

Set the referencing column for a foreign key relationship. For example, id for a country_id column.

Example:

SchemaBuilder

SQL (MySQL)

Sets the referencing table for a foreign key relationship. For example, countries for a country_id column.

Example:

SchemaBuilder

SQL (MySQL)

Set the strategy for updating foreign keys when the parent key is updated.

Example:

SchemaBuilder

SQL (MySQL)

Set the strategy for updating foreign keys when the parent key is deleted.

Example:

SchemaBuilder

SQL (MySQL)

Installation

Installation is easy through CommandBox and ForgeBox. Simply type box install qb to get started.

Usage

To start a new query, instantiate a new Builder: wirebox.getInstance('QueryBuilder@qb').

By default, qb uses a generic Grammar. You can specify your specific grammar in ColdBox by setting the defaultGrammar in your moduleSettings.

moduleSettings = {
    qb = {
        defaultGrammar = "MySQLGrammar@qb"
    }
};

The grammars provided by qb are:

• MySQLGrammar

• OracleGrammar

• PostgresGrammar

• SqlServerGrammar

If you are not using WireBox, just make sure to wire up the Builder object with the correct grammar:

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:

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.

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.

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().

Sometimes you want to add constraints on a table level, rather than a column level. The following methods will let you accomplish that.

Create a generic index from one or more columns.