1 of 45

13.0.0 Introduction

qb is a fluent query builder for CFML. It is heavily inspired by from .

Using qb, you can:

Quickly scaffold simple queries

Installation & Usage

Installation

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

Contributing & Filing Issues

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:

// MSSQLQueryBuilderSpec.cfc
function orWhere() {
    // If just a string is returned, we assume the bindings is an empty array ([])
    return {
        sql = "SELECT * FROM [users] WHERE [id] = ? OR [email] = ?",
        bindings = [ 1, "foo" ]
    };
}

// OracleSchemaBuilderSpec.cfc
function boolean() {
    // returns an array since schema builder can execute multiple statements.
    return [ "CREATE TABLE ""USERS"" (""ACTIVE"" NUMBER(1, 0) NOT NULL)" ];
}

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.

Query Builder

Getting a New Query

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.

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.

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:

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

Building Queries

For

This section only applies to SQL Server Grammars.

In SQL Server, FOR clauses are how you can return JSON or XML directly from your query.

In qb, only raw expressions are accepted via the forRaw method.

forRaw

Name

Type

Required

Default

Description

Raw Expressions

Raw expressions are the qb escape hatch. While qb strives to provide ways to execute the majority of queries, you will occasionally need to provide raw sql values that are not processed by qb. These SQL snippets are called raw or Expressions in qb.

raw expressions are useful, but shoud be used only if there is not another way to accomplish the same action using other qb methods. This is because a raw expression has the potential to use syntax specific to one database grammar or another, preventing you from easily switching from one grammar to another, one of the major benefits of using qb.

The first way to retrieve an Expression is to call the raw method on the QueryBuilder object.

raw

The sql snippet passed to raw is not processed by qb at all. With that in mind, it is important to follow all best practices and security recommendations with the sql you use with raw.

Expressions can be passed to most qb methods, like select, from, where, or orderBy, among others. Additionally, qb provides some convenience methods to add raw values in different parts of the query:

When / Conditionals

If you store the builder object in a variable, you can use if and else statements like you would expect.

This works, but breaks chainability. To keep chainability you can use the when helper method.

`when`

Executing Queries

Options and Utilities

Query Options and Utilities

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

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

Clone and Reset

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.

QueryBuilder

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.

Return Format

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"
"none"
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.

Column Formatter

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 );
} );

Interception Points

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:

Schema Builder

Creating Tables and Views

create

This method allows you to create a table object.

Argument

Type

Required

Default

Description

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 .

createAs

This method allows you to create a table using a query. It is similar to a view except that the data is inserted once at table creation.

This is an UnsupportedOperation on DerbyGrammar.

Argument

Type

Required

Default

Description

Like with a view, the columns are defined by the data returned by the query. The data returned by the query will be inserted into the table.

createView

This method allows you to create a view using a query.

Argument

Type

Required

Default

Description

Debugging

pretend

A SchemaBuilder instance can be put into pretend mode by calling the pretend method. In this mode, the SchemaBuilder will turn all query operations into no-ops. A log of the SQL that would have been executed can be retrieved from the query log.

Once a SchemaBuilder instance has been set to pretend mode, it cannot be unset. Instead, you will need to obtain a new SchemaBuilder instance.

queryLog

Each instance of a SchemaBuilder maintains a log of queries it executed. This can be accessed by calling getQueryLog. This will return an array of structs like so:

This can be very useful in combination with the feature to see what SQL will be executed before actually executing it.

External Links

// Plain old CFML
var results = queryExecute(
    "SELECT * FROM posts WHERE published_at IS NOT NULL AND author_id IN ?",
    [ { value = "5,10,27", cfsqltype = "CF_SQL_NUMERIC", list = true } ]
);

// qb
var qb = wirebox.getInstance( "QueryBuilder@qb" );
var results = qb.from( "posts" )
    .whereNotNull( "published_at" )
    .whereIn( "author_id", [ 5, 10, 27 ] )
    .get();

var qb = wirebox.getInstance( "QueryBuilder@qb" );
var results = qb.from( "posts" )
         .orderBy( "published_at" )
         .select( "post_id", "author_id", "title", "body" )
         .whereLike( "author", "Ja%" )
         .join( "authors", "authors.id", "=", "posts.author_id" )
         .get();

// Becomes
var results = queryExecute(
    "SELECT post_id, author_id, title, body FROM posts INNER JOIN authors ON authors.id = posts.author_id WHERE author LIKE ? ORDER BY published_at",
    [ { value = "Ja%", cfsqltype = "CF_SQL_VARCHAR", list = false, null = false } ]
);

config/ColdBox.cfc

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

moduleSettings = {

    qb : {
        "defaultGrammar": "AutoDiscover@qb",
        "defaultReturnFormat": "array",
        "preventDuplicateJoins": false,
        "convertEmptyStringsToNull": true,
        "numericSQLType": "NUMERIC",
        "integerSQLType": "INTEGER",
        "decimalSQLType": "DECIMAL",
        "defaultOptions": {},
        "sqlCommenter": {
            "enabled": false,
            "commenters": [
                { "class": "FrameworkCommenter@qb", "properties": {} },
                { "class": "RouteInfoCommenter@qb", "properties": {} },
                { "class": "DBInfoCommenter@qb", "properties": {} }
            ]
        },
        "shouldMaxRowsOverrideToAll": function( maxRows ) {
            return maxRows <= 0;
        }
    }

}

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();
    }
  }
}

// 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();

schema.create( "users", function( table ) {
    table.increments( "id" );
    table.string( "email" );
    table.string( "password" );
    table.timestamp( "created_date" );
    table.timestamp( "modified_date" );
    table.timestamp( "last_logged_in" ).nullable();
} );

CREATE TABLE `users` (
    `id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `created_date` TIMESTAMP NOT NULL,
    `modified_date` TIMESTAMP NOT NULL,
    `last_logged_in` TIMESTAMP,
    CONSTRAINT `pk_users_id` PRIMARY KEY (`id`)
)

[
  {
    "sql": "CREATE TABLE `users` (`id` INT PRIMARY KEY AUTO_INCREMENT, `email` VARCHAR NOT NULL)",
    "bindings": [],
    "options": { "datasource": "main" },
    "returnObject": "array",
    "pretend": false,
    "result": {},
    "executionTime": 21
  }
]

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`.

handlers/posts.cfc

component {

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

    function create( event, rc, prc ) {
        // This will cause you pain and grief...
        query.table( "posts" )
            .where( "id", rc.id )
            .update( event.getOnly( [ "body" ] ) );
    }

}

handlers/posts.cfc

component {

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

}

handlers/posts.cfc

component {

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

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

}

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

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

var posts = query.newQuery().from( "posts" ).get();
// This will work as we expect it to.

config/ColdBox.cfc

moduleSettings

config/WireBox.cfc

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" 
            } );
    }

}

QueryBuilder

query.from( "posts" )
    .when(
        someFlag,
        function( q ) {
            q.orderBy( "published_date", "desc" );
        },
        function( q ) {
            q.orderBy( "modified_date", "desc" );
        }
    );

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE CASCADE ON DELETE NO ACTION
)

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE NO ACTION ON DELETE SET NULL
)

SELECT * FROM foo /*action='index',dbDriver='mysql-connector-java-8.0.25%20%28Revision%3A%2008be9e9b4cba6aa115f9b27b215887af40b159e0%29',event='Main.index',framework='coldbox-6.0.0',handler='Main',route='%2F'*/

settings = {
    "sqlCommenter": {
        "enabled": false,
        "commenters": [
            { "class": "FrameworkCommenter@qb", "properties": {} },
            { "class": "RouteInfoCommenter@qb", "properties": {} },
            { "class": "DBInfoCommenter@qb", "properties": {} }
        ]
    }
};

component singleton accessors="true" {

    property name="coldboxVersion" inject="coldbox:coldboxSetting:version";

    property name="properties";

    /**
     * Returns a struct of key/value comment pairs to append to the SQL.
     *
     * @sql         The SQL to append the comments to. This is provided if you need to
     *              inspect the SQL to make any decisions about what comments to return.
     * @datasource  The datasource that will execute the query. If null, the default datasource will be used.
     *              This can be used to make decisions about what comments to return.
     */
    public struct function getComments( required string sql, string datasource ) {
        return { "version": "coldbox-#variables.coldboxVersion#" };
    }

}

component singleton accessors="true" {

    property name="auth" inject="AuthenticationService@cbauth";

    property name="properties";

    /**
     * Returns a struct of key/value comment pairs to append to the SQL.
     *
     * @sql         The SQL to append the comments to. This is provided if you need to
     *              inspect the SQL to make any decisions about what comments to return.
     * @datasource  The datasource that will execute the query. If null, the default datasource will be used.
     *              This can be used to make decisions about what comments to return.
     */
    public struct function getComments( required string sql, string datasource ) {
        return { "userId": variables.auth.getUserId() };
    }

}

getInstance( "ColdBoxSQLCommenter@qb" )
    .parseCommentedSQL( "SELECT * FROM foo /*action='index',dbDriver='mysql-connector-java-8.0.25%20%28Revision%3A%2008be9e9b4cba6aa115f9b27b215887af40b159e0%29',event='Main.index',framework='coldbox-6.0.0',handler='Main',route='%2F'*/" );

Result

{
    "sql": "SELECT * FROM foo",
    "comments": {
        "action": "index",
        "dbDriver": "mysql-connector-java-8.0.25 (Revision: 08be9e9b4cba6aa115f9b27b215887af40b159e0)",
        "event": "Main.index",
        "framework": "coldbox-6.0.0",
        "handler": "Main",
        "route": "/"
    }
}

getInstance( "ColdBoxSQLCommenter@qb" )
    .parseCommentString(
        "/*action='index',dbDriver='mysql-connector-java-8.0.25%20%28Revision%3A%2008be9e9b4cba6aa115f9b27b215887af40b159e0%29',event='Main.index',framework='coldbox-6.0.0',handler='Main',route='%2F'*/"
    );

Result

{
    "action": "index",
    "dbDriver": "mysql-connector-java-8.0.25 (Revision: 08be9e9b4cba6aa115f9b27b215887af40b159e0)",
    "event": "Main.index",
    "framework": "coldbox-6.0.0",
    "handler": "Main",
    "route": "/"
}

You can pass include any parameters you would use with cfqueryparam including null, list, etc. This applies anywhere parameters are used including where, update, and insert methods.

QueryBuilder

query.from( "users" )
    .join( "logins", function( j ) {
        j.on( "users.id", "logins.user_id" );
        j.where( "logins.created_date", ">", dateAdd( "m", -1, "01 Jun 2019" ) );
    } )
    .where( "active", 1 );

QueryBuilder

query.from( "users" )
    .join( "logins", function( j ) {
        j.on( "users.id", "logins.user_id" );
        j.where( "logins.created_date", ">", dateAdd( "m", -1, "01 Jun 2019" ) );
    } )
    .where( "active", 1 );

Result

{
    "commonTables" = [],
    "select" = [],
    "join" = [
        { value = "01 May 2019", cfsqltype = "CF_SQL_TIMESTAMP"  },
    ],
    "where" = [
        { value = 1, cfsqltype = "CF_SQL_NUMERIC" }
    ],
    "union" = [],
    "insert" = [],
    "insertRaw" = [],
    "update" = []
};

SELECT *
FROM `users`
WHERE `id` = ?
LOCK IN SHARE MODE

SELECT *
FROM [users] WITH (ROWLOCK,HOLDLOCK)
WHERE [id] = ?

SELECT *
FROM "users"
WHERE "id" = ?
FOR SHARE

LOCK TABLE "USERS"
IN SHARE MODE NOWAIT;

SELECT *
FROM "USERS"
WHERE "ID" = ?

QueryBuilder

query.select( [ "firstName", "lastName" ] )
    .fromSub( "legalUsers", function ( q ) {
        q.select( [ "lName as lastName", "fName as firstName" ] )
            .from( "users" )
            .where( "age", ">=", 21 )
        ;
    } )
    .orderBy( "lastName" )
    .get()

QueryBuilder

var legalUsersQuery = query
    .select( [ "lName as lastName", "fName as firstName" ] )
    .from( "users" )
    .where( "age", ">=", 21 );

query.select( [ "firstName", "lastName" ] )
    .fromSub( "legalUsers", legalUsersQuery )
    .orderBy( "lastName" )
    .get();

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)

schema.create( "posts_users", function( table ) {
    table.unsignedInteger( "post_id" ).references( "id" ).onTable( "posts" );
    table.unsignedInteger( "user_id" ).references( "id" ).onTable( "users" );
    table.primaryKey( [ "post_id", "user_id" ], "pk_posts_users" );
} );

CREATE TABLE `posts_users` (
    `post_id` VARCHAR(255) NOT NULL,
    `user_id` VARCHAR(255) NOT NULL,
    INDEX `idx_users_full_name` (`first_name`, `last_name`),
    CONSTRAINT `fk_posts_users_post_id` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `fk_posts_users_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT ""pk_users_first_name_last_name"" PRIMARY KEY (""first_name"", ""last_name"")
)

QueryBuilder

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .union( function ( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 2 );
    } );

QueryBuilder

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .union( function ( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 2 );
    } )
    .union( function ( q ) {
        q.from( "users" )
            .select("name")
            .where( "id", 3 );
    } );

QueryBuilder

var q1 = query.newQuery()
    .from( "users" )
    .select( "name" )
    .where( "id", 2 );
    
var q2 = query.newQuery()
    .from( "users" )
    .select( "name" )
    .where( "id", 3 );

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .union( q1 )
    .union( q2 );

QueryBuilder

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .unionAll( function( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 2 );
     } );

QueryBuilder

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .unionAll( function( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 2 );
     } )
    .unionAll( function( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 3 );
     } );

QueryBuilder

var q1 = query.newQuery()
    .from( "users" )
    .select( "name" )
    .where( "id", 2 );
    
var q2 = query.newQuery()
    .from( "users" )
    .select( "name" )
    .where( "id", 3 );

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .unionAll( q1 )
    .unionAll( q2 );

queryExecute

defaultSchema

hasTable

hasColumn

schema

Common Table Expressions (i.e. CTEs)

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.

Some database engines require the recursive keyword anytime at least one of your CTEs is recursive, but some database engines (e.g. SQL Server and Oracle) do not require the keyword. qb will manage adding the keyword, if necessary. If your query does use recursion you should use the withRecursive()method to avoid issues when migrating grammars.

with

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:

withRecursive

IMPORTANT — The way the SQL in a recursive CTEs are written, using them in your code is likely to lock in you in to a specific database engine, unless you structure your code to build the correct SQL based on the current grammar being used.

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:

Migration Guide

v13.0.0

Columns are now stored in a different format internally

Before v13.0.0, internally columns were stored as string values or raw Expression instances. Now, they are stored as structs representing the column. This was to allow aliasing and renaming of subselect columns more easily.

This change shouldn't break anything for end users. The only people who need to check their code are Grammar authors, as the Grammars will now be passed an array of column structs instead of simple values.

Column types follow the format: { "type": "string", "value": "any" }. For simple columns this shows as { "type": "simple", "value": "name" }. For expressions this shows as { "type": "raw", "value": Expression Instance } , etc.

Query Param structs are now validated when adding to a query

Previously, when passing a query param struct as a binding, qb would use the keys it cared about and would ignore the rest. Now, qb will validate the incoming param to make sure it is a valid query param struct. A valid query param struct contains NO keys that are not found on cfqueryparam. The main reason for this is to catch bugs where the value of a column should be JSON and instead of passing the result of serializeJSON the struct itself is passed.

This change may cause some of your existing queries to begin throwing QBInvalidQueryParam exceptions. Remove the non-standard keys to fix the error.

v12.0.0

Remove `autoAddScale` setting

It is no longer possible to disable auto scale being added. You can still override any scale by providing it in your query param struct.

Remove `strictDateDetection` setting

It is no longer possible to disable strict date detection being performed. You can still override the cfsqltype by specifying it in your query param struct.

Remove `autoDeriveNumericType` setting

It is no longer possible to disable the numeric type detection. You can specify the numeric types you want used in your settings. You can also override the cfsqltype by specifying it in your query param struct.

Argument order changed for some aggregate functions

The , , , and methods now accept a defaultValue argument. This comes before the defaultOptions argument. If you are using positional parameters with any of these functions, update your code to the new method signature.

Argument order changed for QueryUtils initializer

This only affects people instantiating QueryUtils manually (such as non-ColdBox users) and instantiating with positional arguments.

Please review the QueryUtils init function and update your code to the new method signature, if needed.

Generated `cfsqltype` attributes no longer include the `CF_SQL_` prefix

Although it shouldn't impact any running application, out of an abundance of caution, we are labeling the drop of theCF_SQL_ prefix as a breaking change. This prefix has been optional since Adobe ColdFusion 11.

v11.0.0

Auto Boolean Casting

Grammars will be able to influence the cfsqltype and value when passing in a literal boolean value as a binding. Postgres and SQLite have boolean support, so they will keep the literal boolean value and use acfsqltype of CF_SQL_OTHER. SQL Server uses CF_SQL_BIT, Oracle usersCF_SQL_NUMERIC, and MySQL uses CF_SQL_TINYINT — all of these will convert literal boolean values to either 1 or 0. This behavior is skipped when providing a custom cfsqltype. Custom grammars can implement thegetBooleanSqlType and convertBooleanValue methods to customize this behavior.

Most people will not need to change anything in their code for this breaking change.

v10.0.0

Dropped Support for Adobe Coldfusion 2018

Internal variables renamed for compatibility with BoxLang and cleaner code in general

In certifying qb for BoxLang, we discovered that some of the way qb had worked for years was due to a lucky interaction between properties and functions sharing a name. Both of these values are put into the variables scope, and the way qb shared some of these names like the from method as well as the from property only worked because of the way Lucee and ACF ordered defining the function and properties. BoxLang is more strict in this regard and probably for the best. You can probably imagine how setting variables.from inside a function called from would maybe work once and then cause a very strange bug when trying to call the from function internally again. Because of these reasons, the following properties have had their names changed:

QueryBuilder

from -> tableName

Column

nullable -> isNullable
unique -> isUnique
unsigned -> isUnsigned

The following functions have had their signatures updated:

BaseGrammar

compileFrom -> compileTableName( required QueryBuilder query, required any tableName )

For the majority of users, this will not take any updates to their code to work with qb 10. For users who have created a custom grammar, column type, or a custom QueryBuilder class, you will need to make sure you code uses the updated property names and functions.

v9.0.0

Dropped support for Adobe ColdFusion 2016

Adobe has ended support for ACF 2016, and so must we.

SchemaBuilder's `uuid` split into and

CFML's uuid does not match other languages; it's one character shorter. Because of this, the value from createUUID() cannot be used in some database column types like SQL Server's uniqueidentifier. This made for some confusion in SchemaBuilder since it wasn't clear if uuid meant CFML's definition or the wider world's definition.

So, the types have been split, following Lucee's pattern, into (matching CFML's ) and (matching Java's UUID or on Lucee).

If you are using uuid with 36 character UUIDs or SQL Server's uniqueidentifier columns, please migrate your uuid calls to guid.

Returning all rows from when maxRows is 0 or lower

Popular grid frameworks like Quasar and Datatables use values of 0 or -1 to return all rows from a query. This is now supported in qb. Previously, it generated an invalid query (SELECT * FROM users LIMIT 0 OFFSET 0).

If this behavior is fine for your application, you don't need to change anything.

This behavior can be customized by providing a callback to the shouldMaxRowsOverrideToAll setting or init argument.

If you need to revert to the previous behavior, provide the following as the shouldMaxRowsOverrideToAll setting:

is now the default

Introduced in , this feature uses separate SQL types for integers and decimals to increase performance in certain database grammars. This feature is now the default, but the previous behavior can be enabled by setting autoDeriveNumericType to false.

This behavior should be an improvement in most every case without any changes needed.

If you need to revert to the previous behavior, provide the following as the autoDeriveNumericType setting:

Note: The option to revert to the old behavior will be removed in the next major version.

is now the default

Introduced in , this feature only returns a SQL type of CF_SQL_TIMESTAMP if the param is a date object, not just a string that looks like a date. This helps avoid situations where some strings were incorrectly interpreted as dates. For many, the migration path is straightforward — calls to are already date objects as well as any function that operates on a date. If you need to parse a string as a date, the built-in function can accomplish that.

If you are relying on qb treating any strings as dates you will need to parse them as actual date objects first. (You can do so using functions like .

If you need to revert to the previous behavior, provide the following as the strictDateDetection setting:

Note: The option to revert to the old behavior may be removed in the next major version.

v8.0.0

Where clauses with an OR combinator are now automatically wrapped inside callbacks

This isn't a breaking change that will affect most people. In fact, it will most likely improve your code.

Previously, when using the control flow function, you were fully responsible for the wrapping of your where statements. For example, the following query:

Would generate the following SQL:

The problem with this statement is that the OR can short circuit the active check.

The fix is to wrap the LIKE statements in parenthesis. This is done in qb using a function callback to where.

When using the when control flow function, it was easy to miss this. This is because you are already in a closure - it looks the same as when using where to group the clauses.

In qb 8.0.0, when will automatically group added where clauses when needed. That means our original example now produces the SQL we probably expected.

Grouping is not needed if there is no OR combinator. In these cases no grouping is added.

If you had already wrapped your expression in a group inside the when callback, nothing changes. Your code works as before. The OR combinator check only works on the top most level of added where clauses.

Additionally, if you do not add any where clauses inside a when callback, nothing changes from qb 7.

The breaking change part is if you were relying on these statements residing at the same level without grouping. In those cases, you may pass the withoutScoping flag to the when callback.

v7.0.0

Lucee 4.5 and Adobe ColdFusion 11 EOL

Support for Lucee 4.5 and Adobe ColdFusion 11 has been dropped. If you need support for these engines, please remain on an earlier version of qb.

MSSQLGrammar renamed to SqlServerGrammar

MSSQLGrammar was visually too close to MySQLGrammar and was hard to differentiate quickly. SqlServerGrammar is much more unique and easily identifiable. Additionally, more people that use this library refer to their database engine as "SQL Server" than "MSSQL".

To migrate, replace any instances of MSSQLGrammar with SqlServerGrammar. Make sure to also append the @qb namespace, if needed,

Variadic Parameters Support Removed

Variadic parameter support was the ability to pass any number of arguments to certain methods like select.

This code came with a slight performance cost and readability cost. That, combined with the fact that the above syntax is very close to an array, we are dropping support for variadic parameters. To migrate, wrap instances of variadic parameters in an array:

defaultGrammar updated to be the full WireBox mapping

In previous versions, the value passed to defaultGrammar was used to look up a mapping in the @qb namespace. This made it difficult to add or use grammars that weren't part of qb. (You could get around this be registering your custom grammar in the @qb namespace, but doing so seemed strange.)

To migrate this code, change your defaultGrammar to be the full WireBox mapping in your moduleSettings:

value method argument order changed

A defaultValue parameter and optional exception throwing was added to value. This pushed the options struct to the end of the method. If you are using positional parameters with value, you will need to update your method calls to either use named parameters or the new positions.

Some methods renamed `callback` to `query`

All methods that could conceivably take a subquery as well as a value now accept a closure or another builder instance to use as a subquery. This led to changing the callback argument to query in the following cases:

whereSub
whereInSub
whereExists

If you are using named parameters with any of the above methods you will need to migrate your method calls.

v5.0.0

Version v5.0.0 brings support for SchemaBuilder inside qb. To avoid naming confusion, Builder was renamed to QueryBuilder. Any references in your code to Builder@qb need to be updated to QueryBuilder@qb.

// manually
var schema = new qb.models.schema.SchemaBuilder(
    grammar = new qb.models.grammars.MySQLGrammar(),
    defaultOptions = { datasource: "my_datasource" }
    defaultSchema = ""
);

// WireBox
var schema = wirebox.getInstance( "SchemaBuilder@qb" );

schema.create( "users", function( table ) {
    table.increments( "id" );
    table.string( "email" );
    table.string( "password" );
    table.timestamp( "created_date" ).nullable();
    table.timestamp( "modified_date" ).nullable();
} );

CREATE TABLE `users` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `created_date` TIMESTAMP,
    `modified_date` TIMESTAMP,
    CONSTRAINT `pk_users_id` PRIMARY KEY (`id`)
)

QueryBuilder

query.from( "users" )
    .tap( function( q ) {
        writeOutput( q.toSQL() & "<br>" );
    } )
    .where( "active", "=", 1 )
    .tap( function( q ) {
        writeOutput( q.toSQL() & "<br>" );
    } );

[
  {
    "sql": "SELECT * FROM `users` WHERE `active` = ?",
    "bindings": [ { "value": 1, "sqltype": "bit" } ],
    "options": { "datasource": "main" },
    "returnObject": "array",
    "pretend": false,
    "result": {},
    "executionTime": 21
  }
]

orWhereExists

QueryBuilder

// qb
query.with( "UserCTE", function ( q ) {
        q
            .select( [ "fName as firstName", "lName as lastName" ] )
            .from( "users" )
            .where( "disabled", 0 );
    } )
    .from( "UserCTE" )
    .get();

QueryBuilder

// qb
var cte = query
    .select( [ "fName as firstName", "lName as lastName" ] )
    .from( "users" )
    .where( "disabled", 0 );

query.with( "UserCTE", cte )
    .from( "UserCTE" )
    .get();

QueryBuilder

query.with( "UserCTE", function ( q ) {
        q.select( [ "id", "fName as firstName", "lName as lastName" ] )
            .from( "users" )
            .where( "disabled", 0 );
    } )
    .with( "BlogCTE", function ( q ) {
        q.from( "blogs" )
            .where( "disabled", 0 );
    } )
    .from( "BlogCTE as b" )
    .join( "UserCTE as u", "b.Creator", "u.id" )
    .get();

MySQL

WITH `UserCTE` AS (
    SELECT
        `id`,
        `fName` as `firstName`,
        `lName` as `lastName`
    FROM `users`
    WHERE `disabled` = 0
),
`BlogCTE` AS (
    SELECT *
    FROM `blogs`
    WHERE `disabled` = 0
)
SELECT *
FROM `BlogCTE` AS `b`
INNER JOIN `UserCTE` AS `u`
ON `b`.`Creator` = `u`.`id`

QueryBuilder

query
.withRecursive( "Hierarchy", function ( q ) {
    q.select( [ "Id", "ParentId", "Name", q.raw( "0 AS [Generation]" ) ] )
        .from( "Sample" )
        .whereNull( "ParentId" )
        // use recursion to join the child rows to their parents
        .unionAll( function ( q ) {
            q.select( [
                    "child.Id",
                    "child.ParentId",
                    "child.Name",
                    q.raw( "[parent].[Generation] + 1" )
                ] )
                .from( "Sample as child" )
                .join( "Hierarchy as parent", "child.ParentId", "parent.Id" );
        } );
    }, [ "Id", "ParentId", "Name", "Generation" ] )
    .from( "Hierarchy" )
    .get();

SqlServer

WITH [Hierarchy] ([Id], [ParentId], [Name], [Generation]) AS (
    SELECT
        [Id],
        [ParentId],
        [Name],
        0 AS [Generation]
    FROM [Sample]
    WHERE [ParentId] IS NULL
    UNION ALL
    SELECT
        [child].[Id],
        [child].[ParentId],
        [child].[Name],

[parent].[Generation] + 1
    FROM [Sample] AS [child]
    INNER JOIN [Hierarchy] AS [parent]
        ON [child].[ParentId] = [parent].[Id]
) SELECT * FROM [Hierarchy]

// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );   
    } );

qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( function( q2 ) {
            q2.where( "username", "LIKE", q & "%" )
                .orWhere( "email", "LIKE", q & "%" );
        } );
    } );

// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when(
        condition = len( url.q ),
        onTrue = function( q ) {
            q.where( "username", "LIKE", q & "%" )
                .orWhere( "email", "LIKE", q & "%" );   
        },
        withoutScoping = true
    );

QueryBuilder

query.subSelect( "last_login_date", function( q ) {
    q.selectRaw( "MAX(created_date)" )
        .from( "logins" )
        .whereColumn( "users.id", "logins.user_id" );
} ) ).from( "users" );

What's New?

13.0.12

Fixed another instance of updating the interceptorService to correctly use announce or processState.

13.0.11

Fix for mechanism used to determine if the interceptor service should use announce or processState.

13.0.10

Use announce as the default method for announcing interception points. (Falls back to processState on ColdBox versions before 6.0.0.)

13.0.9

Process subqueries when a closure or Builder instance is passed as the second column to .

13.0.8

Fix retrieving count for pagination when the query has both a GROUP BY and an ORDER BY clause.

13.0.7

BoxLang: Minor fix for isBuilder checks on BoxLang

13.0.6

Remove duplicate calls for performance improvements.

13.0.5

SqlServerGrammar: Move clause to last position

13.0.4

Fix for using operators in

13.0.3

Reset tableName in aggregate queries

IMPORTANT:

Persist constructor argument in QueryUtils.

This was added in 12.0.0, but because it was not persisted until now, this may appear as a breaking change in your application. A reminder that if you rely on empty strings being inserted into your application as empty strings to turn this setting off.

13.0.2

Apply a .limit( 1 ) to the aggregate.

13.0.1

Allow null values in the update clause of queries.

13.0.0

Aliases in subselects are now renamed correctly when using .
TestBox Helpers:
- expectToHaveCount
- expectNotToHaveCount

Breaking Changes

Query Param structs are now validated when adding to a query

This change may cause some of your existing queries to begin throwing QBInvalidQueryParam exceptions. Remove the non-standard keys to fix the error.

12.1.1

QueryUtils: Add name as a valid query param key.

12.1.0

qb now checks the shape of query param structs passed as bindings and will throw a QBInvalidQueryParam if there are any invalid keys.

This is to help developers who may have passed a struct as a param that they meant to first serialize to JSON.

12.0.0

Breaking Changes

Add new setting and default to true.

qb now automatically converts an empty string value to null when inserting into a query. If your application relies on inserting or updating values to an empty string, set this setting to false.

Remove `autoAddScale` setting.

qb now always automatically adds a scale to decimal and float query params. This has been the default since . This can still be overridden by providing a full struct query param when adding bindings.

Remove `strictDateDetection` setting

qb will now only use type introspection over the isDate function for all date detection. This has been the default since .

Remove `autoDeriveNumericType` setting

qb will only use INTEGER or DECIMLAL sql types instead of the more ambiguous NUMERIC. This has been the default since .

Allow for default values for , , , and functions

The argument order change to account for the new defaultValue argument. If you are using positional parameters with these functions, please migrate to the new function signatures.

Removal of `CF_SQL` prefix

The CF_SQL prefix for cfsqltype has been optional since , so while this change should not impact any running application, we are labelling it as a breaking change out of an abundance of caution.

Initializer Argument change for `QueryUtils`

To support removal of the settings above and to add a new setting to convert empty strings to null, the QueryUtils class' initializer has been modified. If you are creating this class manually, please check the API docs and upgrade to the new initializer arguments.

New Features

New DerbyGrammar support.
Allow queries .
Add a new shortcut method for returning( "*" ).

Bug Fixes

Compatibility fixes for BoxLang and Adobe ColdFusion.
Compatibility for pure BoxLang (without the bx-compat-cfml module).
Fix the count method for pagination when being used with a query with DISTINCT turned on.

11.1.0

QueryBuilder: Support JOINS in DELETE statements for supported grammars, like MySQL and SQL Server.

11.0.3

QueryBuilder: Don't overly specify that grammars must extend BaseGrammar. It's just an implicit interface, after all.

11.0.2

QueryBuilder: Have aliases work with full server qualifications, like ServerName.schemaName.tableName.

11.0.1

Allow for disabling of wrapping values

Either a Grammar setting (setShouldWrapValues( true|false )) or for a one-off Query Builder ( / ) can control whether identifiers like table names, columns, etc. are wrapped.

BoxLang Compatibility

This release includes updates to be compatible with the latest releases of BoxLang.

11.0.0

Auto Boolean Casting

Grammars will be able to influence the cfsqltype and value when passing in a literal boolean value as a binding. Postgres and SQLite have boolean support, so they will keep the literal boolean value and use a cfsqltype of CF_SQL_OTHER. SQL Server uses CF_SQL_BIT, Oracle users CF_SQL_NUMERIC, and MySQL uses CF_SQL_TINYINT — all of these will convert literal boolean values to either 1 or 0. This behavior is skipped when providing a custom cfsqltype.

Custom grammars can implement the getBooleanSqlType and convertBooleanValue methods to customize this behavior.

Additionally, attempting to change the grammar with any bindings currently configured will throw an exception. This is because the bindings are converted via the grammar when added to the builder and cannot be changed retroactively when setting a new grammar. Set the grammar first before configuring the query to avoid this exception.

10.0.2

QueryUtils: Fix timestamp formatting losing timezone information

10.0.1

QueryUtils: Manually construct ISO 8601 timestamps due to lack of Adobe support

10.0.0

Full compatibility for running on with the module.
Internal property name changes for BoxLang compatibility as well as cleaner code. (This may cause breaking changes, in rare cases. See the for more details.)

9.8.1

Fix missing parseNumber function for ACF
Add alias to clone()

9.8.0

Support alias renaming using

9.7.1

Add in missing join compilations.

9.7.0

Implement and for supported Grammars

9.6.1

Expand type annotation for from. This can be a string or an Expression.

9.6.0

Make and publicly accessible.

9.5.1

Add MariaDB support to AutoDiscover@qb grammar. (It will choose the MySQLGrammar@qb.)

9.5.0

Add and methods, inspired by .

9.4.1

Better Support for OracleGrammar in SchemaBuilder

Fix trigger creation by escaping colons (:).
Try to drop associated sequences and triggers when dropping a table.
Better support for creating a table in a different schema by only checking for the last identifier as the table name in and .

9.4.0

Allow for setting a property on a SchemaBuilder instance.

The defaultSchema will be used for methods like and . A passed in schema will still take precedence.

9.3.1

Use CHAR for GUID and UUID types in MySQL.
Don't call getUtils from inside QueryUtils.

9.3.0

Make publicly available in QueryUtils.

This is used by qb to inline query bindings in toSQL or dump calls and can be used to inline the bindings in other tools like.

9.2.5

Use named parameters when passing to BaseGrammar. This avoids problems where custom Grammars have extra arguments and we add arguments to the official grammar.

9.2.4

We apologize for the new features in a patch release.

New Features

Add the ability to pretend to run queries, both in and .
Add query logging to and instances.

Bug Fixes

Use varchar for clob when converting to a CFML query. This is used when removing a column like in Oracle pagination.

9.2.3

Handle more numeric SQL types like AtomicInteger and Long.

9.2.2

Add millisecond accuracy to inline bindings.

9.2.1

Separate having bindings from where bindings.

9.2.0

New Features

We now support the returning function inside update and delete statements for supported Grammars. Supported grammars are SQL Server, Postgres, and SQLite.

Bug Fixes

Fix raw table name parsing in update queries for SqlServerGrammar.
Fix truncating text in nested wheres inside joins.
Fix out of order bindings in joinSub

9.1.5

Switch from table_catalog to table_schema when referencing schema for PostgresGrammar.

9.1.4

CommandBox-friendly injections for SQL Commenter.

9.1.3

Add support for from bindings, used especially in fromSub queries.

9.1.2

This release reverts the use of native returntypes. There are too many bugs between engine implementations to make it viable. No end-user changes should be visible.

9.1.1

Make withReturnFormat a public method.

9.1.0

New Features

Add ability to inline bindings when calling toSQL and dump. These strings can be executed in a DBMS application.

Bug Fixes

Move coldbox namespace injection to the function body so CommandBox doesn't blow up.
Correctly apply native returntypes after newQuery and withReturnFormat.

9.0.2

Fix losing defaultOptions when calling newQuery.
Shortcut for no return format using none.
Allow for native struct returntypes. Requires a return format of none.

9.0.1

Fix RouteInfoCommenter file name.

9.0.0

Breaking Changes

Dropped support for Adobe ColdFusion 2016

Adobe has ended support for ACF 2016, and so must we.

SchemaBuilder's `uuid` split into and

So, the types have been split, following Lucee's pattern, into (matching CFML's ) and (matching Java's UUID or on Lucee).

Returning all rows from when maxRows is 0 or lower

This behavior can be customized by providing a callback to the shouldMaxRowsOverrideToAll setting or init argument. For instance, to revert to the previous behavior you would set the function as follows:

is now the default

Note: the option to revert to the old behavior will be removed in the next major version.

is now the default

Note: the option to revert to the old behavior may be removed in the next major version.

New Features and Improvements

SQLite Grammar Support

Thanks to , qb now supports SQLite for both QueryBuilder and SchemaBuilder. You can use it in your apps by specifying SQLiteGrammar@qb as the default grammar.

sqlCommenter is a for adding contextual information as a comment at the end of a SQL statement. This can give insights into your application, especially when diagnosing slow queries. Examples of the information you can append to your queries are route, handler, action, version, and others, as well as the ability to add your own, such as loggedInUser and more.

helper function

There's a new shortcut method to return qb.sum( qb.raw( expression ) ). You're welcome. 😉

Dedicated method

Some grammars, like SQL Server, do not treat simple indexes as constraints. For this reason, we've added a method alongside the existing .

helper method

will return either an array of column names for the configured table or the query that is generated by cfdbinfo for the configured table. Especially useful when working with dynamically generated grids.

Bug Fixes

Correctly compile insertUsing statements that use Common Table Expressions (CTEs).
Update announceInterception calls for ColdBox 7. (Thank you, Michael Born.)
Fixed insertUsing not placing Common Table Expressions (CTEs) in the correct order.

8.10.0

Add a fetch method inspired by .
There are now used during the inferSQLType check in QueryUtils. This is an opt-in feature, enabled by setting the autoDeriveNumericType setting. The previous approach was to use CF_SQL_NUMERIC for all numeric types which could cause performance issues in some grammars as they interpreted all CF_SQL_NUMERIC as floating point numbers.

8.9.1

HOLDLOCK and READPAST are mutually exclusive table locks in SQL Server but were mistakenly being applied together.

8.9.0

Specify defaultOptions

8.8.1

Better parsing of raw statements when deriving columns.

8.8.0

New Features and Improvements

Insert data based off of a callback or builder using .
Insert data ignoring duplicate key errors using .
Use a callback or builder as the source for an statement.

Bug Fixes

Don't uppercase quoted aliases in Oracle.
Fix for aliases in update statements.
Don't sort columns for insertUsing.

8.7.8

Fix for Oracle returning custom column types when renaming a column.

8.7.7

Explicit arguments scoping.

8.7.6

arrayEach is slow compared to merging arrays.

8.7.5

Fix wheres with joins in update statements.

8.7.2

Add better null handling to inferSqlType.

8.7.1

Correctly format columns being updated.

8.7.0

New Features and Improvements

Add an method. upsert can update or insert multiple records at once depending on if a column is matched.
Allow expressions in and . Also add a and helper method to make that pattern more ergonomic.
Allow . (This is not supported on Oracle.)

Bug Fixes

Better handling of and clauses in queries.
Allow any value to be returned from including strings, numbers, and dates.
Provide default values for and if no records are returned.

8.6.1

Correctly wrap CTE expressions with parenthesis when required in certain grammars.

8.6.0

SchemaBuilder can now be configured with . (Default options will still be overridden by options passed to each SchemaBuilder method.)

8.5.0

QueryBuilder

Add a method to QueryBuilder.
Add helpers such as , , , and .
Correct return aggregate values for date values from max and min executors.

SchemaBuilder

Add support for and .

8.4.9

Swap master branch to main branch.

8.4.8

Remove unnecessary injection for QueryUtils.

8.4.7

Account for raw expressions when generating mementos for comparison

8.4.6

Add support for & types for MySQLGrammar.

8.4.5

Fix limit on .

8.4.1 - 8.4.4

Migrate release process to GitHub Actions.

8.4.0

Add a simplePaginate pagination method for quicker performance when total records or total pages are not needed or too slow.

8.3.0

Introduce a setting to specify the default numeric SQL type.

8.2.2

Default to html for the dump format argument to writeDump.

8.2.1

Correctly use the passed in strictDateDetection to the QueryUtils.cfc.

8.2.0

📹

Added a command to aid in debugging a query while chaining.

8.1.0

📹

now can accept bindings.
A new, optional setting is available to check the underlying Java class of a date object instead of using isDate.

8.0.3

Ignore select bindings for aggregate queries.
Allow spaces in table aliases.
Split FLOAT and DECIMAL column types in SQL Server.

8.0.2

Clear orderBy bindings when calling clearOrders.

8.0.1

Trim table definitions before searching for aliases. Makes qb more lenient with extra whitespace.

8.0.0

📹

BREAKING CHANGES

`` callbacks now automatically scope and group where clauses when an OR combinator is used.

Other Changes

Combine and orderBy with a new method.
Clear current selected columns with .
Combine and either or with and respectively.

7.10.0

Expose nested where functions to enable advanced query manipulation in downstream libraries like Quick.

7.9.9

Fixes for OracleGrammar including table aliases and wrapped subqueries.

7.9.8

Allow nullable in MySQL.

7.9.7

Return 0 on null .

7.9.6

Match type hints to documentation for functions

7.9.5

Handle enhanced numeric checks with Secure Profile enabled.

7.9.4

Allow raw statements in basic where clauses.

7.9.3

Passed along the options struct to the method when calling .

7.9.2

Allow for space-delimited directions like column DESC.
Add helpful message when trying to use a closure with instead of .
`` and now work with

7.9.1

Handle multi-word columns in queryRemoveColumns.

7.9.0

Remove elvis operator due to ACF compatibility issues

7.8.0

Add support for and data types to .

7.7.3

Fix wrapping of types for Postgres.

7.7.2

Compatibility fix for ACF 2018 and listLast parsing.
Include current_timestamp default for columns in SchemaBuilder.
Ignore table qualifiers for insert and update.

7.7.1

Fix a bug with preventDuplicateJoins when using the closure syntax with a join.

7.7.0

Add executionTime to the data output from BaseGrammar, including being available in interceptors.

7.6.2

Fix a case where a column was not wrapped correctly when a where used a subquery for the value.

7.6.1

Avoid duplicate function due to cbORM / Hibernate bugs when used in the same application.

7.6.0

Split off a private whereBasic method. This is used in Quick to provide extra sql type features.
Add a method. Any already configured orders are cleared. Any orders added after this call will be added as normal.
now can take an array of expressions.

7.5.1

Fixed an issue using column formatters with update and insert.

7.5.0

Using a new preventDuplicateJoins setting in the module settings, qb can detect duplicate joins and ignore them. This is especially useful in a heavily filtered and dynamic query where you may or may not need the join at all or more than one column may need the same join. preventDuplicateJoins defaults to false, so it is opt-in. It may be turned on by default in a future breaking release of qb.

7.4.0

Enhance order by's with more direction options ()

You can now use two shortcut methods: orderByAsc and orderByDesc. Additionally, orderBySub or using orderBy with a closure or builder instance will respect the direction argument.

7.3.15

Fix using whereBetween with query param structs ()

7.3.14

Ignore orders in aggregate queries ()

7.3.13

Format with cfformat ()

7.3.12

Improve column wrapping with trimming ()
Prefer the parent query over magic methods when the parent query has the exact method. ()

7.3.9, 7.3.10, 7.3.11

Switch to using .

7.3.8

Allow passing query options in to paginate ()

7.3.7

Fix for inserting null values directly ()

7.3.5, 7.3.6

Use cfformat for automatic formatting ()
Add a type to the onMissingMethod exception ()

7.3.4

Correctly wrap in MySQLGrammar.

7.3.2, 7.3.3

Publish qb apidocs to .

7.3.1

Fix for null values breaking the new checkIsActuallyNumeric method in QueryUtils.

7.3.0

Add a parameterLimit public property to SqlServerGrammar. This property is used in Quick to split up eager loading to work around the 2100 param limit of SQL Server.

7.2.0

Allow a to be set. A parent query will receive any method calls that are not found on the Query Builder instance. This is especially useful for instances like to allow Quick features like scopes to be available inside any closures.

7.1.0

Lambdas (arrow functions) are now allowed wherever closures are allowed.
Add an method.
Allow for fully-qualified column names (table_name.column.name) in the and methods.

7.0.0

BREAKING CHANGES

Please see the for more information on these changes.

Drop support for Lucee 4.5 and Adobe ColdFusion 11.
MSSQLGrammar renamed to SqlServerGrammar
Remove variadic parameters support in builder functions like select.

Other Changes

Completely revamped documentation! (You're looking at it right now.)
Add new flag to to replace question marks (?) with cfqueryparam-compatible structs for debugging.
Preserve column case and order when converting a query to an array using the default "array" return format.

****

6.4.0

schema.create( "users", function( table ) {
    table.boolean( "is_active" ).default( 1 );
    table.timestamp( "created_date" ).default( "NOW()" );
    tablVIRTUAL NOT NULLe.string( "country" ).default( "'USA'" );
} );

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE
)

QueryBuilder

query.from( "users" )
    .orderBy( function( q ) {
        q.selectRaw( "MAX(created_date)" )
            .from( "logins" )
            .whereColumn( "users.id", "logins.user_id" );
    } );

Altering Tables and Views

alter

The alter method loads up an existing table in order to make modifications. These modifications may include adding, renaming, or dropping columns and constraints.

To begin altering an existing table, call the alter method off of the SchemaBuilder. This method takes a callback as the second parameter that is passed a Blueprint object, much like the create method.

Calling multiple methods inside a single alter callback creates multiple SQL statements to be executed. qb takes care of this execution for you by default.

The following methods off of Blueprint let you modify the table inside the callback:

addColumn

Add a new column to an existing table. Takes a Column instance as the only argument.

renameConstraint

Rename an existing table constraint.

Example:

SchemaBuilder

SQL (MySQL)

renameTable

Rename an existing table.

Example:

SchemaBuilder

SQL (MySQL)

rename

An alias for renameTable.

Example:

SchemaBuilder

SQL (MySQL)

alterView

Shortcut method frop dropView and createView together.

Argument

Type

Required

Default

Description

Retrieving Results

get

Name

Type

Required

Default

Description

columns

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.

first

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.

firstOrFail

Name

Type

Required

Default

Description

throws: RecordNotFound

Returns the first matching row for the configured query, just like . If no records are found, it throws an RecordNotFound exception.

find

Name

Type

Required

Default

Description

Adds an id constraint to the query and returns the first record from the query.

findOrFail

Name

Type

Required

Default

Description

Throws: RecordNotFound

Adds an id constraint to the query and returns the first record from the query. If no record is found, it throws an RecordNotFound exception.

values

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 function can make this pattern more ergonomic.

valuesRaw

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

value

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 function can make this pattern more ergonomic.

valueRaw

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

chunk

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.

paginate

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

The behavior when a maxRows of 0 or lower is passed is determined by the shouldMaxRowsOverrideToAll callback function. The default callback returns all rows for values <= 0. You can customize this behavior by passing a new callback to the shouldMaxRowsOverrideToAll setting or init argument.

simplePaginate

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

Custom Pagination Collectors

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.

generateWithResults

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.

generateSimpleWithResults

If you use a custom paginationCollector, ensure it has been updated with this new generateSimpleWithResults method before calling simplePaginate.

Wheres

Table of Contents

Where Methods

where

Adds a where clause to a query.

Using the where method will parameterize the value passed. If you want to constrain a column to another column, use the method.

You can also pass an as the value.

Any of the following operators can be used in a where clause.

When using the "=" constraint, you can use a shortcut and define the value as the second argument.

You may also use statements to simplify this further.

To group where statements together, pass a function to the where clause as the only parameter.

This grouping can be nested as many levels as you require.

A Function or QueryBuilder can be used as a subselect expression when passed to value.

andWhere

This method is simply an alias for with the combinator set to "and".

orWhere

This method is simply an alias for with the combinator set to "or".

whereBetween

Adds a where between clause to the query.

If a function or QueryBuilder is passed it is used as a subselect expression.

whereNotBetween

Adds a where not in clause to the query. This behaves identically to the method with the negateflag set to true. See the documentation for for usage and examples.

whereColumn

Adds a where clause to a query that compares two columns.

Just as with where, when using "=" as the operator you can use a shorthand passing the second column in as the operator and leaving the second column null.

Expressions can be passed in place of either column.

whereExists

Adds a where exists clause to the query.

It can be configured with a function.

It can also be configured with a QueryBuilder instance.

whereNotExists

Adds a where not in clause to the query. This behaves identically to the method with the negateflag set to true. See the documentation for for usage and examples.

whereLike

A shortcut for calling with "like" set as the operator.

whereNotLike

A shortcut for calling with "not like" set as the operator.

whereIn

Adds a where in clause to the query.

The values passed to whereIn can be a single value, a list of values, or an array of values.

Some database grammars have a hard limit on the number of parameters passed to a SQL statement. Keep this in mind while writing your queries.

If a list of values is passed in, it is converted to an array of values using a single comma (",") delimiter.

Any value in the list or array can also be passed using a to have more control over the parameter settings.

Expressions can be freely mixed in with other values.

A function or QueryBuilder instance can be passed to be used as a subquery expression instead of a list of values.

You may find a whereExists method performs better for you than a whereIn with a subquery.

whereNotIn

Adds a where not in clause to the query. This behaves identically to the whereIn method with the negateflag set to true. See the documentation for for usage and examples.

whereRaw

Shorthand to add a raw SQL statement to the where clauses.

whereNull

Adds a where null clause to the query.

whereNotNull

Adds a where not in clause to the query. This behaves identically to the method with the negateflag set to true. See the documentation for for usage and examples.

Dynamic Where Methods

qb uses onMissingMethod to provide a few different helpers when working with where... methods.

andWhere... and orWhere...

Every where... method in qb can be called prefixed with either and or or. Doing so will call the original method using the corresponding combinator.

where{Column}

If you call a method starting with where that does not match an existing qb method, qb will instead call the where method using the rest of the method name as the first column name. (The rest of the arguments will be shifted to account for this.) This also applies to andWhere{Column} and orWhere{Column} method signatures.

QueryBuilder

query.from( "users" )
    .where( "email", "foo" )
    .orWhere( "id", "=", function( q ) {
        q.select( q.raw( "MAX(id)" ) )
            .from( "users" )
            .where( "email", "bar" );
    } );

QueryBuilder

query.from( "users" )
    .whereBetween(
        "id",
        function( q ) {
            q.select( q.raw( "MIN(id)" ) )
                .from( "users" )
                .where( "email", "bar" );
        },
        builder.newQuery()
            .select( builder.raw( "MAX(id)" ) )
            .from( "users" )
            .where( "email", "bar" )
    );

QueryBuilder

query.from( "orders" )
    .whereExists( function( q ) {
        q.select( q.raw( 1 ) )
            .from( "products" )
            .whereColumn( "products.id", "orders.id" );
    } );

QueryBuilder

var existsQuery = query.newQuery()
    .select( q.raw( 1 ) )
    .from( "products" )
    .whereColumn( "products.id", "orders.id" );

query.from( "orders" )
    .whereExists( existsQuery );

MERGE [users] AS [qb_target]
USING (VALUES (?, ?), (?, ?)) AS [qb_src] ([email], [name])
ON [qb_target].[email] = [qb_src].[email]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([email], [name]) VALUES ([email], [name]);

MERGE INTO "USERS" "QB_TARGET"
USING (SELECT ?, ? FROM dual UNION ALL SELECT ?, ? FROM dual) "QB_SRC"
ON "QB_TARGET"."EMAIL" = "QB_SRC"."EMAIL"
WHEN NOT MATCHED THEN
INSERT ("EMAIL", "NAME")
VALUES ("QB_SRC"."EMAIL", "QB_SRC"."NAME")

INSERT INTO `users`
    (`active`, `createdDate`, `modifiedDate`, `username`)
VALUES
    (?, ?, ?, ?),
    (?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
    `active` = VALUES(`active`),
    `modifiedDate` = VALUES(`modifiedDate`)

MERGE [users] AS [qb_target]
USING (VALUES (?, ?, ?, ?), (?, ?, ?, ?)) AS [qb_src]
    ([active], [createdDate], [modifiedDate], [username])
ON [qb_target].[username] = [qb_src].[username]
WHEN MATCHED THEN UPDATE
    SET [active] = [qb_src].[active],
        [modifiedDate] = [qb_src].[modifiedDate]
WHEN NOT MATCHED BY TARGET THEN INSERT
    ([active], [createdDate], [modifiedDate], [username])
    VALUES
    ([active], [createdDate], [modifiedDate], [username])

INSERT INTO "users"
    ("active", "createdDate", "modifiedDate", "username")
VALUES
    (?, ?, ?, ?),
    (? ,? ,? ,?)
ON CONFLICT ("username") DO UPDATE
    "active" = EXCLUDED."active",
    "modifiedDate" = EXCLUDED."modifiedDate"

MERGE INTO "USERS" "QB_TARGET"
USING (
    SELECT ?, ?, ?, ? FROM dual
    UNION ALL
    SELECT ?, ?, ?, ? FROM dual
) "QB_SRC"
ON "QB_TARGET"."USERNAME" = "QB_SRC"."USERNAME"
WHEN MATCHED THEN UPDATE
    SET "ACTIVE" = "QB_SRC"."ACTIVE",
        "MODIFIEDDATE" = "QB_SRC"."MODIFIEDDATE"
WHEN NOT MATCHED THEN INSERT
    ("ACTIVE", "CREATEDDATE", "MODIFIEDDATE", "USERNAME")
    VALUES
    ("QB_SRC"."ACTIVE", "QB_SRC"."CREATEDDATE", "QB_SRC"."MODIFIEDDATE", "QB_SRC"."USERNAME")

MERGE [stats] AS [qb_target]
USING (VALUES (?, ?, ?), (?, ?, ?)) AS [qb_src]
    ([postId], [viewedDate], [views])
ON [qb_target].[postId] = [qb_src].[postId]
    AND [qb_target].[viewedDate] = [qb_src].[viewedDate]
WHEN MATCHED THEN UPDATE
    SET [views] = stats.views + 1
WHEN NOT MATCHED BY TARGET THEN INSERT
    ([postId], [viewedDate], [views])
    VALUES
    ([postId], [viewedDate], [views])

MERGE INTO "STATS" "QB_TARGET"
USING (
    SELECT ?, ?, ? FROM dual
    UNION ALL
    SELECT ?, ?, ? FROM dual
) "QB_SRC"
ON "QB_TARGET"."POSTID" = "QB_SRC"."POSTID"
    AND "QB_TARGET"."VIEWEDDATE" = "QB_SRC"."VIEWEDDATE"
WHEN MATCHED THEN UPDATE
    SET "VIEWS" = stats.views + 1
WHEN NOT MATCHED THEN INSERT
    ("POSTID", "VIEWEDDATE", "VIEWS")
    VALUES
    ("QB_SRC"."POSTID", "QB_SRC"."VIEWEDDATE", "QB_SRC"."VIEWS")

INSERT INTO `users`
    (`username`, `active`, `createdDate`, `modifiedDate`)
SELECT `username`, `active`, `createdDate`, `modifiedDate`
FROM `activeDirectoryUsers`
ON DUPLICATE KEY UPDATE
    `active` = VALUES(`active`),
    `modifiedDate` = VALUES(`modifiedDate`)

MERGE [users] AS [qb_target]
USING (
    SELECT [username], [active], [createdDate], [modifiedDate]
    FROM [activeDirectoryUsers]
) AS [qb_src]
ON [qb_target].[username] = [qb_src].[username]
WHEN MATCHED THEN UPDATE
    SET [active] = [qb_src].[active],
        [modifiedDate] = [qb_src].[modifiedDate]
WHEN NOT MATCHED BY TARGET THEN INSERT
    ([username], [active], [createdDate], [modifiedDate])
VALUES ([username], [active], [createdDate], [modifiedDate]);

INSERT INTO "users"
("username", "active", "createdDate", "modifiedDate")
SELECT "username", "active", "createdDate", "modifiedDate"
FROM "activeDirectoryUsers"
ON CONFLICT ("username") DO UPDATE
    "active" = EXCLUDED."active",
    "modifiedDate" = EXCLUDED."modifiedDate"

MERGE INTO "USERS" "QB_TARGET"
USING (
    SELECT "USERNAME", "ACTIVE", "CREATEDADATE", "MODIFIEDDATE"
    FROM "ACTIVEDIRECTORYUSERS"
) "QB_SRC"
ON "QB_TARGET"."USERNAME" = "QB_SRC"."USERNAME"
WHEN MATCHED THEN UPDATE
    SET "ACTIVE" = "QB_SRC"."ACTIVE",
        "MODIFIEDDATE" = "QB_SRC"."MODIFIEDDATE"
WHEN NOT MATCHED THEN INSERT
    ("USERNAME", "ACTIVE", "CREATEDDATE", "MODIFIEDDATE")
    VALUES
    (
        "QB_SRC"."USERNAME",
        "QB_SRC"."ACTIVE",
        "QB_SRC"."CREATEDDATE",
        "QB_SRC"."MODIFIEDDATE"
    )

QueryBuilder

query.from( "users" )
    .insertIgnore(
        values = [
            { "email" = "foo", "name" = "bar" },
            { "email" = "baz", "name" = "bam" }
        ],
        target = [ "email" ]
    );

qb.from( "users" )
    .insertUsing( function( q ) {
        q.from( "activeDirectoryUsers" )
            .select( [ "email", "modifiedDate AS createdDate" ] )
            .where( "active", 1 );
    } );

qb.from( "users" )
    .insertUsing(
        columns = [ "email", "createdDate" ],
        source = function( q ) {
            q.from( "activeDirectoryUsers" )
                 .select( [ "email", "modifiedDate" ] )
                 .where( "active", 1 );
        }
    );

qb.from( "users" )
    .insertUsing(
        qb.newQuery()
            .from( "activeDirectoryUsers" )
            .select( [ "email", "modifiedDate AS createdDate" ] )
            .where( "active", 1 )
    );

qb.table( "employees" )
    .update( {
		    "departmentName" = function( q ) {
		        q.from( "departments" )
		            .select( "name" )
		            .whereColumn( "employees.departmentId", "departments.id" );
		    } )
		} );

qb.table( "employees" )
    .update( {
		    "departmentName" = qb.newQuery()
		        .from( "departments" )
		        .select( "name" )
		        .whereColumn( "employees.departmentId", "departments.id" )
		    } )
		} );

QueryBuilder

query.from( "users" )
    .whereId( 1 )
    .addUpdate( {
        "email" = "foo",
        "name" = "bar"
    } )
    .when( true, function( q ) {
        q.addUpdate( {
            "foo": "yes"
        } );
    } )
    .when( false, function( q ) {
        q.addUpdate( {
            "bar": "no"
        } );
    } )
    .update();

qb.table( "users" )
    .upsert(
        values = [
            {
                "username": "johndoe",
                "active": 1,
                "createdDate": "2021-09-08 12:00:00",
                "modifiedDate": "2021-09-08 12:00:00"
            },
            {
                "username": "janedoe",
                "active": 1,
                "createdDate": "2021-09-10 10:42:13",
                "modifiedDate": "2021-09-10 10:42:13"
            },
        ],
        target = [ "username" ],
        update = [ "active", "modifiedDate" ],
    );

qb.table( "stats" )
    .upsert(
        values = [
            { "postId": 1, "viewedDate": "2021-09-08", "views": 1 },
            { "postId": 2, "viewedDate": "2021-09-08", "views": 1 }
        ],
        target = [ "postId", "viewedDate" ],
        update = { "views": qb.raw( "stats.views + 1" ) }
    );

qb.table( "stats" )
    .upsert(
        source = function( q ) {
            q.from( "activeDirectoryUsers" )
                .select( [
                    "username",
                    "active",
                    "createdDate",
                    "modifiedDate"
                ] );
        },
        values = [ "username", "active", "createdDate", "modifiedDate" ],
        target = [ "username" ],
        update = [ "active", "modifiedDate" ]
    );

QueryBuilder

qb.from( "users" )
    .where( "id", 1 )
    .returningRaw( [
        "DELETED.modifiedDate AS oldModifiedDate",
        "INSERTED.modifiedDate AS newModifiedDate"
    ] )
    .update( { "email": "[email protected]" } );

QueryBuilder

query.from( "users" )
    .join( "posts", function( j ) {
        j.on( function( j1 ) {
            j1.on( "users.id", "posts.author_id" )
                .orOn( "users.id", "posts.reviewer_id" );
        } );
        j.whereNotNull( "posts.published_date" );
    } );

QueryBuilder

query.from( "users as u" )
    .joinSub( "c", function ( q ) {
        q.select( "id" )
            .from( "contacts" )
            .whereNotIn( "id", [ 1, 2, 3 ] );
    }, "u.id", "=", "c.id" );

QueryBuilder

query.from( "users as u" )
    .joinSub( "c", function ( q ) {
        q.select( "id" )
            .from( "contacts" )
            .whereNotIn( "id", [ 1, 2, 3 ] );
    }, function( j ) {
        j.on( "u.id", "c.id" );
        j.on( "u.type", "c.type" );
    } );

QueryBuilder

var sub = query.newQuery()
    .select( "id" )
    .from( "contacts" )
    .whereNotIn( "id", [ 1, 2, 3 ] );

query.from( "users as u" )
    .leftJoinSub( "c", sub, "u.id", "=", "c.id" );

QueryBuilder

var sub = query.newQuery()
    .select( "id" )
    .from( "contacts" )
    .whereNotIn( "id", [ 1, 2, 3 ] );

query.from( "users as u" )
    .rightJoinSub( "c", sub, "u.id", "=", "c.id" );

QueryBuilder

qb.from( "users as u" )
    .select( [ "u.ID", "childCount.c" ] )
    .crossApply( "childCount", function( qb ) {
        qb.selectRaw( "count(*) c" )
            .from( "children" )
            .whereColumn( "children.parentID", "=", "users.ID" )
            .where( "children.someCol", "=", 0 );
    } )
    .where( "childCount.c", ">", 1 )

SQL Server

SELECT
    [u].[ID],
    [childCount].[c]
FROM [users] AS [u]
CROSS APPLY (
    SELECT count(*) c
    FROM [children]
    WHERE [children].[parentID] = [users].[ID]
    AND [children].[someCol] = ?
) AS [childCount]
WHERE [childCount].[c] > ?

QueryBuilder

qb.from( "users as u" )
    .select( [ "u.ID", "childCount.c" ] )
    .outerApply( "childCount", function( qb ) {
        qb.selectRaw( "count(*) c" )
            .from( "children" )
            .whereColumn( "children.parentID", "=", "users.ID" )
            .where( "children.someCol", "=", 0 );
    } )
    .where( "childCount.c", ">", 1 )

SQL Server

SELECT
    [u].[ID],
    [childCount].[c]
FROM [users] AS [u]
OUTER APPLY (
    SELECT count(*) c
    FROM [children]
    WHERE [children].[parentID] = [users].[ID]
    AND [children].[someCol] = ?
) AS [childCount]
WHERE [childCount].[c] > ?

QueryBuilder

// This is still an inner join because
// the JoinClause is an inner join
var j = query.newJoin( "contacts", "inner" )
    .on( "users.id", "posts.author_id" );

query.from( "users" ).leftJoin( j );

Columns

Example:

SchemaBuilder

SQL (MySQL)

char

Create a column using a CHAR equivalent type for your database.

Example (default length):

SchemaBuilder

SQL (MySQL)

Example (custom length):

SchemaBuilder

SQL (MySQL)

date

MySQL and Others: Create a column using a CHAR equivalent type for your database and a length of 36. Used in conjunction with the Lucee createGUID method or Java's java.util.UUID.randomUUID().

Example:

SchemaBuilder

MySQL (SQL Server)

SQL (MySQL)

increments

Create an auto-incrementing column using an unsigned INTEGER type. This column is also set as the primary key for the table.

Example:

SchemaBuilder

SQL (MySQL)

integer

Create a column using a INTEGER equivalent type for your database.

Example (no precision):

SchemaBuilder

SQL (MySQL)

Example (with precision):

SchemaBuilder

SQL (MySQL)

json

Create a column using a JSON equivalent type for your database.

Example:

SchemaBuilder

SQL (MySQL)

jsonb

Create a column using a JSONB equivalent type for your database.

Currently, only PostgresGrammar makes a distinction between json and jsonb.

Example:

SchemaBuilder

Postgres

lineString

Create a column using a LINESTRING equivalent type for your database.

Example:

SchemaBuilder

SQL (MySQL)

longText

Create a column using a LONGTEXT equivalent type for your database.

Example:

SchemaBuilder

SQL (MySQL)

mediumIncrements

Create an auto-incrementing column using an unsigned MEDIUMINT type. This column is also set as the primary key for the table.

Example:

SchemaBuilder

SQL (MySQL)

mediumInteger

Create a column using a MEDIUMINT equivalent type for your database.

Example (no precision):

SchemaBuilder

SQL (MySQL)

smallMoney

Create a column using a SMALLMONEY equivalent type for your database.

Example:

SchemaBuilder

SQL (MySQL)

SQL (MSSQL)

softDeletes

Creates a nullable deletedDate TIMESTAMP column.

If you want different names for your timestamp column, feel free to call other schema builder methods individually.

Example:

SchemaBuilder

SQL (MySQL)

softDeletesTz

Creates a nullable deletedDate timezone-specific TIMESTAMP column.

If you want different names for your timestamp column, feel free to call other schema builder methods individually.

Example:

SchemaBuilder

SQL (SQL Server)

string

Create a column using a VARCHAR equivalent type for your database. For databases that distinguish between unicode- and non-unicode string data types, this function will create a non-unicode string.

Example (with defaults):

SchemaBuilder

timestampTz

Create a column using a timezone-specific TIMESTAMP equivalent type for your database.

Example:

SchemaBuilder

SQL (Postgres)

timestampsTz

Creates the createdDate and modifiedDate timezone-specific TIMESTAMP columns.

If you want different names for your timestamp columns, feel free to call other schema builder methods individually.

Example:

SchemaBuilder

SQL (Postgres)

tinyIncrements

Create an auto-incrementing column using an unsigned TINYINT type. This column is also set as the primary key for the table.

Example:

SchemaBuilder

SQL (MySQL)

tinyInteger

Create a column using a TINYINT equivalent type for your database.

Example (no precision):

SchemaBuilder

SQL (MySQL)

Example (with precision):

SchemaBuilder

SQL (MySQL)

unicodeLongText

Create a column using a LONGTEXT equivalent type for your database. For databases that distinguish between unicode- and non-unicode string data types, this function will create a unicode text field.

Example:

SchemaBuilder

SQL (MySQL)

SQL (MSSQL)

unicodeMediumText

Create a unicode-enabled column using a MEDIUMTEXT equivalent type for your database. For databases that distinguish between unicode- and non-unicode string data types, this function will create a unicode text field.

Example:

SchemaBuilder

SQL (MySQL)

SQL (MSSQL)

unicodeString

Create a column using a NVARCHAR equivalent type for your database. For databases that distinguish between unicode- and non-unicode string data types, this function will create a unicode string.

Example (with defaults):

SchemaBuilder

SQL (MySQL)

SQL (MSSQL)

Example (with length):

SchemaBuilder

SQL (MySQL)

Creates a column using a CHAR equivalent type for your database and a length of 35. Used in conjunction with the CFML createUUID method.

Example:

SchemaBuilder

SQL (MySQL)

13.0.0

Introduction

Introduction

Installation & Usage

Installation

Contributing & Filing Issues

Query Builder

Getting a New Query

Building Queries

For

forRaw

Raw Expressions

raw

When / Conditionals

when

Executing Queries

Options and Utilities

Query Options and Utilities

Default Options

Clone and Reset

Clone

Reset

Return Format

Column Formatter

Interception Points

preQBExecute

postQBExecute

Schema Builder

Creating Tables and Views

create

createAs

createView

Debugging

pretend

queryLog

External Links

Options and Utilities

Building Queries

Executing Queries

Column Formatter

For

forRaw

Clone and Reset

Clone

Reset

Introduction

Introduction

Contributing & Filing Issues

Requirements

Discussion & Help

Installation

Code Samples

Usage

Raw Expressions

raw

Return Format

Installation & Usage

Installation

Configuration Settings

SQL Type Inference

Integrating With FW/1

Wiring Up With DI/1

Usage In Your FW/1 Application

For further instructions on getting started with qb & FW/1, refer to this blog post.

Creating Tables and Views

create

createAs

createView

Interception Points

preQBExecute

postQBExecute

Debugging

pretend

queryLog

Getting a New Query

Query Options and Utilities

Default Options

When / Conditionals

when

newQuery

`when`

`when`

`create`