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

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.

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

SQL (MySQL)

foreignKey

Example:

SchemaBuilder

SQL (MySQL)

primaryKey

Create a primary key constraint from one or more columns.

Example:

SchemaBuilder

SQL (MySQL)

unique

Create a unique constraint from one or more columns.

Example:

SchemaBuilder

SQL (MySQL)

When from the Blueprint object, a Column object is returned. This column gives you access to a few modifier commands to further configure the column.

comment

Attach a comment to the column.

Example:

SchemaBuilder

SQL (MySQL)

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

SQL (MySQL)

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

SQL (MySQL)

primaryKey

Adds the column as a primary key for the table.

Example:

SchemaBuilder

SQL (MySQL)

references

Creates a foreign key constraint for the column.

Example:

SchemaBuilder

SQL (MySQL)

unsigned

Sets the column as unsigned.

Example:

SchemaBuilder

SQL (MySQL)

unique

Sets the column to have the UNIQUE constraint.

Example:

SchemaBuilder

SQL (MySQL)

6.4.0

• Allow Expressions (query.raw) in update statements.

Installation

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

Usage

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

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

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

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

var grammar = new qb.models.Grammars.MySQLGrammar();
var builder = new qb.models.Query.QueryBuilder( grammar );

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.

this.mappings = {
    "/qb" = expandPath("./subsystems/qb")
};

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

qb = {
  diLocations = "/qb/models",
  diConfig = {
    loadListener = function( di1 ) {
      di1.declare( "BaseGrammar" ).instanceOf( "qb.models.Query.Grammars.Grammar" ).done()
         .declare( "MySQLGrammar" ).instanceOf( "qb.models.Query.Grammars.MySQLGrammar" ).done()
         .declare( "QueryUtils" ).instanceOf( "qb.models.Query.QueryUtils" ).done()
         .declare( "QueryBuilder" ).instanceOf( "qb.models.Query.QueryBuilder" )
         .withOverrides({
            grammar = di1.getBean( "MySQLGrammar" ),
            utils = di1.getBean( "QueryUtils" ),
            returnFormat = "array"
         })
         .asTransient();
    }
  }
}

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

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

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

We welcome all types of contributions, following the all-contributors specification. And please take a look at our wonderful contributors on the README!

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.

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

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

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:

var getResults = query.from('users')
    .get('name,email,age');
writeDump(getResults);

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.

var getResults = query.from('users')
    .get('name as myAccountName,users.email,age');
writeDump(getResults);

var getResults = query.from('users as myTableAlias')
    .get( columns = ['name as myAccountName' ,'myTableAlias.email' ,'age'], options= { datasource='myOtherDatasource'} );
writeDump(getResults);

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

var getResults = query.from('users')
    .select('email')
    .distinct();
writeDump(getResults);

(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:

var getResults = query.from('users')
    .where('age','>=','18');
getResults = getResults.addSelect('name,email,age').get();
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:

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

//sql
SELECT * FROM `users`

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:

//qb
var getResults = query.from('users as u').get();

//sql
SELECT * FROM `users` AS `u`

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:

//qb
var getResults = query.from('users u').get();

//sql
SELECT * FROM `users` AS `u`

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:

//qb
var getResults = query.fromRaw('[users] u (nolock)').get();

//sql
SELECT * FROM [users] u (nolock)

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:

//qb
var getResults = query
    .fromRaw('dbo.generateDateTable(?, ?, ?) as dt', ['2017-01-01', '2017-12-31', 'm'])
    .get()
;

//sql
SELECT * FROM dbo.generateDateTable('2017-01-01', '2017-12-31', 'm') as dt

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:

//qb
var getResults = query
    .select('firstName', 'lastName')
    .fromSub('u', function (q){
        q
            .select('lName as lastName', 'fName as firstName')
            .from('users')
            .where('age', '>=', 21)
        ;
    })
    .orderBy('lastName')
    .get()
;

//sql
SELECT `firstName`, `lastName` FROM (SELECT `lName` as `lastName`, `fName` as `firstName` FROM `users` WHERE `age` >= 21) AS `u` ORDER BY `lastName`

Defining a derived table using a QueryBuilder instance

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

//qb
var derivedQA = query
    .select('lName as lastName', 'fName as firstName')
    .from('users')
    .where('age', '>=', 21)
;

var getResults = query
    .select('firstName', 'lastName')
    .fromSub('u', derivedQA)
    .orderBy('lastName')
    .get()
;

//sql
SELECT `firstName`, `lastName` FROM (SELECT `lName` as `lastName`, `fName` as `firstName` FROM `users` WHERE `age` >= 21) AS `u` ORDER BY `lastName`

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:

//qb
var getResults = query.from('blogs')
    .join('users', 'users.ID', '=', 'blogs.FK_usersID')
    .get();
writeDump(getResults);

//sql
SELECT users.name,blogs.title,blogs.ID as blogID FROM `blogs` INNER JOIN `users` ON `users`.`ID` = `blogs`.`FK_usersID`

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

//qb
var getResults = query.from('blogs')
    .joinRaw('[users] AS u (nolock)', 'u.ID', '=', 'blogs.FK_usersID')
    .get();
writeDump(getResults);

//sql
SELECT * FROM [blogs] INNER JOIN [users] AS u (nolock) ON [u].[ID] = [blogs].[FK_usersID]

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:

//qb
var getResults = query.from( "blogs" )
    .join( "users", function( j ) {
        j.on( "users.ID", "=", "blogs.FK_usersID" )
            .on( "users.type", "=", "blogs.type" );
    } )
    .get();

//sql
SELECT users.name,blogs.title,blogs.ID as blogID FROM `blogs` INNER JOIN `users` ON `users`.`ID` = `blogs`.`FK_usersID` AND `users`.`type` = `blogs`.`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:

//qb (leftJoin)
var getResults = query.from('blogs')
    .leftJoin('users', 'users.ID', '=', 'blogs.FK_usersID')
    .get();
writeDump(getResults);

//qb (rightJoin)
var getResults = query.from('blogs')
    .rightJoin('users', 'users.ID', '=', 'blogs.FK_usersID')
    .get();
writeDump(getResults);

//sql (leftJoin)
SELECT * FROM `blogs` LEFT JOIN `users` ON `users`.`ID` = `blogs`.`FK_usersID`

//sql (rightJoin)
SELECT * FROM `blogs` RIGHT JOIN `users` ON `users`.`ID` = `blogs`.`FK_usersID`

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:

var getResults = query.from('users')
    .crossJoin('departments', 'departments.ID', '=', 'users.FK_departmentID')
    .get();
writeDump(getResults);

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

//qb
var getResults = query
    .from('blogs')
    .joinSub('u', function (q){
        q
            .from('users')
            .where('disabled', 0)
        ;
    }, 'u.ID', '=', 'blogs.FK_usersID')
    .get();
writeDump(getResults);

//sql
SELECT * FROM `blogs` INNER JOIN (SELECT * FROM `users` WHERE `disabled` = 0) AS `u` ON `u`.`ID` = `blogs`.`FK_usersID`

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:

//qb
var addRecords = query.from( "users" )
        .insert( values = { "name" = "Robert", "email" = "robert@test.com", "age" = 55 } );
writeDump(addRecords);

//sql
INSERT INTO `users` (`age`, `email`, `name`) VALUES (55, `robert@test.com`, `Robert`)

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:

//qb
var addRecords = query.from( "users" )
.insert( values = [
        { "name" = "Robert", "email" = "robert@test.com", "age" = 55 },
        { "name" = "Jessica", "email" = "jessica@test.com", "age" = 31 },
        { "name" = "Ross", "email" = "ross@test.com", "age" = 9 }
    ] );
writeDump(addRecords);

//sql
INSERT INTO `users` (`age`, `email`, `name`)
VALUES (55, `robert@test.com`, `Robert`),
        (31, `jessica@test.com`, `Jessica`),
        (9, `ross@test.com`, `Ross`)

You can also insert records by strong typing them just like using cfqueryParam. Just adhere to the same syntax: { value : "", cfsqltype : "" } (https://cfdocs.org/cfqueryparam)

//qb
var addRecords = query
        .from( "users" )
        .insert( values = { 
                "name" = "Robert", 
                "email" = "robert@test.com", 
                "age" = { value : 55, cfsqltype : "integer" },
                "createdDate" = { value : now(), cfsqltype : "timestamp" }
        } );
writeDump(addRecords);

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.

// qb
var addRecords = query.from( "users" )
    .returning( "id" )
    .insert( { "name" = "Robert", "email" = "robert@test.com", "age" = 55 } );
writeDump(addRecords);

// Postgres
INSERT INTO "users" ("age", "email", "name")
VALUES (55, "robert@test.com", "Robert")
RETURNING "id"

// MSSQL
INSERT INTO [users] ([age], [email], [name])
OUTPUT INSERTED.[id]
VALUES (55, "robert@test.com", "Robert")

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

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]

Thanks goes to these wonderful people (emoji key):

This project follows the all-contributors specification. Contributions of any kind welcome!

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 11+

• Lucee 4.5+

qb supports four major database grammars:

• MSSQL (MSSQLGrammar)

• MySQL (MySQLGrammar)

• Oracle (OracleGrammar)

• Postgres (PostgresGrammar)

Installation

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

Code Samples

Compare these two examples:

// Plain old CFML
q = queryExecute("SELECT * FROM users");

// qb
query = wirebox.getInstance('QueryBuilder@qb');
q = query.from('users').get();

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

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

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

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

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! https://gist.github.com/elpete/80d641b98025f16059f6476561d88202

Usage

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

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

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

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

var grammar = new qb.models.Query.Grammars.MySQLGrammar();
var builder = new qb.models.Query.Builder( 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.

moduleSettings = {
    qb = {
        returningArrays = false
    }
};

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

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

Thanks goes to these wonderful people (emoji key):

This project follows the all-contributors specification. Contributions of any kind welcome!

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:

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.

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 = ?

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:

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

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.

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

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

This method allows you to create a table object.

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

Example:

This would convert to the following SQL in MySQL:

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

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:

You can also use Expressions inside an update statement:

Updating Null values

Null values can be inserted by using queryparam syntax:

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

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

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

references

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

Example:

SchemaBuilder

SQL (MySQL)

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)

Dropping tables straightforward in qb.

For dropping columns or constraints, see .

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)

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.

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

SQL (MySQL)

Alter an existing table in the database.

Example:

SchemaBuilder

SQL (MySQL)

Drop a table from the database.

Example:

SchemaBuilder

SQL (MySQL)

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

SQL (MySQL)

hasTable

Check if a table exists in the database.

Example:

SchemaBuilder

SQL (MySQL)

hasColumn

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

Example:

SchemaBuilder

SQL (MySQL)

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

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

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

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

addColumn

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

Any instance of Column is valid like those returned by the (integer, string, etc.) as well as the (unsigned, nullable, etc.).

Example:

SchemaBuilder

SQL (MySQL)

dropColumn

Drop a column on an existing table.

Example:

SchemaBuilder

SQL (MySQL)

modifyColumn

Modify an existing column on a table.

Example:

SchemaBuilder

SQL (MySQL)

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

SQL (MySQL)

addConstraint

Example:

SchemaBuilder

SQL (MySQL)

dropConstraint

Drop an existing table constraint.

Example:

SchemaBuilder

SQL (MySQL)

renameConstraint

Rename an existing table constraint.

Example:

SchemaBuilder

SQL (MySQL)

The Blueprint object has many column types available to construct your table schema. Additionally, you can modify the columns created and .

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

SQL (MySQL)

bigInteger

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

Example (no precision):

SchemaBuilder

SQL (MySQL)

Example (with precision):

SchemaBuilder

SQL (MySQL)

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

SQL (MySQL)

Example (custom length):

SchemaBuilder

SQL (MySQL)

boolean

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

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

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

Example:

SchemaBuilder

SQL (MySQL)

datetime

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