1 of 43

8.8.0 Introduction

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

Using qb, you can:

Quickly scaffold simple queries
Make complex, out-of-order queries possible
Abstract away differences between database engines

Requirements

Adobe ColdFusion 2016+
Lucee 5+

qb supports four major database grammars:

MySQL (MySQLGrammar)
Oracle (OracleGrammar)
Postgres (PostgresGrammar

Discussion & Help

The Box modules discussion group and community can be found here:

Installation

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

Code Samples

Compare these two examples:

The differences become even more stark when we introduce more complexity:

With qb you can easily handle setting order by statements before the columns you want or join statements after a where clause:

qb enables you to explore new ways of organizing your code by letting you pass around a query builder object that will compile down to the right SQL without you having to keep track of the order, whitespace, or other SQL gotchas!

Here's a gist with an example of the powerful models you can create with this!

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.

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

Installation & Usage

Installation

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

Migration Guide

v8.0.0

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

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:

Building Queries

From

from

Group By and Having

groupBy

Name

Type

Required

Default

Description

groups

Passing a single string will group by that one column.

You can also pass a list of column names. A single comma (",") will be used as the delimiter.

An array of column names can be provided.

Calling groupBy multiple times will to the current groups.

An can be passed in place of a column.

having

Adds a having clause to a query.

Expressions can be used in place of the column or the value.

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

Aggregates

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.

Options and Utilities

Query Options

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 combine this with WireBox to create custom QueryBuilder instances pointing to different datasources and even different grammars.

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"

Parent Query

Schema Builder

Create

This method allows you to create a table object.

Argument

Type

Required

Default

Description

table

string

true

The name of the table to create.

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 .

Drop

Dropping tables straightforward in qb.

For dropping columns or constraints, see Alter.

drop

Drop a table from the database.

Example:

SchemaBuilder

SQL (MySQL)

dropIfExists

Drop a table from the database if it exists.

Example:

SchemaBuilder

SQL (MySQL)

External Links

// Plain old CFML
q = 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
query = wirebox.getInstance('QueryBuilder@qb');
q = query.from('posts')
         .whereNotNull('published_at')
         .whereIn('author_id', [5, 10, 27])
         .get();

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

// Becomes

q = 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 } ]
);

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

}

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

config/ColdBox.cfc

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

QueryBuilder

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

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

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

select

from

orderBy

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

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

{Grammar}SchemaBuilderSpec.cfc

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

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" )
    .tap( function( q ) {
        writeOutput( q.toSQL() & "<br>" );
    } )
    .where( "active", "=", 1 )
    .tap( function( q ) {
        writeOutput( q.toSQL() & "<br>" );
    } );

country_id

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
)

QueryBuilder

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

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

// 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`)
)

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
)

Query Parameters and Bindings

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.

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

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

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.

Numeric SQL Type

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.

Automatic Scale Detection

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

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.

You can view the current SQL for the query with bindings inline for debugging purposes using the method.

Use these methods only for debugging. Modifying the bindings directly will likely cause issues when executing your query. Adding or removing bindings should be done using the public API.

getBindings

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.

getRawBindings

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

QueryBuilder

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

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" = []
};

columns

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.

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.

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.

QueryBuilder

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

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:

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]

Joins

Join clauses range from simple to complex including joining complete subqueries on multiple conditions. qb has your back with all of these use cases.

Table of Contents

join

Applies a join to the query. The simplest join is to a table based on two columns:

When doing a simple join using = as the operator, you can omit it and pass just the column names:

`` are also supported as the table argument (though you may prefer the readability of the method):

Using raw will most likely tie your code to a specific database, so think carefully before using the raw method if you want your project to be database agnostic.

When you need to specify more clauses to join, you can pass a function as the second argument:

You can specify clauses in your joins as well.

Conditions inside a join clause can be grouped using a function.

A preconfigured can also be passed to the join function. This allows you to extract shared pieces of code out to different functions.

joinWhere

Adds a join to another table based on a WHERE clause instead of an ON clause. WHERE clauses introduce parameters and parameter bindings whereas on clauses join between columns and don't need parameter bindings.

For simple joins, this specifies a column on which to join the two tables:

For complex joins, a function can be passed to first. This allows multiple on and where conditions to be applied to the join. See the documentation for for more information.

joinRaw

Uses the raw SQL provided to as the table for the join clause. All the other functionality of joinRaw matches the method. Additionally, there are , , and crossJoinRaw methods available.

Using joinRaw will most likely tie your code to a specific database, so think carefully before using the joinRaw method if you want your project to be database agnostic.

joinSub

Adds a join to a derived table. All the functionality of the method applies to constrain the query. The derived table can be defined using a QueryBuilder instance:

Alternatively, a function may be used to define the derived table:

Complex join conditions are also possible by passing a function as the third parameter:

leftJoin

leftJoinRaw

Uses the raw SQL provided to as the table for the left join clause. All the other functionality of leftJoinRaw matches the method.

Using leftJoinRaw will most likely tie your code to a specific database, so think carefully before using the leftJoinRaw method if you want your project to be database agnostic.

leftJoinSub

Adds a left join to a derived table. All the functionality of the method applies to define and constrain the query.

rightJoin

rightJoinRaw

Uses the raw SQL provided to as the table for the right join clause. All the other functionality of rightJoinRaw matches the method.

Using rightJoinRaw will most likely tie your code to a specific database, so think carefully before using the rightJoinRaw method if you want your project to be database agnostic.

rightJoinSub

Adds a right join to a derived table. All the functionality of the method applies to define and constrain the query.

crossJoin

crossJoinRaw

Uses the raw SQL provided to as the table for the cross join clause. Cross joins cannot be further constrained with on or where clauses.

Using crossJoinRaw will most likely tie your code to a specific database, so think carefully before using the crossJoinRaw method if you want your project to be database agnostic.

crossJoinSub

Adds a cross join to a derived table. The derived table can be defined using a QueryBuilder instance or a function just as with . Cross joins cannot be constrained, however.

newJoin

Creates a new . A is a specialized version of a QueryBuilder. You may call on or orOn to constrain the . You may also call any methods.

Creating a directly is useful when you need to share a join between different queries. You can create and configure the in a function and pass it to queries as needed.

Although a can be passed to , , , and crossJoin, the type of the will override the type of the function.

JoinClause

A JoinClause is a specialized version of a QueryBuilder. You may call on or orOn to constrain the JoinClause. You may also call any methods.

on

Applies a join condition to the JoinClause. An alias for whereColumn.

orOn

Applies a join condition to the JoinClause using an or combinator. An alias for orWhereColumn.

Preventing Duplicate Joins

You can optionally configure qb to ignore duplicate joins. With this setting turned on each JoinClause is inspected and checked if it matches any existing JoinClause instances on the query. This is useful if you have a table shared between optional constraints and want to ensure it is only added once.

You can opt-in to this behavior by setting preventDuplicateJoins = true in your moduleSettings in config/ColdBox.cfc.

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

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

Inserts, Updates, and Deletes

The following methods all have the same return value:

insert, update, and delete actions always return a query object for query, regardless of your configured returnFormat.

insert

Name

Type

Required

Default

Description

This call must come after setting the query's table using or .

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.

This is not the same as looping over and array and calling insert in the loop. Using an array with insert will batch the inserts in one SQL call. Looping over an array and calling insert each time will create a SQL request for each item in the array. Bottom line, pass your array to insert!

insertIgnore

Name

Type

Required

Default

Description

This call must come after setting the query's table using or .

Inserts data into a table while ignoring duplicate key conflicts.

target is only required for SQLServerGrammar and OracleGrammar

insertUsing

Name

Type

Required

Default

Description

This call must come after setting the query's table using or .

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:

returning

returning is only supported in PostgresGrammar and SqlServerGrammar. Using this method on unsupported grammars will result in an UnsupportedOperation exception. Be aware that using this method constrains your grammar choices.

Specifies columns to be returned from the insert query.

update

This call must come after setting the query's table using or .

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.

Updating Null values

Null values can be inserted by using queryparam syntax:

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

Updating with Subselects

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.

Updating with Joins

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

OracleGrammar does not support JOIN clauses inUPDATE statements. Consider using in your UPDATE statement instead.

addUpdate

Adds values to a later , similar to .

updateOrInsert

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.

upsert

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.

In most database grammars, the target columns are required to be primary key or unique indexes.

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.

delete

Deletes all records that the query returns.

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

QueryBuilder

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

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

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" ],
    );

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

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

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

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

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

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

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

Example:

SchemaBuilder

SQL (MySQL)

integer

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

Example (no precision):

SchemaBuilder

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)

SQL Server: Create a column using a uniqueidentifier.

MySQL and Others: Create a column using a CHAR equivalent type for your database and a length of 36. Used in conjunction with the CFML createUUID method.

Example:

SchemaBuilder

MySQL (SQL Server)

SQL (MySQL)

8.8.0

Introduction

hashtagIntroduction

hashtagRequirements

hashtagDiscussion & Help

hashtagInstallation

hashtagCode Samples

hashtagUsage

Installation & Usage

hashtagInstallation

Migration Guide

hashtagv8.0.0

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

Query Builder

Getting a New Query

Building Queries

From

hashtagfrom

Group By and Having

hashtaggroupBy

hashtaghaving

Raw Expressions

hashtagraw

When / Conditionals

hashtagwhen

Executing Queries

Aggregates

Options and Utilities

Query Options

hashtagDefault Options

Clone and Reset

hashtagClone

hashtagReset

Return Format

Parent Query

Schema Builder

Create

Drop

hashtagdrop

hashtagdropIfExists

External Links

Building Queries

Introduction

hashtagIntroduction

hashtagRequirements

hashtagDiscussion & Help

hashtagInstallation

hashtagCode Samples

hashtagUsage

Options and Utilities

Parent Query

Executing Queries

Clone and Reset

hashtagClone

hashtagReset

Getting a New Query

Query Options

hashtagDefault Options

hashtagnewQuery

hashtagRetrieving results from alternative datasources

Raw Expressions

hashtagraw

Installation & Usage

hashtagInstallation

When / Conditionals

hashtagwhen

Return Format

hashtagSQL Type Inferral

hashtagIntegrating With FW/1

hashtagWiring Up With DI/1

hashtagUsage In Your FW/1 Application

hashtagFor further instructions on getting started with qb & FW/1, refer to this blog postarrow-up-right.

From

hashtagfrom

Migration Guide

hashtagv8.0.0

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

hashtagv7.0.0

hashtagLucee 4.5 and Adobe ColdFusion 11 EOL

hashtagMSSQLGrammar renamed to SqlServerGrammar

Introduction

Requirements

Discussion & Help

Installation

Code Samples

Usage

Installation

v8.0.0

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

from

groupBy

having

raw

`when`

Default Options

Clone

Reset

drop

dropIfExists

Introduction

Requirements

Discussion & Help

Installation

Code Samples

Usage

Clone

Reset

Default Options

newQuery

Retrieving results from alternative datasources

raw

Installation

`when`

SQL Type Inferral

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.

from

v8.0.0

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

v7.0.0

Lucee 4.5 and Adobe ColdFusion 11 EOL

MSSQLGrammar renamed to SqlServerGrammar

Variadic Parameters Support Removed

defaultGrammar updated to be the full WireBox mapping

value method argument order changed

Some methods renamed `callback` to `query`

v5.0.0

table

fromRaw

fromSub

groupBy

having

drop

dropIfExists

count

max

min

sum

union

unionAll

Debugging a Single Query

toSQL

tap

dump

Debugging All Queries

cbDebugger

LogBox Appender

ColdBox Interception Points

index

foreignKey

primaryKey

unique

sharedLock

lockForUpdate

noLock

lock

clearLock

references