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.

6.4.0

Introduction

Using qb, you can:

• Quickly scaffold simple queries

• Make complex, out-of-order queries possible

• Abstract away differences between database engines

Requirements

• Adobe ColdFusion 11+

• Lucee 4.5+

qb supports four major database grammars:

• MSSQL (MSSQLGrammar)

• MySQL (MySQLGrammar)

• Oracle (OracleGrammar)

• Postgres (PostgresGrammar)

Installation

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!

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:

Return Format

You can influence the return format of the result in two ways.

By default, qb returns an array of structs as the result of your query. You can turn this behavior off by setting builder.setReturningArrays( false ) for one-offs or setting returningArrays = false in your ColdBox config.

If you want complete control over your return result, you can provide a closure as a returnFormat. The results of the closure will be returned as the results of the builder.

Interception Points

Two interception points are available from QB: preQBExecute and postQBExecute. These fire before and after the queryExecute call, respectively. The following information is available in the interceptData struct:

Contributors

Installation

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:

Integrating With FW/1

Note: These instructions assume a basic knowledge of FW/1, a working FW/1 application structure with qb installed in the /subsystems directory (manually or via CommandBox), and a database configured to run with your application.

Wiring Up With DI/1

Once the application structure is setup, now we need to wire up qb to a bean factory using DI/1.

First we will add a mapping in Application.cfc.

Next we need to tell DI/1 where qb's components are and how to reference them for later use in the application. We can do so by defining the configuration settings in the variables.framework.subsystems struct in Application.cfc. The example below makes use of a load listener to declare each component instance and pass in any constructor arguments.

Usage In Your FW/1 Application

Now that everything is configured, you can launch your application with CommandBox by entering start in the terminal or use whatever method you're accustomed to.

To access qb from your application's code, you can call on it by using getBeanFactory().

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.

Two interception points are available from QB: preQBExecute and postQBExecute. These fire before and after the queryExecute call, respectively. The following information is available in the interceptData struct:

Usage## Retrieving Results

Retrieving All Rows From A Table

// qb
var getAllResults = query.from( "users" ).get();
writeDump( getAllResults );

// sql
// select * from users

// more qb examples
var getAllResults = query.from( "users" )
    .get(
        columns = [ "Id", "Name" ],
        options = { datasource = "myAdditionalDatasource" }
    );

The get method returns an Array of Structs by default. Both columns and options are optional.

public any function get( any columns, struct options = {} )

Retrieving A Single Row / Column From A Table

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.

//qb
var getResults = query.from('users')
    .first();
writeDump(getResults);

//sql
select * from users limit(1)

public any function first( struct options = {} )

If you don't even need an entire row, you may extract a single value from each record using the values method. This method will return the value of the column directly:

query.from( "users" ).values( "email" );
// [ "john@example.com", "jane@example.com", "jim@example.com", ... ]

public any function values( required string column, struct options = {} );

If you only need a single column for the first record returned, use the value function:

query.from( "users" ).value( "email" );
// "john@example.com"

public any function value( required string column, struct options = {} );

Options parameter: Retrieving results from alternative datasources

In application.cfc you can specify your default datasource which will be used by qb. If you want to retrieve data from other datasources you can specify this in all retrieval functions by using the extra options parameter such as:

//qb
var getAllResults = query.from('users')
    .get( options={ datasource= 'MyOtherDatasourceName'} );
writeDump(getAllResults);

If you also want to use a non-default SQL Grammar you have to specify this when creating your querybuilder, e.g

var query =  wirebox.getInstance('QueryBuilder@qb')
    .setGrammar( wirebox.getInstance('MSSQLGrammar@qb') );

The query builder also provides a variety of aggregate methods such as count, max, min, and sum. You may call any of these methods after constructing your query:

var getResults = query.from('users')
    .count();
writeDump(getResults);

var getResults = query.from('users')
    .max('age');
writeDump(getResults);

var getResults = query.from('users')
    .min('age');
writeDump(getResults);

Of course, you may combine these methods with other clauses:

var getResults = query.from('users')
    .where('active', '=', 1)
    .max('age');
writeDump(getResults);

Queries come in many varieties—from the basic to extremely complex. In order to provide you maximum flexibility there are several ways to define the source table for you query.

Using the from() method

The most common method for defining the source table is using the from() method. For the majority of queries, the from() method is all you need. It's syntax is very easy:

This would return all columns from the users table.

NOTE: Alternatively, you can use the table() method as an alias to from().

Declaring a table alias

Optionally you can specify an alias for the table by using the syntax:

This would parse the string users as u and convert it into the correct syntax for current grammar.

Alternatively, you can use the ANSI SQL shorthand and leave out the as keyword:

Defining the from clause using raw SQL

Sometimes you need more control over your from clause in order to add grammar specific instructions, such as adding SQL Server table hints to your queries.

If you need complete control over your from clause you can use the fromRaw().

For example, to provide a table hint for a SQL Server query you could use:

Since the fromRaw() takes your string verbatim, it's important that you make sure your SQL declaration is escaped properly. Failure to properly escape your table names may result in SQL errors.

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

Adding bindings to your raw from clause

Many database engines allow you to define User Defined Functions. For example, SQL Server allows you to define UDFs that will return a table. In these type of cases, it may be necessary to bind parameters to your from clause.

You can bind parameters to the fromRaw() method by passing a secondary argument that is an array of the parameters to bind:

Derived tables

Complex queries often contain derived tables. Derived tables are essentially a temporal table defined as a subquery in the from statement.

You can build queries that comprise of derived tables by using the fromSub() method, which requires two arguments:

• The alias to use for the derived table (which is how you reference your query)

• Either a QueryBuilder instances or closure defining the subquery

Defining a derived table using a closure

The simplest way to create a derived table is by using a closure to define the subquery:

Defining a derived table using a QueryBuilder instance

Alternatively you can supply a QueryBuilder instance to the fromSub() method:

Specifying A Select Clause

Of course, you may not always want to select all columns from a database table. Using the from method, you can specify a custom from clause for the query:

Individual columns can contain fully-qualified names (i.e. "some_table.some_column"), fully-qualified names with table aliases (i.e. "alias.some_column"), and even set column aliases themselves (i.e. "some_column AS c"). Columns can be a single column, a list or columns (comma-separated), or an array of columns.

The distinct method allows you to force the query to return distinct results:

(Note that distinct applies to the entire query, not just certain fields.)

If you already have a query builder instance and you wish to add a column to its existing select clause, you may use the addSelect method:

You can influence the return format of the result in two ways.

By default, qb returns an array of structs as the result of your query. This is the same as specifiying array as your returnFormat:

moduleSettings = {
    qb = {
        returnFormat = "array"
    }
};

You can get the original query object that CFML generates by setting the returnFormat to query:

moduleSettings = {
    qb = {
        returnFormat = "query"
    }
};

If you want complete control over your return result, you can provide a closure as a returnFormat. The results of the closure will be returned as the results of the builder.

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

Parameters

By default, the correct sql type will be inferred from your parameters. 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 always pass a struct with the parameters you would pass to cfqueryparam.

//qb
var getResults = query.from('users')
    .where('age','>=', { value = 18, cfsqltype = "CF_SQL_INTEGER" })
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` >= 18

Simple Where Clauses

You may use the where method on a query builder instance to add where clauses to the query. The most basic call to where requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. Finally, the third argument is the value to evaluate against the column.

For example, here is a query that verifies the value of the "age" column is greater than or equal to 18:

//qb
var getResults = query.from('users')
    .where('age','>=','18')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` >= 18

For convenience, if you simply want to verify that a column is equal to a given value, you may pass the value directly as the second argument to the where method:

//qb
var getResults = query.from('users')
    .where('age','18')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` = 18

Of course, you may use a variety of other operators when writing a where clause:

//qb
var getResults = query.from('users')
    .where('age','>=','18')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` >= 18

//qb
var getResults = query.from('users')
    .where('age','<>','18')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` <> 18


//qb
var getResults = query.from('users')
    .where('name','like','A%')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `name` LIKE 'A%'

Or Statements

You may chain where constraints together as well as add or clauses to the query. The orWhere method accepts the same arguments as the where method:

//qb
var getResults = query.from('users')
    .where('name','like','A%')
    .orWhere('age','>','30')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `name` LIKE 'A%' OR `age` > 30

Additional Where Clauses

whereBetween / whereNotBetween

The whereBetween method verifies that a column's value is between two values:

//qb
var getResults = query.from('users')
    .whereBetween('age','18','21')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` BETWEEN 18 AND 21

The whereNotBetween method verifies that a column's value lies outside of two values:

//qb
var getResults = query.from('users')
    .whereNotBetween('age','18','21')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` NOT BETWEEN 18 AND 21

whereIn / whereNotIn (sub-queries)

The whereIn method verifies that a given column's value is contained within the provided array or QueryBuilder object:

Array:

var getResults = query.from('users')
    .whereIn('age',[ 17, 18, 19, 20, 21 ])
    .get();
writeDump(getResults);

QueryBuilder (fetch all users whose age is in the all_ages table with a value between 17 and 21):

var getResults = query.from('users')
       .whereIn('age', function ( subQuery ) {
        return subQuery.select( "age" )
        .from( "all_ages" )
        .whereBetween("age","17","21");
    })
    .get();
writeDump(getResults);

The whereNotIn method verifies that the given column's value is not contained in the provided array of QueryBuilder object:

var getResults = query.from('users')
    .whereNotIn('age',[ 17, 18, 19, 20, 21 ])
    .get();
writeDump(getResults);

whereNull / whereNotNull

The whereNull method verifies that the value of the given column is NULL:

//qb
var getResults = query.from('users')
    .whereNull('modifiedDate')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `modifiedDate` IS NULL

The whereNotNull method verifies that the column's value is not NULL:

//qb
var getResults = query.from('users')
    .whereNotNull('modifiedDate')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `modifiedDate` IS NOT NULL

whereExists / whereNotExists

The whereExists method:

//qb
var getResults = query.from('users')
    .whereExists( function( q ) {
        q.select( q.raw( 1 ) ).from( "departments" )
            .where( "departments.ID", "=", q.raw( '"users.FK_departmentID"' ) );
    })
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE EXISTS (SELECT 1 FROM `departments` WHERE `departments`.`ID` = "users.FK_departmentID")

whereColumn

The whereColumn method may be used to verify that two columns are equal:

//qb
var getResults = query.from('users')
    .whereColumn('modifiedDate','createdDate')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `modifiedDate` = `createdDate`

You may also pass a comparison operator to the method:

//qb
var getResults = query.from('users')
    .whereColumn('modifiedDate','<>','createdDate')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `modifiedDate` <> `createdDate`

WHERE (a = ? OR b = ?) AND c = ?

Here is an example of how to strategically place parentheses with OR using closures.

//qb
var getResults = query.from('users')
    .where( function( q ) {
        q.where( "a", 1 ).orWhere( "b", 2 );
    } )
    .where( "c", 3 );
writeDump(getResults);

// sql
SELECT * FROM `users` WHERE (a = ? OR b = ?) AND c = ?

orderBy

The orderBy method allows you to sort the result of the query by a given column. The first argument to the orderBy method should be the column you wish to sort by, while the second argument controls the direction of the sort and may be either asc or desc:

//qb
var getResults = query.from('users')
    .where('age','>=','18')
    .orderBy('modifiedDate','desc')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` >= 18 ORDER BY `modifiedDate` DESC

groupBy / having

The groupBy and having methods may be used to group the query results. The having method's signature is similar to that of the where method:

//qb
var getResults = query.from('users')
    .groupBy('FK_departmentID')
    .having('age','>','21')
    .orderBy('age','desc')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` GROUP BY `FK_departmentID` HAVING `age` > 21 ORDER BY `age` DESC

take / limit

To limit the number of results returned from the query, you may use the take method:

//qb
var getResults = query.from('users')
    .where('age','>=','18')
    .orderBy('modifiedDate','desc')
    .take(5)
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` >= ? ORDER BY `modifiedDate` DESC LIMIT 5

Alternatively, you may use the limit method:

//qb
var getResults = query.from('users')
    .where('age','>=','18')
    .orderBy('modifiedDate','desc')
    .limit(5)
    .get();
writeDump(getResults);

//sql
SELECT * FROM `users` WHERE `age` >= ? ORDER BY `modifiedDate` DESC LIMIT 5

offset

To offset the number of results returned from the query, use the offset method:

// This will return rows 26 through 50
query.from( "users" )
    .offset( 25 )
    .limit( 25 )
    .get();

forPage

Combine limit and offset in one method. Pass the current page number and the number of results per page (limit) and we'll calculate the rest.

// This will return records 51 to 75
query.from( "users" )
    .forPage( 3, 25 )
    .get();

Basic if and else

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

var builder = builder.from( "posts" );
if ( rc.recent ) {
    builder.orderBy( "published_date", "desc" );
}
var results = builder.get();

This works, but breaks chainability. A better way is to use the when helper method.

when

We can rewrite the above query like so:

var results = builder.from( "posts" )
    .when( rc.recent, function( q ) {
        q.orderBy( "published_date", "desc" );
    } )
    .get();

Nice. We keep chainability this way and reduce the number of temporary variables we need.

Inner Join Clause

The query builder may also be used to write join statements. To perform a basic "inner join", you may use the join method on a query builder instance. The first argument passed to the join method is the name of the table you need to join to, while the remaining arguments specify the column constraints for the join. Of course, as you can see, you can join to multiple tables in a single query:

Joining using raw SQL

Sometimes you need more control over your join clause in order to add grammar specific instructions, such as adding SQL Server table hints to your queries.

If you need complete control over your join clause you can use the joinRaw() method

Since the joinRaw() takes your string verbatim, it's important that you make sure your SQL declaration is escaped properly. Failure to properly escape your table names may result in SQL errors.

NOTE: Using the 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.

NOTE: All of the join methods have a *Raw equivalent method. This means you can use leftJoinRaw(), rightJoinRaw(), etc.

Complex (multi-conditional) Join Clause

For a compound join clause, pass in the name of the table as the first argument (just as before) but instead of passing the remaining arguments describing the single join clause, we'll pass a single closure with a joinClause argument. Consider a (contrived) example where our users and blogs had to match not only ID but also type:

Left/Right Join Clause

If you would like to perform a "left/right join" instead of an "inner join", use the leftJoin / rightJoin method. The leftJoin / rightJoin method has the same signature as the join method:

Cross Join Clause

To perform a "cross join" use the crossJoin method with the name of the table you wish to cross join to. Cross joins generate a cartesian product between the first table and the joined table:

Joining using Derived Tables

Complex queries often contain derived tables, which are temporal, subqueries defined inline within your SQL.

To join your main table to a derived table you can use the joinSub() methods. Each join method has a corresponding "sub" method which you can use when you need to use a derived table (i.e. leftJoinSub(), rightJoinSub(), etc).

These functions differ slightly than the normal join methods, because the first two arguments specify:

• The alias to use for the derived table (which is how you reference your query)

• Either a QueryBuilder instances or closure defining the subquery

Common Table Expressions (CTEs) are powerful SQL concept that allow you to create re-usable temporal resultset, 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.

NOTE: Some database engines require the recursive keyword is implemented anytime at least one of your CTEs is recursive, but some database engines (e.g. SQL Server and Oracle) do not require the keyword. For engines that do not require the recursive keyword the grammar will manage adding the keyword if necessary. If your query does use recursion, you should always use the withRecursive() method to avoid issues with other grammars.

Simple CTE using a closure

Building a CTE is as easy as using the with() method with a closure:

// qb
var getResults = query
    .with('UserCTE', function (q){
        q
            .select('fName as firstName', 'lName as lastName')
            .from('users')
            .where('disabled', 0)
        ;
    })
    .from('UserCTE')
    .get();
writeDump(getResults);

// sql
WITH `UserCTE` AS (
    SELECT
        `fName` as `firstName`,
        `lName` as `lastName`
    FROM `users`
    WHERE `disabled` = 0
) SELECT * FROM `UserCTE`

Simple CTE using a QueryBuilder instance

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

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

var getResults = query
    .with('UserCTE', cte)
    .from('UserCTE')
    .get();
writeDump(getResults);

// sql
WITH `UserCTE` AS (
    SELECT
        `fName` as `firstName`,
        `lName` as `lastName`
    FROM `users`
    WHERE `disabled` = 0
)
SELECT * FROM `UserCTE`

Multiple CTEs

A single query can reference multiple CTEs:

// qb
var getResults = 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();
writeDump(getResults);

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

Recursive CTEs

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:

// qb
var getResults = query
    .withRecursive('Hierarchy', function (q){
        q
            // get the parent rows only
            .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();
writeDump(getResults);

// sql
;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]

The query builder also lets you create union statements on your queries. When merging multiple queries together using a union statement, there are two methods you can use merge the queries together:

• union() — This method builds a SQL statement using the ANSI SQL union clause which combines two SQL queries into a single resultset containing all the matching rows. The two queries must have the same defined columns and compatible data types or the SQL engine will generate an error. The union clause only returns unique rows.

• unionAll() — This builds a SQL statement using the union all clause. This is the same as union but includes duplicate rows.

The union methods take either a Query Builder instance or a closure, which you use to define a new QueryBuilder instance.

Union statements are added in the order in which the union methods are invoked, but the union statements can be in any order in your API call stack. This means you can safely declare your union method calls before the select, from and orderBy calls on the source Query Builder instance.

IMPORTANT: The QueryBuilder instances passed to a union statement cannot contain a defined order. Any use of the orderBy() method on the unioned QueryBuilder instances will result in an exception. To order the results, add an orderBy() call to the parent source Query Builder instance.

Simple union using a callback

The easiest way to combine union the results of multiple queries is by using a callback to the union methods:

//qb
var getResults = query
        .select('title')
        .from('blogs')
        .whereIn('id', [1, 2, 3])
        .union(function (q){
            q
                .select('title')
                .from('blogs-archive')
                .whereIn('id', [1, 2, 3])
            ;
        })
    .get();
writeDump(getResults);

//sql
SELECT `title` FROM `blogs` WHERE `id` IN (1, 2, 3) UNION SELECT `title` FROM `blogs-archive` WHERE `id` IN (1, 2, 3)

Using a Query Builder instance

Alternatively, you can use another Query Builder instance and pass it to the union methods:

var unionQB = query
    .select('title')
    .from('blogs-archive')
    .whereIn('id', [1, 2, 3])
;

//qb
var getResults = query
        .select('title')
        .from('blogs')
        .whereIn('id', [1, 2, 3])
        .union(unionQB)
    .get();
writeDump(getResults);

//sql
SELECT `title` FROM `blogs` WHERE `id` IN (1, 2, 3) UNION SELECT `title` FROM `blogs-archive` WHERE `id` IN (1, 2, 3)

union all

If you want to make sure that all duplicate rows are returned, use the unionAll() method instead of union():

//qb
var getResults = query
        .select('title')
        .from('blogs')
        .whereIn('id', [1, 2, 3])
        .unionAll(function (q){
            q
                .select('title')
                .from('blogs-archive')
                .whereIn('id', [1, 2, 3])
            ;
        })
    .get();
writeDump(getResults);

//sql
SELECT `title` FROM `blogs` WHERE `id` IN (1, 2, 3) UNION ALL SELECT `title` FROM `blogs-archive` WHERE `id` IN (1, 2, 3)

Ordering union queries

To order a union query, only the parent query object can contain an orderBy() directive. If any of the Query Builder instances passed to a union method contain an orderBy directive an exception will be thrown when you attempt to either execute the query or generate the SQL.

The follow shows how to order the results:

//qb
var getResults = query
        .select('title')
        .from('blogs')
        .whereIn('id', [1, 2, 3])
        .union(function (q){
            q
                .select('title')
                .from('blogs-archive')
                .whereIn('id', [1, 2, 3])
            ;
        })
        .orderBy("title", "desc")
    .get();
writeDump(getResults);

//sql
SELECT `title` FROM `blogs` WHERE `id` IN (1, 2, 3) UNION SELECT `title` FROM `blogs-archive` WHERE `id` IN (1, 2, 3) ORDER BY `title` DESC

NOTE: The orderBy() call does have to be after the calls to the union methods.

Multiple union statements

Your query can contain multiple union methods:

//qb
var getResults = query
        .select('title')
        .from('blogs')
        .whereIn('id', [1, 2, 3])
        // order can be *before* union statements
        .orderBy("title", "desc")
        .unionAll(function (q){
            q
                .select('title')
                .from('blogs-archive-1990-decade')
                .whereIn('id', [1, 2, 3])
            ;
        })
        .unionAll(function (q){
            q
                .select('title')
                .from('blogs-archive-2000-decade')
                .whereIn('id', [1, 2, 3])
            ;
        })
    .get();
writeDump(getResults);

//sql
SELECT `title` FROM `blogs` WHERE `id` IN (1, 2, 3) UNION ALL SELECT `title` FROM `blogs-archive-1990-decade` WHERE `id` IN (1, 2, 3) UNION ALL SELECT `title` FROM `blogs-archive-2000-decade` WHERE `id` IN (1, 2, 3) ORDER BY `title` DESC

The query builder also provides an insert method for inserting records into the database table. The insert method accepts an array of column names and values:

You may even insert several records into the table with a single call to insert by passing an array of structs. Each struct represents a row to be inserted into the table:

Returning

Certain grammars have the ability to return values from an insert statement. That can be useful if you use your built-in database functions to generate primary keys that you want to retrieve.

If you attempt to use returning on grammars that do not support it, you will recieve a UnsupportedOperation exception.

The query builder may also be used to delete records from the table via the delete method. You may constrain delete statements by adding where clauses before calling the delete method:

//qb
var deleteRecords = query.from( "users" )
    .whereID( 10 )
    .delete();
writeDump(deleteRecords);

//sql
DELETE FROM `users` WHERE `ID` = 10;

This utilizes the where clause on a column other than the ID:

//qb
var deleteRecords = query.from( "users" )
    .where( 'age', '>', 50 )
    .delete();
writeDump(deleteRecords);

//sql
DELETE FROM `users` WHERE `age` > 50

At times you may need to duplicate a query. Perhaps you need the count of all the records before paginating it. Using clone you have a performant way to duplicate a query

var q1 = query.from( "users" ).where( "firstName", "like", "Jo%" );
var q2 = q1.clone();
q2.getFrom(); // "users"

Of course, in addition to inserting records into the database, the query builder can also update existing records using the update method. The update method, like the insert method, accepts an array of column and value pairs containing the columns to be updated. You may constrain the update query using where clauses:

//qb
var addRecords = query.from( "users" )
    .whereID( 10 )
    .update(  { "name" = "Roberto", "email" = "roberto@test.com", "age" = 55 });
writeDump(addRecords);

//sql
UPDATE `users` SET `age` = 55, `email` = `roberto@test.com`, `name` = `Roberto` WHERE `ID` = 10

You can also use Expressions inside an update statement:

query.from( "posts" )
    .whereID( 10 )
    .update( { "likes" = query.raw( "likes + 1" ) } );
    
// SQL:      UPDATE `posts` SET `likes` = likes + 1 WHERE `ID` = ?
// Bindings: [ 10 ]

Updating Null values

Null values can be inserted by using queryparam syntax:

query.from("user")
		.whereId( 10 )
		.update( {
			manager_FK = { value = "", null=true },
		} )

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

query.from("user")
		.whereId( 10 )
		.update( {
			manager_FK = { value = null },
		} )

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

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

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

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

An easy way to get a new query builder is to use the newQuery method available on the builder.

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

The newQuery method will keep the current grammar, return format, and utils attached to the called query builder.

QB ships with a schema builder to help you build your database objects. This provides a few benefits:

• The syntax is expressive and fluent, making it easy to understand what is being executed

• The syntax is database-agnostic. Specific quirks are isolated in a Grammar file, making it easy to migrate between engines.

You start with a SchemaBuilder object. The SchemaBuilder takes the same Grammar that a QueryBuilder takes.

// manually
var schema = new qb.models.schema.SchemaBuilder(
    new qb.models.grammars.MySQLGrammar()
);

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

Note: the SchemaBuilder is a transient, and a new one should be created for each operation.

The SchemaBuilder has four main methods to start your database object creation:

Create a new table in the database.

Example:

SchemaBuilder

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

SQL (MySQL)

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

Alter an existing table in the database.

Example:

SchemaBuilder

schema.alter( "users", function( table ) {
    table.addConstraint( table.unique( "username" ) );
    table.dropColumn( "last_logged_in" );
} );

SQL (MySQL)

ALTER TABLE `users` ADD CONSTRAINT `unq_users_username` UNIQUE (`username`);
ALTER TABLE `users` DROP COLUMN `last_logged_in`;

Drop a table from the database.

Example:

SchemaBuilder

schema.drop( "user_logins" );

SQL (MySQL)

DROP TABLE `user_logins`

Additionally, there are a few utility methods defined on SchemaBuilder as well:

rename

Rename a table from an old name to a new name

Example:

SchemaBuilder

schema.rename( "posts", "blog_posts" );

SQL (MySQL)

RENAME TABLE `posts` TO `blog_posts`

hasTable

Check if a table exists in the database.

Example:

SchemaBuilder

schema.hasTable( "users" );

SQL (MySQL)

SELECT 1
FROM `information_schema`.`tables`
WHERE `table_name` = 'users'

hasColumn

Check if a column exists in a table in the database.

Example:

SchemaBuilder

schema.hasColumn( "users", "last_logged_in" );

SQL (MySQL)

SELECT 1
FROM `information_schema`.`columns`
WHERE `table_name` = 'users'
    AND `column_name` = 'last_logged_in'

This method allows you to create a table object.

Example:

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

This would convert to the following SQL in MySQL:

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

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

references

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

Example:

SchemaBuilder

SQL (MySQL)

onTable

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

Example:

SchemaBuilder

SQL (MySQL)

onUpdate

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

Example:

SchemaBuilder

SQL (MySQL)

onDelete

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

Example:

SchemaBuilder

SQL (MySQL)

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

index

Create a generic index from one or more columns.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.string( "first_name" );
    table.string( "last_name" );
    table.index( [ "first_name", "last_name" ], "idx_users_full_name" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `first_name` VARCHAR(255) NOT NULL,
    `last_name` VARCHAR(255) NOT NULL,
    INDEX `idx_users_full_name` (`first_name`, `last_name`)
)

foreignKey

Create a foreign key constraint from one or more columns. Follow up this call with calls to the TableIndex's references and onTable methods.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" );
    table.foreignKey( "country_id" ).references( "id" ).onTable( "countries" );
} );

SQL (MySQL)

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
)

primaryKey

Create a primary key constraint from one or more columns.

Example:

SchemaBuilder

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

SQL (MySQL)

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

unique

Create a unique constraint from one or more columns.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.increments( "id" );
    table.string( "username ");
    table.unique( "username" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    UNIQUE (`username`)
)

comment

Attach a comment to the column.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.integer( "age" ).comment( "Do not lie about your age" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `age` INTEGER NOT NULL COMMENT `Do not lie about your age`
)

default

Sets a default value for the column.

Note: The value is not escaped, allowing you to specify functions like NOW() or literals like 1. To specify a literal string, wrap the value in quotes.

Example:

SchemaBuilder

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

SQL (MySQL)

CREATE TABLE `users` (
    `is_active` TINYINT(1) DEFAULT 1,
    `created_date` TIMESTAMP DEFAULT NOW(),
    `country` VARCHAR(255) DEFAULT 'USA'
)

nullable

Sets the column to allow null values.

All columns are created as NOT NULL by default. As such, there is no notNull method.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.timestamp( "last_logged_in" ).nullable()
} );

SQL (MySQL)

CREATE TABLE `users` (
    `last_logged_in` TIMESTAMP
)

primaryKey

Adds the column as a primary key for the table.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.uuid( "id" ).primaryKey();
} );

SQL (MySQL)

CREATE TABLE `users` (
    `id` CHAR(35) NOT NULL,
    CONSTAINT `pk_users_id` PRIMARY KEY (`id`)
)

references

Creates a foreign key constraint for the column.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" ).references( "id" ).onTable( "countries" ).onDelete( "cascade" );
} );

SQL (MySQL)

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
)

unsigned

Sets the column as unsigned.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.integer( age" ).unsigned();
} );

SQL (MySQL)

CREATE TABLE `users` (
    `age` INTEGER UNSIGNED NOT NULL
)

unique

Sets the column to have the UNIQUE constraint.

Example:

SchemaBuilder

schema.create( "email", function( table ) {
    table.string( email" ).unique();
} );

SQL (MySQL)

CREATE TABLE `users` (
    `email` VARCHAR(255) NOT NULL UNIQUE
)

Dropping tables straightforward in qb.

drop

Drop a table from the database.

Example:

SchemaBuilder

schema.drop( "user_logins" );

SQL (MySQL)

DROP TABLE `user_logins`

dropIfExists

Drop a table from the database if it exists.

Example:

SchemaBuilder

schema.dropIfExists( "user_logins" );

SQL (MySQL)

DROP TABLE IF EXISTS `user_logins`

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

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.

Example:

SchemaBuilder

schema.alter( "users", function( table ) {
    table.addColumn( table.boolean( "is_active" ) );
} );

SQL (MySQL)

ALTER TABLE `users` ADD `is_active` TINYINT(1) NOT NULL

dropColumn

Drop a column on an existing table.

Example:

SchemaBuilder

schema.alter( "users", function( table ) {
    table.dropColumn( "username" );
} );

SQL (MySQL)

ALTER TABLE `users` DROP COLUMN `username`

modifyColumn

Modify an existing column on a table.

Example:

SchemaBuilder

schema.alter( "users", function( table ) {
    table.modifyColumn( "name", table.string( "username" ) );
} );

SQL (MySQL)

ALTER TABLE `users` CHANGE `name` `username` VARCHAR(255) NOT NULL

renameColumn

Rename a column on a table. A full Column instance is required as the second argument for Grammars that need to redeclare the column definition when renaming.

Example:

SchemaBuilder

schema.alter( "users", function( table ) {
    table.renameColumn( "name", table.string( "username" ) );
} );

SQL (MySQL)

ALTER TABLE `users` CHANGE `name` `username` VARCHAR(255) NOT NULL

addConstraint

Add an index or key to an existing table. Any TableIndex instance is valid, like those created by the index methods (unique, index, primaryKey, etc.).

Example:

SchemaBuilder

schema.alter( "users", function( table ) {
    table.addConstraint( table.unique( "username" ) );
} );

SQL (MySQL)

ALTER TABLE `users` ADD CONSTRAINT `unq_users_username` UNIQUE (`username`)

dropConstraint

Drop an existing table constraint.

Example:

SchemaBuilder

schema.alter( "users", function( table ) {
    table.dropConstraint( "unq_users_full_name" );
    table.dropConstraint( table.unique( "username" ) );
} );

SQL (MySQL)

ALTER TABLE `users` DROP INDEX `unq_users_full_name`
ALTER TABLE `users` DROP INDEX `unq_users_username`

renameConstraint

Rename an existing table constraint.

Example:

SchemaBuilder

schema.alter( "users", function( table ) {
    table.renameConstraint( "unq_users_first_name_last_name", "unq_users_full_name" );
} );

SQL (MySQL)

ALTER TABLE `users` RENAME INDEX `unq_users_first_name_last_name` TO `unq_users_full_name`

bigIncrements

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

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.bigIncrements( "id" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    CONSTRAINT `pk_users_id` PRIMARY KEY (`id`)
)

bigInteger

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

Example (no precision):

SchemaBuilder

schema.create( "users", function( table ) {
    table.bigInteger( "salary" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `salary` BIGINT NOT NULL
)

Example (with precision):

SchemaBuilder

schema.create( "users", function( table ) {
    table.bigInteger( "salary", 5 );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `salary` BIGINT(5) NOT NULL
)

bit

Create a column using a BIT equivalent type for your database. The length can be specified as the second argument.

Example (default length):

SchemaBuilder

schema.create( "users", function( table ) {
    table.bit( "is_active" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `is_active` BIT(1) NOT NULL
)

Example (custom length):

SchemaBuilder

schema.create( "users", function( table ) {
    table.bit( "is_active", 2 );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `is_active` BIT(2) NOT NULL
)

boolean

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

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.boolean( "is_subscribed" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `is_subscribed` TINYINT(1) NOT NULL
)

char

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

Example (default length):

SchemaBuilder

schema.create( "students", function( table ) {
    table.char( "grade" );
} );

SQL (MySQL)

CREATE TABLE `students` (
    `grade` CHAR(1) NOT NULL
)

Example (custom length):

SchemaBuilder

schema.create( "users", function( table ) {
    table.char( "tshirt_size", 4 );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `tshirt_size` CHAR(4) NOT NULL
)

date

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

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.date( "birthday" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `birthday` DATE NOT NULL
)