Join clauses range from simple to complex including joining complete subqueries on multiple conditions. qb has your back with all of these use cases.
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:
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:
Conditions inside a join clause can be grouped using a function.
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:
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.
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:
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.
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.
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.
Applies a join condition to the JoinClause. An alias for whereColumn.
Applies a join condition to the JoinClause using an or combinator. An alias for orWhereColumn.
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.