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:
When doing a simple join using =
as the operator, you can omit it and pass just the column names:
``Expressions
are also supported as the table
argument (though you may prefer the readability of the joinRaw
method):
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:
You can specify where
clauses in your joins as well.
Conditions inside a join clause can be grouped using a function.
A preconfigured JoinClause
can also be passed to the join function. This allows you to extract shared pieces of code out to different functions.
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:
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.
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:
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
Uses the raw SQL provided to as the table for the left join clause. All the other functionality of leftJoinRaw
matches the join
method.
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.
rightJoin
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.
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.
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.
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.
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.
Although a JoinClause
can be passed to join
, leftJoin
, rightJoin
, and crossJoin
, the type of the JoinClause
will override the type of the function.
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
.
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
.