1 of 1

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.

Table of Contents

join

Name

Type

Required

Default

Description

table

true

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

type

string

false

"inner"

where

boolean

false

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

Name

Type

Required

Default

Description

table

string

true

The raw SQL string to use as the table.

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

type

string

false

"inner"

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

Name

Type

Required

Default

Description

table

string

true

The raw SQL string to use as the table.

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

type

string

false

"inner"

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

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

Name

Type

Required

Default

Description

alias

string

true

The alias for the derived table.

input

Function | QueryBuilder

true

Either a QueryBuilder instance or a function to define the derived query.

first

true

operator

string

false

"="

The boolean operator for the join clause.

second

false

type

string

false

"inner"

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

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

Name

Type

Required

Default

Description

table

true

first

string | Expression | Function

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

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

Name

Type

Required

Default

Description

table

string

true

The raw SQL string to use as the table.

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

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

Name

Type

Required

Default

Description

alias

string

true

The alias for the derived table.

input

Function | QueryBuilder

true

Either a QueryBuilder instance or a function to define the derived query.

first

true

operator

string

false

"="

The boolean operator for the join clause.

second

false

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

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

Name

Type

Required

Default

Description

table

true

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

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

Name

Type

Required

Default

Description

table

string

true

The raw SQL string to use as the table.

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

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

Name

Type

Required

Default

Description

alias

string

true

The alias for the derived table.

input

Function | QueryBuilder

true

Either a QueryBuilder instance or a function to define the derived query.

first

true

operator

string

false

"="

The boolean operator for the join clause.

second

false

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

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

Name

Type

Required

Default

Description

table

true

QueryBuilder

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

MySQL

SELECT *
FROM `users`
CROSS JOIN `posts`

crossJoinRaw

Name

Type

Required

Default

Description

table

string

true

The raw SQL string to use as the table.

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

Name

Type

Required

Default

Description

alias

string

true

The alias for the derived table.

input

Function | QueryBuilder

true

Either a QueryBuilder instance or a function to define the derived query.

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 (?, ?, ?)
)

crossApply

Name

Type

Required

Default

Description

name

string

true

The name for the cross apply table

tableDef

function | QueryBuilder

true

A QueryBuilder instance or a function that accepts a new query builder instance to configure.

Adds a cross apply join using a derived table. The derived table can be defined using a QueryBuilder instance or a function just as with joinSub.

QueryBuilder

qb.from( "users as u" )
    .select( [ "u.ID", "childCount.c" ] )
    .crossApply( "childCount", function( qb ) {
        qb.selectRaw( "count(*) c" )
            .from( "children" )
            .whereColumn( "children.parentID", "=", "users.ID" )
            .where( "children.someCol", "=", 0 );
    } )
    .where( "childCount.c", ">", 1 )

SQL Server

SELECT
    [u].[ID],
    [childCount].[c]
FROM [users] AS [u]
CROSS APPLY (
    SELECT count(*) c
    FROM [children]
    WHERE [children].[parentID] = [users].[ID]
    AND [children].[someCol] = ?
) AS [childCount]
WHERE [childCount].[c] > ?

outerApply

Name

Type

Required

Default

Description

name

string

true

The name for the cross apply table

tableDef

function | QueryBuilder

true

A QueryBuilder instance or a function that accepts a new query builder instance to configure.

Adds a outer apply join using a derived table. The derived table can be defined using a QueryBuilder instance or a function just as with joinSub.

QueryBuilder

qb.from( "users as u" )
    .select( [ "u.ID", "childCount.c" ] )
    .outerApply( "childCount", function( qb ) {
        qb.selectRaw( "count(*) c" )
            .from( "children" )
            .whereColumn( "children.parentID", "=", "users.ID" )
            .where( "children.someCol", "=", 0 );
    } )
    .where( "childCount.c", ">", 1 )

SQL Server

SELECT
    [u].[ID],
    [childCount].[c]
FROM [users] AS [u]
OUTER APPLY (
    SELECT count(*) c
    FROM [children]
    WHERE [children].[parentID] = [users].[ID]
    AND [children].[someCol] = ?
) AS [childCount]
WHERE [childCount].[c] > ?

newJoin

Name

Type

Required

Default

Description

table

true

type

string

false

"inner"

The type of the join. Valid types are inner, left, right, or cross.

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

Name

Type

Required

Default

Description

first

false

operator

string

false

"="

The boolean operator for the condition.

second

false

combinator

string

false

"and"

The boolean combinator for the clause (e.g. "and" or "or").

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

Name

Type

Required

Default

Description

first

false

operator

string

false

"="

The boolean operator for the condition.

second

false

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

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.

Table of Contents

join

Name

Type

Required

Default

Description

table

string | |

true

The name of the table or a object from which the query is based. Alternatively, a configured instance can be passed.

first

string | | Function

false

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

operator

string

false

"="

The boolean operator for the join clause.

second

string |

false

The second column or to join the table on.

type

string

false

"inner"

The type of the join. Passing this as an argument is discouraged for readability. Use the dedicated methods like and where possible.

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use the dedicated or a join closure where possible.

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

Name

Type

Required

Default

Description

table

string

true

The raw SQL string to use as the table.

first

string | | Function

false

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

operator

string

false

"="

The boolean operator for the join clause.

second

string |

false

The second column or to join the table on.

type

string

false

"inner"

The type of the join. Passing this as an argument is discouraged for readability. Use the dedicated methods like and with a join function where possible.

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

Name

Type

Required

Default

Description

table

string

true

The raw SQL string to use as the table.

first

string | | Function

false

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

operator

string

false

"="

The boolean operator for the join clause.

second

string |

false

The second column or to join the table on.

type

string

false

"inner"

The type of the join. Passing this as an argument is discouraged for readability. Use the dedicated methods like and where possible.

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

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

Name

Type

Required

Default

Description

alias

string

true

The alias for the derived table.

input

Function | QueryBuilder

true

Either a QueryBuilder instance or a function to define the derived query.

first

string | | Function

true

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

operator

string

false

"="

The boolean operator for the join clause.

second

string |

false

The second column or to join the table on.

type

string

false

"inner"

The type of the join. Passing this as an argument is discouraged for readability. Use the dedicated methods like and where possible.

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

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

Name

Type

Required

Default

Description

table

string | |

true

The name of the table or a object from which the query is based. Alternatively, a configured instance can be passed.

(Note: a instance may have a different join type than a left join. The instance's join type will be used.)

first

string | Expression | Function

false

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

operator

string

false

"="

The boolean operator for the join clause.

second

string |

false

The second column or to join the table on.

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

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

Name

Type

Required

Default

Description

table

string

true

The raw SQL string to use as the table.

first

string | | Function

false

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

operator

string

false

"="

The boolean operator for the join clause.

second

string |

false

The second column or to join the table on.

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

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

Name

Type

Required

Default

Description

alias

string

true

The alias for the derived table.

input

Function | QueryBuilder

true

Either a QueryBuilder instance or a function to define the derived query.

first

string | | Function

true

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

operator

string

false

"="

The boolean operator for the join clause.

second

string |

false

The second column or to join the table on.

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

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

Name

Type

Required

Default

Description

table

string | |

true

The name of the table or a object from which the query is based. Alternatively, a configured instance can be passed.

(Note: a instance may have a different join type than a right join. The instance's join type will be used.)

first

string | | Function

false

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

operator

string

false

"="

The boolean operator for the join clause.

second

string |

false

The second column or to join the table on.

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

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

Name

Type

Required

Default

Description

table

string

true

The raw SQL string to use as the table.

first

string | | Function

false

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

operator

string

false

"="

The boolean operator for the join clause.

second

string |

false

The second column or to join the table on.

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

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

Name

Type

Required

Default

Description

alias

string

true

The alias for the derived table.

input

Function | QueryBuilder

true

Either a QueryBuilder instance or a function to define the derived query.

first

string | | Function

true

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

operator

string

false

"="

The boolean operator for the join clause.

second

string |

false

The second column or to join the table on.

where

boolean

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

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

Name

Type

Required

Default

Description

table

string | |

true

The name of the table or a object from which the query is based. Alternatively, a configured instance can be passed.

(Note: a instance may have a different join type than a cross join. The instance's join type will be used.)

QueryBuilder

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

MySQL

SELECT *
FROM `users`
CROSS JOIN `posts`

crossJoinRaw

Name

Type

Required

Default

Description

table

string

true

The raw SQL string to use as the table.

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

Name

Type

Required

Default

Description

alias

string

true

The alias for the derived table.

input

Function | QueryBuilder

true

Either a QueryBuilder instance or a function to define the derived query.

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 (?, ?, ?)
)

crossApply

Name

Type

Required

Default

Description

name

string

true

The name for the cross apply table

tableDef

function | QueryBuilder

true

A QueryBuilder instance or a function that accepts a new query builder instance to configure.

Adds a cross apply join using a derived table. The derived table can be defined using a QueryBuilder instance or a function just as with joinSub.

QueryBuilder

qb.from( "users as u" )
    .select( [ "u.ID", "childCount.c" ] )
    .crossApply( "childCount", function( qb ) {
        qb.selectRaw( "count(*) c" )
            .from( "children" )
            .whereColumn( "children.parentID", "=", "users.ID" )
            .where( "children.someCol", "=", 0 );
    } )
    .where( "childCount.c", ">", 1 )

SQL Server

SELECT
    [u].[ID],
    [childCount].[c]
FROM [users] AS [u]
CROSS APPLY (
    SELECT count(*) c
    FROM [children]
    WHERE [children].[parentID] = [users].[ID]
    AND [children].[someCol] = ?
) AS [childCount]
WHERE [childCount].[c] > ?

outerApply

Name

Type

Required

Default

Description

name

string

true

The name for the cross apply table

tableDef

function | QueryBuilder

true

A QueryBuilder instance or a function that accepts a new query builder instance to configure.

Adds a outer apply join using a derived table. The derived table can be defined using a QueryBuilder instance or a function just as with joinSub.

QueryBuilder

qb.from( "users as u" )
    .select( [ "u.ID", "childCount.c" ] )
    .outerApply( "childCount", function( qb ) {
        qb.selectRaw( "count(*) c" )
            .from( "children" )
            .whereColumn( "children.parentID", "=", "users.ID" )
            .where( "children.someCol", "=", 0 );
    } )
    .where( "childCount.c", ">", 1 )

SQL Server

SELECT
    [u].[ID],
    [childCount].[c]
FROM [users] AS [u]
OUTER APPLY (
    SELECT count(*) c
    FROM [children]
    WHERE [children].[parentID] = [users].[ID]
    AND [children].[someCol] = ?
) AS [childCount]
WHERE [childCount].[c] > ?

newJoin

Name

Type

Required

Default

Description

table

string |

true

The name of the table or a object from which the query is based.

type

string

false

"inner"

The type of the join. Valid types are inner, left, right, or cross.

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

Name

Type

Required

Default

Description

first

string | | Function

false

The first column or of the condition. Alternatively, a function can be passed to nest conditions with parenthesis.

operator

string

false

"="

The boolean operator for the condition.

second

string |

false

The second column or of the condition.

combinator

string

false

"and"

The boolean combinator for the clause (e.g. "and" or "or").

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

Name

Type

Required

Default

Description

first

string | | Function

false

The first column or of the condition. Alternatively, a function can be passed to nest conditions with parenthesis.

operator

string

false

"="

The boolean operator for the condition.

second

string |

false

The second column or of the condition.

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