Wheres

Where Methods

where

Adds a where clause to a query.

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

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

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

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.

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

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

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

whereNotBetween

Adds a where not in clause to the query. This behaves identically to the whereBetween method with the negate`flag set to true.

whereColumn

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

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

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

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

whereExists

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

whereNotExists

Adds a where not in clause to the query. This behaves identically to the whereExists method with the negate`flag set to true.

whereLike

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

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

whereIn

Adds a where in clause to the query.

Pass single value, a list of values, or an array of values.

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

whereNotIn

Adds a where not in clause to the query.

whereRaw

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

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

whereNull

Adds a where null clause to the query.

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

whereNotNull

Adds a where not in clause to the query.

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.

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

where{Column}

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

When / Conditionals

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

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.

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

Selects

select

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

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

distinct

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

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

addSelect

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

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

selectRaw

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

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

subSelect

The subselect is added to the other already selected columns.

.subSelect( "last_login_date", ( q ) => {
  q.selectRaw( "MAX(created_date)" ).from( "logins" )
} ) ).from( "users" )

clearSelect

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

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

reselect

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

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

reselectRaw

Clears out the selected columns.

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

Group By and Having

groupBy

Passing a single string will group by that one column.

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

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

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

# Calling groupBy multiple times will to the current groups.
query.from( "users" )
    .groupBy( "country" )
    .groupBy( "city" );

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

having

Adds a having clause to a query.

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

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

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

Limit, Offset, and Pagination

limit

Sets the limit value for the query.

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

take

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

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

offset

Sets the offset value for the query.

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

forPage

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

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

simplePaginate & paginate

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

From

from

Used to set the base table for the query.

query.from( "users" );

table

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

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

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.

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

fromSub

Complex queries often contain derived tables.

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

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)

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

Calling orderBy multiple times will append to the order list.

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

You can also provide an Expression.

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

Order By (List)

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

Order By (Array of Strings)

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

Order By (Array of Structs)

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

Order By (Subquery)

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

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

Order By Raw

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

clearOrders

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

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

reorder

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

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

Common Table Expressions (i.e. CTEs)

Common Table Expressions (CTEs) allow you to create re-usable temporal result sets.

with

You can build a CTE using a function:

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

withRecursive

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

Unions

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

union

Adds a UNION statement to the query.

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .union( function ( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 2 );
    } );

unionAll

Adds a UNION ALL statement to the query.

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .unionAll( function( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 2 );
     } );

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:

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

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

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

joinWhere

Adds a join to another table based on a WHERE clause instead of an ON clause.

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

For complex joins, a function can be passed to first.

joinRaw

Uses the raw SQL provided to as the table for the join clause.

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

Using joinRaw will most likely tie your code to a specific database

joinSub

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

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

leftJoin

query.from( "posts" )
    .leftJoin( "users", "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.

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

leftJoinSub

Adds a left join to a derived table.

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

rightJoin

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

rightJoinRaw

Uses the raw SQL provided to as the table for the right join clause.

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

Using rightJoinRaw will most likely tie your code to a specific database.

rightJoinSub

Adds a right join to a derived table.

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

crossJoin

query.from( "users" ).crossJoin( "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.

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

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.

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

newJoin

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

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

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.

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

orOn

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

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

Preventing Duplicate Joins

You can optionally configure qb to ignore duplicate joins.

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

Retrieving Results

get

The get method is the most common method used for retrieving results.

query.from( "users" ).get();

`get can also take a list or array of columns to use as a shortcut.

query.from( "users" ).get( [ "id", "name" ] );

first

If you just need to retrieve a single row from the database table, you may use the first method.

query.from( "users" ).first();

values

If you don't even need an entire row, you may extract a single value from each record using the values method.

query.from( "users" ).values( "firstName" );

[ "jon", "jane", "jill", ...
]

value

This method is similar to values except it only returns a single, simple value.

query.from( "users" ).value( "firstName" );

"jon"

If no records are returned from the query, one of two things will happen.

chunk

Large datasets can be broken up and retrieved in chunks.

query.from( "users" ).chunk( 100, function( users ) {
    // Process the users here
    // Returning false from the callback stops processing
} );

paginate

Generates a pagination struct along with the results of the executed query.

query.from( "users" )
    .paginate();

{
    "pagination": {
        "maxRows": 25,
        "offset": 0,
        "page": 1,
        "totalPages": 2,
        "totalRecords": 45
    },
    "results": [ { /* ... */ }, ]
}

simplePaginate

Generates a simple pagination struct along with the results of the executed query.

query.from( "users" )
    .simplePaginate();

{
    "pagination": {
        "maxRows": 25,
        "offset": 0,
        "page": 1,
        "hasMore": true
    },
    "results": [ { /* ... */ }, ]
}

Custom Pagination Collectors

A pagination collector is the name given to the struct returned from calling the paginate method.

generateWithResults

You can set your custom pagination collector either in the constructor using the paginationCollector argument or by calling setPaginationCollector on a query builder instance.

Inserts, Updates, and Deletes

insert

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

You can insert a single record by passing a struct:

query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "robert@test.com",
        "age" = 55
    } );

You can specify any query param.

query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "robert@test.com",
        "age" = { value = 55, cfsqltype = "CF_SQL_INTEGER" }
    } );

Raw values can be supplied to an insert statement.

query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "robert@test.com",
        "updatedDate" = query.raw( "NOW()" )
    } );

Multiple rows can be inserted in a batch by passing an array of structs to insert.

query.from( "users" ).insert( [
    { "email" = "john@example.com", "name" = "John Doe" },
    { "email" = "jane@example.com", "name" = "Jane Doe" }
] );

returning

Returning is only supported in PostgresGrammar and SqlServerGrammar.

Specifies columns to be returned from the insert query.

query.from( "users" )
    .returning( "id" )
    .insert( {
        "email" = "foo",
        "name" = "bar"
    } );

update

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

Updates a table with a struct of column and value pairs.

query.from( "users" )
    .update( {
        "email" = "foo",
        "name" = "bar"
    } );

You can specify any query param

query.from( "users" )
    .update( {
        "email" = "foo",
        "name" = "bar",
        "updatedDate" = { value = now(), cfsqltype = "CF_SQL_TIMESTAMP" }
    } );

Any constraining of the update query should be done using the appropriate WHERE statement before calling update.

query.from( "users" )
    .whereId( 1 )
    .update( {
        "email" = "foo",
        "name" = "bar"
    } );

You can update a column based on another column using a raw expression.

query.from( "hits" )
    .where( "page", "someUrl" )
    .update( {
        "count" = query.raw( "count + 1" )
    } );

Updating Null values

Null values can be inserted by using queryparam syntax:

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

addUpdate

Adds values to a later update, similar to addSelect.

query.from( "users" )
    .whereId( 1 )
    .addUpdate( {
        "email" = "foo",
        "name" = "bar"
    } )
    .when( true, function( q ) {
        q.addUpdate( {
            "foo": "yes"
        } );
    } )
    .when( false, function( q ) {
        q.addUpdate( {
            "bar": "no"
        } );
    } )
    .update();

updateOrInsert

Performs an update statement if the configured query returns true for exists. Otherwise, performs an insert statement.

query.from( "users" )
    .where( "email", "foo" )
    .updateOrInsert( {
        "email" = "foo",
        "name" = "baz"
    } );

If the configured query returns 0 records, then an insert statement is performed.

query.from( "users" )
    .where( "email", "foo" )
    .updateOrInsert( {
        "email" = "foo",
        "name" = "baz"
    } );

delete

Deletes all records that the query returns.

query.from( "users" )
    .where( "email", "foo" )
    .delete();

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

query.from( "users" )
    .delete( 1 );

Aggregates

The query builder also provides a variety of aggregate methods such as count, max, min, and sum.

exists

Returns true if the query returns any rows. Returns false otherwise.

query.from( "users" ).where( "username", "like", "jon%" ).exists();

count

Returns an integer number of rows returned by the query.

query.from( "users" ).count();

max

Returns the maximum value for the given column.

query.from( "users" ).max( "age" );

min

Returns the minimum value for the given column.

query.from( "users" ).min( "age" );

sum

Returns the sum of all returned rows for the given column.

query.from( "employees" ).sum( "salary" );

Query Parameters and Bindings

Custom Parameter Types

When passing a parameter to qb, it will infer the sql type to be used. You can pass a struct with the parameters you would pass to cfqueryparam.

query.from( "users" )
    .where( "id", "=", { value = 18, cfsqltype = "CF_SQL_VARCHAR" } );

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

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

Strict Date Detection

By default, qb will try to determine if a variable is a date using the built-in isDate function.

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

Numeric SQL Type

By default, qb will use the CF_SQL_NUMERIC SQL type when it detects a numeric binding.

moduleSettings = {
    "qb": {
        "numericSQLType": "CF_SQL_INTEGER"
    }
};

Bindings

Bindings are the values that will be sent as parameters to a prepared SQL statement.

getBindings

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

query.from( "users" )
    .join( "logins", function( j ) {
        j.on( "users.id", "logins.user_id" );
        j.where( "logins.created_date", ">", dateAdd( "m", -1, "01 Jun 2019" ) );
    } )
    .where( "active", 1 );

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.

query.from( "users" )
    .join( "logins", function( j ) {
        j.on( "users.id", "logins.user_id" );
        j.where( "logins.created_date", ">", dateAdd( "m", -1, "01 Jun 2019" ) );
    } )
    .where( "active", 1 );

Raw Expressions

Raw expressions are the qb escape hatch.

raw

The sql snippet passed to raw is not processed by qb at all.

query.from( "users" ).select( query.raw( "MAX(created_date)" ) );