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.

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.

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

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`

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

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

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.

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:

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.

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:

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:

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

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:

Additional Where Clauses

whereBetween / whereNotBetween

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

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

whereIn / whereNotIn (sub-queries)

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

Array:

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

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

whereNull / whereNotNull

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

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

whereExists / whereNotExists

The whereExists method:

whereColumn

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

You may also pass a comparison operator to the method:

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

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

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:

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

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:

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:

take / limit

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

Alternatively, you may use the limit method:

offset

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

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.

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

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:

Using a Query Builder instance

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

union all

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

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:

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:

Basic if and else

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

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

when

We can rewrite the above query like so:

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

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"

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.

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

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]

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.

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

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 columns and indexes for your tables.

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.

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

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

Create a new table in the database.

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

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

Alter an existing table in the database.

In addition to using the columns and indexes off of the passed-in Blueprint object, the Blueprint contains helpers such as addConstraint, removeConstraint, addColumn, renameColumn, and dropColumn to assist in altering existing tables.

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 and dropIfExists

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'

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)

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.

Example:

SchemaBuilder

SQL (MySQL)

decimal

Create a column using a DECIMAL equivalent type for your database. The length and precision can be specified as the second and third arguments.

Example (with defaults):

SchemaBuilder

SQL (MySQL)

Example (with length):

SchemaBuilder

SQL (MySQL)

Example (with precision):

SchemaBuilder

SQL (MySQL)

enum

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

Example:

SchemaBuilder

SQL (MySQL)

float

Create a column using a FLOAT equivalent type for your database. The length and precision can be specified as the second and third arguments.

Example (with defaults):

SchemaBuilder

SQL (MySQL)

Example (with length):

SchemaBuilder

SQL (MySQL)

Example (with precision):

SchemaBuilder

SQL (MySQL)

increments

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

Example:

SchemaBuilder

SQL (MySQL)

integer

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

Example (no precision):

SchemaBuilder

SQL (MySQL)

Example (with precision):

SchemaBuilder

SQL (MySQL)

json

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

Example:

SchemaBuilder

SQL (MySQL)

longText

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

Example:

SchemaBuilder

SQL (MySQL)

mediumIncrements

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

Example:

SchemaBuilder

SQL (MySQL)

mediumInteger

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

Example (no precision):

SchemaBuilder

SQL (MySQL)

Example (with precision):

SchemaBuilder

SQL (MySQL)

mediumText

Create a column using a MEDIUMTEXT equivalent type for your database. For databases that distinguish between unicode and non-unicode fields, creates a non-unicode field.

Example: