from

Used to set the base table for the query.

query.from( "users" );

SELECT * FROM `users`

You can optionally specify an alias for the table.

query.from( "users as u" );

SELECT * FROM `users` AS `u`

table

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

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

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.

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

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.

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.

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

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.

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

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.

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

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

groupBy

Passing a single string will group by that one column.

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

SELECT *
FROM `users`
GROUP BY `country`

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

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

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

An array of column names can be provided.

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

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

Calling groupBy multiple times will to the current groups.

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

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

An Expression can be passed in place of a column.

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

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

having

Adds a having clause to a query.

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

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

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

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

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

limit

Sets the limit value for the query.

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

SELECT *
FROM `users`
LIMIT 5

take

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

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

SELECT *
FROM `users`
LIMIT 5

offset

Sets the offset value for the query.

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

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

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.

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 "*".

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

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

distinct

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

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

SELECT DISTINCT `username` FROM `users`

addSelect

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

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

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

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

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.

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

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.

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

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.

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

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.

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

SELECT YEAR(birthdate) AS birth_year FROM `users`

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

Order By (String)

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

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

Calling orderBy multiple times will append to the order list.

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

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

You can also provide an Expression.

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

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

Order By (List)

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

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

Order By (Array of Strings)

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

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

Order By (Array of Structs)

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

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.

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

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

Order By Raw

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

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.

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

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.

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

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

Where Methods

where

Adds a where clause to a query.

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

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

You can also pass an Expression as the value.

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

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.

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

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

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

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

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

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

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

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.

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

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

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

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.

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

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.

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

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

Expressions can be passed in place of either column.

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

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.

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

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

It can also be configured with a QueryBuilder instance.

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

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

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.

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

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

whereNotLike

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

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

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.

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

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

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

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

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.

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

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

Expressions can be freely mixed in with other values.

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

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.

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

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

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.

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

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

whereNull

Adds a where null clause to the query.

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

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.

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

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.

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

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

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:

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

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

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

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:

joinRaw

joinSub

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

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

leftJoin

leftJoinRaw

leftJoinSub

rightJoin

rightJoinRaw

rightJoinSub

crossJoin

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.

crossJoinSub

newJoin

JoinClause

on

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

orOn

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

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.