1 of 14

Building Queries

Selects

Specifying A Select Clause

You may not always want to select all columns from a database table. You can influence the select list of a query with the following methods.

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

select

When calling select any previous columns are discarded. If you want to incrementally select columns, use the addSelect method.

If you pass no columns to this method, it will default to "*".

QueryBuilder

query.select( [ "fname AS firstName", "age" ] ).from( "users" );

SQL (MySQL)

SELECT `fname` AS `firstName`, `age` FROM `users`

distinct

Calling distinct will cause the query to be executed with the DISTINCT keyword.

QueryBuilder

query.select( "username" ).distinct().from( "users" );

SQL (MySQL)

SELECT DISTINCT `username` FROM `users`

distinct applies to the entire query, not just certain fields.

addSelect

This method adds the columns passed to it to the currently selected columns.

If the QueryBuilder is currently selecting all columns ("*") when this method is called, the incoming columns will becoming the only columns selected.

QueryBuilder

query.addSelect( [ "fname AS firstName", "age" ] ).from( "users" );

SQL (MySQL)

SELECT `fname` AS `firstName`, `age` FROM `users`

selectRaw

A shortcut to use a raw expression in the select clause.

The expression is added to the other already selected columns.

(To learn more about raw and expressions, check out the docs on Raw Expressions.)

QueryBuilder

query.selectRaw( "YEAR(birthdate) AS birth_year" ).from( "users" );

SQL (MySQL)

SELECT YEAR(birthdate) AS birth_year FROM `users`

subSelect

The method lets you pass either a callback or a QueryBuilder instance to be used as a subselect expression. If a callback is passed it will be passed a new query instance as the only parameter.

The subselect is added to the other already selected columns.

QueryBuilder

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

SQL (MySQL)

SELECT (
    SELECT MAX(created_date)
    FROM `logins`
    WHERE `users`.`id` = `logins`.`user_id`
) AS `last_login_date`
FROM `users

clearSelect

Clears out the selected columns for a query along with any configured select bindings.

QueryBuilder

query.from( "users" )
    .select( [ "fname AS firstName", "age" ] )
    .clearSelect();

SQL (MySQL)

SELECT * FROM `users`

reselect

Clears out the selected columns for a query along with any configured select bindings. Then sets a selection of columns to select from the query. Any valid argument to select can be passed here.

QueryBuilder

query.from( "users" )
    .select( [ "fname AS firstName", "age" ] )
    .reselect( "username" );

SQL (MySQL)

SELECT `username` FROM `users`

reselectRaw

Clears out the selected columns for a query along with any configured select bindings. Then adds an Expression or array of expressions to the already selected columns.

QueryBuilder

query.from( "users" )
    .select( [ "fname AS firstName", "age" ] )
    .reselectRaw( "YEAR(birthdate) AS birth_year" );

SQL (MySQL)

SELECT YEAR(birthdate) AS birth_year FROM `users`

From

from

Used to set the base table for the query.

QueryBuilder

query.from( "users" );

MySQL

SELECT * FROM `users`

You can optionally specify an alias for the table.

QueryBuilder

query.from( "users as u" );

MySQL

SELECT * FROM `users` AS `u`

table

An alias for from where you like how calling table looks.

QueryBuilder

query.table( "users" ).insert( { "name" = "jon" } );

MySQL

INSERT INTO `users` (`name`) VALUES (?)

fromRaw

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.

QueryBuilder

query.fromRaw( "[users] u (nolock)" ).get();

SQL Server

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.

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

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.

QueryBuilder

query.fromRaw(
    "dbo.generateDateTable(?, ?, ?) as dt",
    [ "2017-01-01", "2017-12-31", "m" ]
).get();

SQL Server

SELECT * FROM dbo.generateDateTable(?, ?, ?) as dt

fromSub

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

QueryBuilder

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

MySQL

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

In additional a function callback, a separate QueryBuilder instance can be passed to the fromSub method.

QueryBuilder

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

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

MySQL

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

Joins

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

join

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

QueryBuilder

query.from( "users" )
    .join( "posts", "users.id", "=", "posts.author_id" );

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

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

QueryBuilder

query.from( "users" )
    .join( "posts", "users.id", "posts.author_id" );

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

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

QueryBuilder

query.from( "users" )
    .join( query.raw( "posts (nolock)" ), "users.id", "=", "posts.author_id" );

SQL Server

SELECT *
FROM [users]
JOIN posts (nolock)
  ON [users].[id] = [posts].[author_id]

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

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

QueryBuilder

query.from( "users" )
    .join( "posts", function( j ) {
        j.on( "users.id", "=", "posts.author_id" );
        j.on( "users.prefix", "=", "posts.prefix" );
    } );

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
  AND `users`.`prefix` = `posts`.`prefix`

You can specify where clauses in your joins as well.

QueryBuilder

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

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
  AND `posts`.`published_date` IS NOT NULL

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

QueryBuilder

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

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON (
      `users`.`id` = `posts`.`author_id`
      OR `users`.`id` = `posts`.`reviewer_id`
  )
  AND `posts`.`published_date` IS NOT NULL

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

QueryBuilder

var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" );

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

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

joinWhere

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

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

QueryBuilder

query.from( "users" )
    .joinWhere( "contacts", "contacts.balance", "<", 100 );

MySQL

SELECT *
FROM `users`
JOIN `contacts`
  WHERE `contacts`.`balance` < ?

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

joinRaw

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

QueryBuilder

query.from( "users" )
    .joinRaw( "posts (nolock)", "users.id", "posts.author_id" );

SQL Server

SELECT *
FROM [users]
JOIN posts (nolock)
  ON [users].[id] = [posts].[author_id]

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

joinSub

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

QueryBuilder

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

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

MySQL

SELECT *
FROM `users` AS `u`
JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

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

QueryBuilder

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

MySQL

SELECT *
FROM `users` AS `u`
JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

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

QueryBuilder

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

MySQL

SELECT *
FROM `users` AS `u`
JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`
  AND `u`.`type` = `c`.`type`

leftJoin

QueryBuilder

query.from( "posts" )
    .leftJoin( "users", "users.id", "posts.author_id" );

MySQL

SELECT *
FROM `posts`
LEFT JOIN `users`
  ON `users`.`id` = `posts`.`author_id`

leftJoinRaw

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

QueryBuilder

query.from( "posts" )
    .leftJoinRaw( "users (nolock)", "users.id", "posts.author_id" );

SQL Server

SELECT *
FROM [posts]
LEFT JOIN users (nolock)
  ON [users].[id] = [posts].[author_id]

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

leftJoinSub

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

QueryBuilder

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

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

MySQL

SELECT *
FROM `users` AS `u`
LEFT JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

rightJoin

QueryBuilder

query.from( "users" )
    .rightJoin( "posts", "users.id", "posts.author_id" );

MySQL

SELECT *
FROM `users`
RIGHT JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

rightJoinRaw

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

QueryBuilder

query.from( "users" )
    .rightJoinRaw( "posts (nolock)", "users.id", "posts.author_id" );

SQL Server

SELECT *
FROM [users]
LEFT JOIN posts (nolock)
  ON [users].[id] = [posts].[author_id]

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

rightJoinSub

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

QueryBuilder

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

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

MySQL

SELECT *
FROM `users` AS `u`
RIGHT JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

crossJoin

QueryBuilder

query.from( "users" ).crossJoin( "posts" );

MySQL

SELECT *
FROM `users`
CROSS JOIN `posts`

crossJoinRaw

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

QueryBuilder

query.from( "users" ).crossJoinRaw( "posts (nolock)" );

SQL Server

SELECT *
FROM [users]
CROSS JOIN posts (nolock)

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

crossJoinSub

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

QueryBuilder

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

query.from( "users as u" ).crossJoinSub( "c", sub );

MySQL

SELECT *
FROM `users` AS `u`
CROSS JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
)

newJoin

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

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

QueryBuilder

var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" );

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

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

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

QueryBuilder

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

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

MySQL

-- This is still an inner join because
-- the JoinClause is an inner join
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

JoinClause

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

on

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

QueryBuilder

var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" );

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

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

orOn

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

QueryBuilder

var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" )
    .orOn( "users.id", "posts.reviewer_id" );

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

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
  OR `users`.`id` = `posts`.`reviewer_id`

Preventing Duplicate Joins

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

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

moduleSettings = {
    "qb": {
         "preventDuplicateJoins": true  
    }
};

Wheres

Where Methods

where

Adds a where clause to a query.

QueryBuilder

query.from( "users" )
    .where( "active", "=", 1 );

MySQL

SELECT *
FROM `users`
WHERE `active` = ?

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

You can also pass an Expression as the value.

QueryBuilder

query.from( "users" )
    .where( "last_logged_in", ">", query.raw( "NOW()" ) );

MySQL

SELECT *
FROM `users`
WHERE `last_logged_in` > NOW()

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

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

QueryBuilder

query.from( "users" )
    .where( "active", 1 );

MySQL

SELECT *
FROM `users`
WHERE `active` = ?

You may also use dynamic where{Column} statements to simplify this further.

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

QueryBuilder

query.from( "users" )
    .where( function( q ) {
        q.where( "active", 1 )
            .where( "last_logged_in", ">", dateAdd( "ww", -1, now() ) )
    } );

MySQL

SELECT *
FROM `users`
WHERE (
    `active` = ?
    AND
    `last_logged_in` > ?
)

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

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

QueryBuilder

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

MySQL

SELECT *
FROM `users`
WHERE `email` = ?
  OR `id` = (
    SELECT MAX(id)
    FROM `users`
    WHERE `email` = ?
  )

andWhere

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

orWhere

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

whereBetween

Adds a where between clause to the query.

QueryBuilder

query.from( "users" )
    .whereBetween( "id", 1, 2 );

MySQL

SELECT *
FROM `users`
WHERE `id` BETWEEN ? AND ?

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

QueryBuilder

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

MySQL

SELECT *
FROM `users`
WHERE `id` BETWEEN (
    SELECT MIN(id)
    FROM `users`
    WHERE `email` = ?
)
AND (
    SELECT MAX(id)
    FROM `users`
    WHERE `email` = ?
)

whereNotBetween

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

whereColumn

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

QueryBuilder

query.from( "users" )
    .whereColumn( "first_name", "=", "last_name" );

MySQL

SELECT *
FROM `users`
WHERE `first_name` = `last_name`

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

QueryBuilder

query.from( "users" )
    .whereColumn( "first_name", "last_name" );

MySQL

SELECT *
FROM `users`
WHERE `first_name` = `last_name`

Expressions can be passed in place of either column.

QueryBuilder

query.from( "users" )
    .whereColumn( "first_name", query.raw( "LOWER(first_name)" ) );

MySQL

SELECT *
FROM `users`
WHERE `first_name` = LOWER(first_name)

whereExists

Adds a where exists clause to the query.

It can be configured with a function.

QueryBuilder

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

MySQL

SELECT *
FROM `orders`
WHERE EXISTS (
    SELECT 1
    FROM `products`
    WHERE `products`.`id` = `orders`.`id`
)

It can also be configured with a QueryBuilder instance.

QueryBuilder

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

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

MySQL

SELECT *
FROM `orders`
WHERE EXISTS (
    SELECT 1
    FROM `products`
    WHERE `products`.`id` = `orders`.`id`
)

whereNotExists

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

whereLike

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

QueryBuilder

query.from( "users" )
    .whereLike( "username", "J%" );

MySQL

SELECT *
FROM `users`
WHERE `username` LIKE ?

whereNotLike

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

QueryBuilder

query.from( "users" )
    .whereNotLike( "username", "J%" );

MySQL

SELECT *
FROM `users`
WHERE `username` NOT LIKE ?

whereIn

Adds a where in clause to the query.

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

QueryBuilder

query.from( "orders" )
    .whereIn( "id", [ 1, 4, 66 ] );

MySQL

SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)

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

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

QueryBuilder

query.from( "orders" )
    .whereIn( "id", "1,4,66" );

MySQL

SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)

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

QueryBuilder

query.from( "orders" )
    .whereIn( "id", [ 1, 4, { value = "66", cfsqltype = "CF_SQL_VARCHAR" } ] );

MySQL

SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)

Expressions can be freely mixed in with other values.

QueryBuilder

query.from( "orders" )
    .whereIn( "id", [ query.raw( "MAX(id)" ), 4, 66 ] );

MySQL

SELECT *
FROM `orders`
WHERE `id` IN (MAX(id), ?, ?)

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

QueryBuilder

query.from( "users" )
    .whereIn( "id", function( q ) {
        q.select( "id" )
            .from( "users" )
            .where( "age", ">", 25 );
    } );

MySQL

SELECT *
FROM `users`
WHERE IN (
    SELECT `id`
    FROM `users`
    WHERE `age` > ?
)

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

whereNotIn

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

whereRaw

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

QueryBuilder

query.from( "users" )
    .whereRaw(
        "id = ? OR email = ? OR is_admin = 1",
        [ 1, "foo" ]
    );

MySQL

SELECT *
FROM `users`
WHERE id = ? OR email = ? OR is_admin = 1

whereNull

Adds a where null clause to the query.

QueryBuilder

query.from( "users" )
    .whereNull( "id" );

MySQL

SELECT *
FROM `users`
WHERE `id` IS NULL

whereNotNull

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

Dynamic Where Methods

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

andWhere... and orWhere...

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

QueryBuilder

query.from( "users" )
    .where( "username", "like", "j%" )
    .andWhere( function( q ) {
        q.where( "isSubscribed", 1 )
            .orWhere( "isOnFreeTrial", 1 );
     } );

MySQL

SELECT *
FROM `users`
WHERE `username` LIKE ?
  AND (
    `isSubscribed` = ?
    OR
    `isOnFreeTrial` = ?
  )

where{Column}

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

QueryBuilder

query.from( "users" )
    .whereUsername( "like", "j%" )
    .whereActive( 1 );

MySQL

SELECT *
FROM `users`
WHERE `username` LIKE ?
  AND `active` = ?

Order By

The orderBy method seems simple but has a lot of depth depending on the type of arguments you pass in.

Calling orderBy multiple times appends to the order list.

Order By (String)

QueryBuilder

query.from( "users" )
    .orderBy( "email" );

MySQL

SELECT *
FROM `users`
ORDER BY `email` ASC

Calling orderBy multiple times will append to the order list.

QueryBuilder

query.from( "users" )
    .orderBy( "email" )
    .orderBy( "username", "desc" );

MySQL

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

You can also provide an Expression.

QueryBuilder

query.from( "users" )
    .orderBy( query.raw( "DATE(created_at)" ) );

MySQL

SELECT *
FROM `users`
ORDER BY DATE(created_at)

Order By (List)

QueryBuilder

query.from( "users" )
    .orderBy( "email|asc,username", "desc" );

MySQL

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Array of Strings)

QueryBuilder

query.from( "users" )
    .orderBy( [ "email|asc", "username" ], "desc" );

MySQL

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Array of Structs)

QueryBuilder

query.from( "users" )
    .orderBy( [
        { "column": "email", "direction": "asc" },
        "username"
    ], "desc" );

MySQL

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Subquery)

You can order with a subquery using either a function or a QueryBuilder instance.

QueryBuilder

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

MySQL

SELECT *
FROM `users`
ORDER BY (
    SELECT MAX(created_date)
    FROM `logins`
    WHERE `users`.`id` = `logins`.`user_id`
)

Order By Raw

QueryBuilder

query.from( "users" )
    .orderByRaw( "CASE WHEN status = ? THEN 1 ELSE 0 END DESC", [ 1 ] );

MySQL

SELECT *
FROM `users`
ORDER BY CASE WHEN status = ? THEN 1 ELSE 0 END DESC

clearOrders

Clears the currently configured orders for the query. Usually used by downstream libraries like Quick.

QueryBuilder

query.from( "users" )
    .orderBy( "email" )
    .clearOrders();

MySQL

SELECT *
FROM `users`

reorder

Clears the currently configured orders for the query and sets the new orders passed in. Any valid argument to orderBy can be passed here. Usually used by downstream libraries like Quick.

QueryBuilder

query.from( "users" )
    .orderBy( "email" )
    .reorder( "username" );

MySQL

SELECT *
FROM `users`
ORDER BY `username` ASC

Group By and Having

groupBy

Passing a single string will group by that one column.

QueryBuilder

query.from( "users" )
    .groupBy( "country" );

MySQL

SELECT *
FROM `users`
GROUP BY `country`

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

QueryBuilder

query.from( "users" )
    .groupBy( "country,city" );

MySQL

SELECT *
FROM `users`
GROUP BY `country`, `city`

An array of column names can be provided.

QueryBuilder

query.from( "users" )
    .groupBy( [ "country", "city" ] );

MySQL

SELECT *
FROM `users`
GROUP BY `country`, `city`

Calling groupBy multiple times will to the current groups.

QueryBuilder

query.from( "users" )
    .groupBy( "country" )
    .groupBy( "city" );

MySQL

SELECT *
FROM `users`
GROUP BY `country`, `city`

An Expression can be passed in place of a column.

QueryBuilder

query.from( "users" )
    .groupBy( query.raw( "DATE(created_at)" ) );

MySQL

SELECT *
FROM `users`
GROUP BY DATE(created_at)

having

Adds a having clause to a query.

QueryBuilder

query.from( "users" )
    .groupBy( "email" )
    .having( "email", ">", 1 );

MySQL

SELECT *
FROM `users`
GROUP BY `email`
HAVING `email` > ?

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

QueryBuilder

query.from( "users" )
    .groupBy( "email" )
    .having( query.raw( "COUNT(email)" ), ">", 1 );

MySQL

SELECT *
FROM `users`
GROUP BY `email`
HAVING COUNT(email) > ?

Limit, Offset, and Pagination

limit

Sets the limit value for the query.

QueryBuilder

query.from( "users" )
    .limit( 5 );

MySQL

SELECT *
FROM `users`
LIMIT 5

take

Sets the limit value for the query. Alias for limit.

QueryBuilder

query.from( "users" )
    .take( 5 );

MySQL

SELECT *
FROM `users`
LIMIT 5

offset

Sets the offset value for the query.

QueryBuilder

query.from( "users" )
    .offset( 25 );

MySQL

SELECT *
FROM `users`
OFFSET 25

forPage

Helper method to calculate the limit and offset given a page number and count per page.

QueryBuilder

query.from( "users" )
    .forPage( 3, 15 );

MySQL

SELECT *
FROM `users`
LIMIT 15
OFFSET 30

simplePaginate & paginate

This method combines forPage, count, and get to create a pagination struct alongside the results. Information on the simplePaginate or paginate methods, including custom pagination collectors, can be found in the Retreiving Results section of the documentation.

Locks

qb includes a few methods to help you lock certain rows when executing select statements.

Note: For locks to work properly, they must be nested inside a transaction. qb does not handle any of the transaction lifecycle for you.

sharedLock

A shared lock prevents the selected rows from being modified until your transaction is committed.

lockForUpdate

Name

Type

Required

Default

Description

A lock for update lock prevents the selected rows from being modified or selected with another shared lock until your transaction is committed.

The main difference between a sharedLock and lockForUpdate is that a lockForUpdate prevents other reads or selects as well as updates.

When using the skipLocked flag, the query will skip over locked records and only return and lock available records.

noLock

noLock will instruct your grammar to ignore any shared locks when executing the query.

Currently this only makes a difference in SQL Server grammars.

lock

The lock method will allow you to add a custom lock directive to your query. Think of it as the raw method for lock directives.

These lock directives vary from grammar to grammar.

clearLock

Clears any lock directive on the query.

Unions

The query builder also lets you create union statements on your queries using either UNION or UNION ALL strategies.

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.

union() — This method builds a SQL statement using the UNION clause which combines two SQL queries into a single result set 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.

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 OrderByNotAllowedexception. To order the results, add an orderBy() call to the parent source Query Builder instance.

union

Adds a UNION statement to the query.

Adding multiple union statements will append it to the query.

It can also add union queries as QueryBuilder instances.

unionAll

Adds a UNION ALL statement to the query.

Adding multiple unionAll statements will append it to the query.

It can also add union queries as QueryBuilder instances.

Common Table Expressions (i.e. CTEs)

Common Table Expressions (CTEs) are powerful SQL concept that allow you to create re-usable temporal result sets, which can be referenced as a table within your SQL. CTEs are available in many common database engines and are available in latest versions of all of the support grammars.

CTEs come in two basic types:

Non-recursive — These are statements that do not reference themselves, in simplified terms they are like a derived table that can be referenced by a user-defined name.
Recursive — Recursive CTEs reference themselves and are generally used for creating hierarchical data—such as creating a parent/child relationship within a table.

While all of the grammars currently support CTEs, there is enough difference between the various databases implementations of CTEs that unless your CTEs are fairly basic, using CTEs within your project will most likely tie your project to a specific database, unless you account for the differences in your code.

However, CTEs are can be extremely useful to solve certain use cases.

To add CTEs to your queries, you have two methods available:

with() — Allows you to define a non-recursive CTE.
withRecursive() — Allows you to define a recursive CTE.

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

with

You can build a CTE using a function:

QueryBuilder

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

MySQL

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

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

QueryBuilder

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

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

MySQL

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

A single query can reference multiple CTEs:

QueryBuilder

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

MySQL

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

withRecursive

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

Here is an example of building a recursive CTE using SQL Server which would return all parent/child rows and show their generation/level depth:

QueryBuilder

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

SqlServer

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

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

Raw Expressions

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

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

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

raw

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

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

When / Conditionals

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

QueryBuilder

var q = query.from( "posts" );
if ( someFlag ) {
    q.orderBy( "published_date", "desc" );
}

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

`when`

The when helper is used to allow conditional statements when defining queries without using if statements and having to store temporary variables.

QueryBuilder

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

You can pass a third argument to be called in the else case.

QueryBuilder

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

when callbacks are automatically scoped and grouped. That means that if a where clause is added inside the callback with an OR combinator the clauses will automatically be grouped (have parenthesis put around them.) You can disable this feature by passing withoutScoping = true to the when callback.

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

SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

Query Parameters and Bindings

Custom Parameter Types

When passing a parameter to qb, it will infer the sql type to be used. If you pass a number, CF_SQL_NUMERIC will be used. If it is a date, CF_SQL_TIMESTAMP, and so forth. If you need more control, you can pass a struct with the parameters you would pass to .

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

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

Strict Date Detection

By default, qb will try to determine if a variable is a date using the built-in isDate function. This can have some interesting effects with different formatted strings. You can opt in to stricter date detection which will check the underlying Java class of the value to determine if the value is a date. This is more accurate, but does require you to specifically pass date instances instead of strings. For this reason, it is currently opt-in to not break existing applications. It is likely to become the default in the next major version of qb.

You can opt in to stricter date detection by setting strictDateDetection = true in your moduleSettings in config/ColdBox.cfc.

Numeric SQL Type

By default, qb will use the CF_SQL_NUMERIC SQL type when it detects a numeric binding. You can specify your own default SQL type to use with numeric values using the numericSQLType setting in your moduleSettings in config/ColdBox.cfc.

There is an opt-in feature to better derive the numeric SQL type for database performance reasons. If you do opt in to this, qb will use a different SQL type for integers than decimals. You can opt in to this feature using the autoDeriveNumericType setting and can customize the SQL types by setting the integerSqlType and decimalSqlType settings.

Automatic Scale Detection

In some combinations of database grammars and CFML engines, the scale argument on a cfqueryparam would default to 0. This would cause issues when attempting to insert a floating point number, even when using the correct SQL type (i.e., CF_SQL_DECIMAL) . In 8.5.0, qb now automatically calculates a scale based on the value provided if the value is a floating point number. This can be disabled by setting autoAddScale in your ColdBox config or passing autoAddScale = false when instantiating your QueryBuilder instance.

Bindings

If you need to inspect the bindings for the current query you can retrieve them in order using the getBindings method.

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

getBindings

This method returns the current bindings in order to be used for the query.

You can also retrieve the bindings associated to their corresponding types.

getRawBindings

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

addBindings

Adds a single binding or an array of bindings to a query for a given type.

addBindingsFromBuilder

Adds all of the bindings from another builder instance.

Joins

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

join

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

QueryBuilder

query.from( "users" )
    .join( "posts", "users.id", "=", "posts.author_id" );

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

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

QueryBuilder

query.from( "users" )
    .join( "posts", "users.id", "posts.author_id" );

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

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

QueryBuilder

query.from( "users" )
    .join( query.raw( "posts (nolock)" ), "users.id", "=", "posts.author_id" );

SQL Server

SELECT *
FROM [users]
JOIN posts (nolock)
  ON [users].[id] = [posts].[author_id]

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

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

QueryBuilder

query.from( "users" )
    .join( "posts", function( j ) {
        j.on( "users.id", "=", "posts.author_id" );
        j.on( "users.prefix", "=", "posts.prefix" );
    } );

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
  AND `users`.`prefix` = `posts`.`prefix`

You can specify where clauses in your joins as well.

QueryBuilder

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

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
  AND `posts`.`published_date` IS NOT NULL

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

QueryBuilder

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

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON (
      `users`.`id` = `posts`.`author_id`
      OR `users`.`id` = `posts`.`reviewer_id`
  )
  AND `posts`.`published_date` IS NOT NULL

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

QueryBuilder

var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" );

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

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

joinWhere

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

QueryBuilder

query.from( "users" )
    .joinWhere( "contacts", "contacts.balance", "<", 100 );

MySQL

SELECT *
FROM `users`
JOIN `contacts`
  WHERE `contacts`.`balance` < ?

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

joinRaw

QueryBuilder

query.from( "users" )
    .joinRaw( "posts (nolock)", "users.id", "posts.author_id" );

SQL Server

SELECT *
FROM [users]
JOIN posts (nolock)
  ON [users].[id] = [posts].[author_id]

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

joinSub

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

QueryBuilder

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

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

MySQL

SELECT *
FROM `users` AS `u`
JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

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

QueryBuilder

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

MySQL

SELECT *
FROM `users` AS `u`
JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

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

QueryBuilder

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

MySQL

SELECT *
FROM `users` AS `u`
JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`
  AND `u`.`type` = `c`.`type`

leftJoin

QueryBuilder

query.from( "posts" )
    .leftJoin( "users", "users.id", "posts.author_id" );

MySQL

SELECT *
FROM `posts`
LEFT JOIN `users`
  ON `users`.`id` = `posts`.`author_id`

leftJoinRaw

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

QueryBuilder

query.from( "posts" )
    .leftJoinRaw( "users (nolock)", "users.id", "posts.author_id" );

SQL Server

SELECT *
FROM [posts]
LEFT JOIN users (nolock)
  ON [users].[id] = [posts].[author_id]

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

leftJoinSub

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

QueryBuilder

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

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

MySQL

SELECT *
FROM `users` AS `u`
LEFT JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

rightJoin

QueryBuilder

query.from( "users" )
    .rightJoin( "posts", "users.id", "posts.author_id" );

MySQL

SELECT *
FROM `users`
RIGHT JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

rightJoinRaw

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

QueryBuilder

query.from( "users" )
    .rightJoinRaw( "posts (nolock)", "users.id", "posts.author_id" );

SQL Server

SELECT *
FROM [users]
LEFT JOIN posts (nolock)
  ON [users].[id] = [posts].[author_id]

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

rightJoinSub

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

QueryBuilder

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

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

MySQL

SELECT *
FROM `users` AS `u`
RIGHT JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

crossJoin

QueryBuilder

query.from( "users" ).crossJoin( "posts" );

MySQL

SELECT *
FROM `users`
CROSS JOIN `posts`

crossJoinRaw

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

QueryBuilder

query.from( "users" ).crossJoinRaw( "posts (nolock)" );

SQL Server

SELECT *
FROM [users]
CROSS JOIN posts (nolock)

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

crossJoinSub

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

QueryBuilder

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

query.from( "users as u" ).crossJoinSub( "c", sub );

MySQL

SELECT *
FROM `users` AS `u`
CROSS JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
)

newJoin

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

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

QueryBuilder

var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" );

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

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

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

QueryBuilder

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

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

MySQL

-- This is still an inner join because
-- the JoinClause is an inner join
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

JoinClause

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

on

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

QueryBuilder

var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" );

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

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`

orOn

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

QueryBuilder

var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" )
    .orOn( "users.id", "posts.reviewer_id" );

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

MySQL

SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
  OR `users`.`id` = `posts`.`reviewer_id`

Preventing Duplicate Joins

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

moduleSettings = {
    "qb": {
         "preventDuplicateJoins": true  
    }
};

Wheres

Where Methods

where

Adds a where clause to a query.

QueryBuilder

query.from( "users" )
    .where( "active", "=", 1 );

MySQL

SELECT *
FROM `users`
WHERE `active` = ?

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

You can also pass an Expression as the value.

QueryBuilder

query.from( "users" )
    .where( "last_logged_in", ">", query.raw( "NOW()" ) );

MySQL

SELECT *
FROM `users`
WHERE `last_logged_in` > NOW()

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

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

QueryBuilder

query.from( "users" )
    .where( "active", 1 );

MySQL

SELECT *
FROM `users`
WHERE `active` = ?

You may also use dynamic where{Column} statements to simplify this further.

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

QueryBuilder

query.from( "users" )
    .where( function( q ) {
        q.where( "active", 1 )
            .where( "last_logged_in", ">", dateAdd( "ww", -1, now() ) )
    } );

MySQL

SELECT *
FROM `users`
WHERE (
    `active` = ?
    AND
    `last_logged_in` > ?
)

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

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

QueryBuilder

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

MySQL

SELECT *
FROM `users`
WHERE `email` = ?
  OR `id` = (
    SELECT MAX(id)
    FROM `users`
    WHERE `email` = ?
  )

andWhere

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

orWhere

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

whereBetween

Adds a where between clause to the query.

QueryBuilder

query.from( "users" )
    .whereBetween( "id", 1, 2 );

MySQL

SELECT *
FROM `users`
WHERE `id` BETWEEN ? AND ?

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

QueryBuilder

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

MySQL

SELECT *
FROM `users`
WHERE `id` BETWEEN (
    SELECT MIN(id)
    FROM `users`
    WHERE `email` = ?
)
AND (
    SELECT MAX(id)
    FROM `users`
    WHERE `email` = ?
)

whereNotBetween

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

whereColumn

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

QueryBuilder

query.from( "users" )
    .whereColumn( "first_name", "=", "last_name" );

MySQL

SELECT *
FROM `users`
WHERE `first_name` = `last_name`

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

QueryBuilder

query.from( "users" )
    .whereColumn( "first_name", "last_name" );

MySQL

SELECT *
FROM `users`
WHERE `first_name` = `last_name`

Expressions can be passed in place of either column.

QueryBuilder

query.from( "users" )
    .whereColumn( "first_name", query.raw( "LOWER(first_name)" ) );

MySQL

SELECT *
FROM `users`
WHERE `first_name` = LOWER(first_name)

whereExists

Adds a where exists clause to the query.

It can be configured with a function.

QueryBuilder

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

MySQL

SELECT *
FROM `orders`
WHERE EXISTS (
    SELECT 1
    FROM `products`
    WHERE `products`.`id` = `orders`.`id`
)

It can also be configured with a QueryBuilder instance.

QueryBuilder

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

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

MySQL

SELECT *
FROM `orders`
WHERE EXISTS (
    SELECT 1
    FROM `products`
    WHERE `products`.`id` = `orders`.`id`
)

whereNotExists

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

whereLike

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

QueryBuilder

query.from( "users" )
    .whereLike( "username", "J%" );

MySQL

SELECT *
FROM `users`
WHERE `username` LIKE ?

whereNotLike

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

QueryBuilder

query.from( "users" )
    .whereNotLike( "username", "J%" );

MySQL

SELECT *
FROM `users`
WHERE `username` NOT LIKE ?

whereIn

Adds a where in clause to the query.

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

QueryBuilder

query.from( "orders" )
    .whereIn( "id", [ 1, 4, 66 ] );

MySQL

SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)

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

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

QueryBuilder

query.from( "orders" )
    .whereIn( "id", "1,4,66" );

MySQL

SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)

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

QueryBuilder

query.from( "orders" )
    .whereIn( "id", [ 1, 4, { value = "66", cfsqltype = "CF_SQL_VARCHAR" } ] );

MySQL

SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)

Expressions can be freely mixed in with other values.

QueryBuilder

query.from( "orders" )
    .whereIn( "id", [ query.raw( "MAX(id)" ), 4, 66 ] );

MySQL

SELECT *
FROM `orders`
WHERE `id` IN (MAX(id), ?, ?)

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

QueryBuilder

query.from( "users" )
    .whereIn( "id", function( q ) {
        q.select( "id" )
            .from( "users" )
            .where( "age", ">", 25 );
    } );

MySQL

SELECT *
FROM `users`
WHERE IN (
    SELECT `id`
    FROM `users`
    WHERE `age` > ?
)

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

whereNotIn

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

whereRaw

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

QueryBuilder

query.from( "users" )
    .whereRaw(
        "id = ? OR email = ? OR is_admin = 1",
        [ 1, "foo" ]
    );

MySQL

SELECT *
FROM `users`
WHERE id = ? OR email = ? OR is_admin = 1

whereNull

Adds a where null clause to the query.

QueryBuilder

query.from( "users" )
    .whereNull( "id" );

MySQL

SELECT *
FROM `users`
WHERE `id` IS NULL

whereNotNull

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

Dynamic Where Methods

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

andWhere... and orWhere...

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

QueryBuilder

query.from( "users" )
    .where( "username", "like", "j%" )
    .andWhere( function( q ) {
        q.where( "isSubscribed", 1 )
            .orWhere( "isOnFreeTrial", 1 );
     } );

MySQL

SELECT *
FROM `users`
WHERE `username` LIKE ?
  AND (
    `isSubscribed` = ?
    OR
    `isOnFreeTrial` = ?
  )

where{Column}

QueryBuilder

query.from( "users" )
    .whereUsername( "like", "j%" )
    .whereActive( 1 );

MySQL

SELECT *
FROM `users`
WHERE `username` LIKE ?
  AND `active` = ?