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:
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:
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" );
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" );
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" );
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 );
SELECT [u].[ID], [childCount].[c]FROM [users] AS [u]OUTERAPPLY (SELECTcount(*) cFROM [children]WHERE [children].[parentID] = [users].[ID]AND [children].[someCol] = ?) AS [childCount]WHERE [childCount].[c] > ?
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.
// This is still an inner join because// the JoinClause is an inner joinvar 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 joinSELECT*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.
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.