Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Add a firstOrFail
fetch method inspired by Quick.
There are now specific numeric SQL types for integers and decimals used during the inferSQLType
check in QueryUtils
. This is an opt-in feature, enabled by setting the autoDeriveNumericType
setting. The previous approach was to use CF_SQL_NUMERIC
for all numeric types which could cause performance issues in some grammars as they interpreted all CF_SQL_NUMERIC
as floating point numbers.
HOLDLOCK
and READPAST
are mutually exclusive table locks in SQL Server but were mistakenly being applied together.
Specify defaultOptions
inside of your ColdBox config.
Better parsing of raw
statements when deriving insertUsing
columns.
Insert data based off of a callback or builder using insertUsing
.
Insert data ignoring duplicate key errors using insertIgnore
.
Use a callback or builder as the source for an upsert
statement.
Allow for deleting unmatched source records in upserts (SQL Server only).
Add a new skipLocked
flag to lockForUpdate
.
Don't uppercase quoted aliases in Oracle.
Fix for aliases in update statements.
Don't sort columns for insertUsing
.
Add subquery bindings in insert and upsert statements.
Maintain column order when using source in upsert.
Fix for Oracle returning custom column types when renaming a column.
Explicit arguments scoping.
arrayEach
is slow compared to merging arrays.
Fix wheres with joins in update statements.
Add better null handling to inferSqlType
.
Correctly format columns being updated.
Add an upsert method. upsert
can update or insert multiple records at once depending on if a column is matched.
Allow JOIN
statements in UPDATE
statements. (This is not supported on Oracle.)
Allow updates with subselects using closures or builder instances.
Better handling of group by
and having
clauses in pagination queries.
Allow any value to be returned from aggregates including strings, numbers, and dates.
Correctly wrap CTE expressions with parenthesis when required in certain grammars.
SchemaBuilder
can now be configured with default query options. (Default options will still be overridden by options passed to each SchemaBuilder
method.)
Add a reset
method to QueryBuilder.
Add locking helpers such as lock
, noLock
, lockForUpdate
, and sharedLock
.
Correct return aggregate values for date values from max
and min
executors.
Automatically add a scale
to an incoming query param when needed.
Add a whereNotLike
shortcut method.
Correctly format a COUNT(DISTINCT column)
query.
Only use bulk insert syntax when needed in OracleGrammar due to interactions between the result
parameter to cfquery
, Lucee, and the Oracle JDBC driver.
Add support for stored computed columns and virtual computed columns.
Swap master
branch to main
branch.
Remove unnecessary injection for QueryUtils.
Account for raw expressions when generating mementos for comparison
Add support for mediumtext & longtext types for MySQLGrammar.
Fix limit on simplePaginate.
Migrate release process to GitHub Actions.
Add a simplePaginate
pagination method for quicker performance when total records or total pages are not needed or too slow.
Introduce a numericSQLType
setting to specify the default numeric SQL type.
Default to html
for the dump
format argument to writeDump
.
Correctly use the passed in strictDateDetection
to the QueryUtils.cfc
.
Added a dump
command to aid in debugging a query while chaining.
``orderByRaw
now can accept bindings.
A new, optional strictDateDetection
setting is available to check the underlying Java class of a date object instead of using isDate
.
Ignore select bindings for aggregate queries.
Allow spaces in table aliases.
Split FLOAT and DECIMAL column types in SQL Server.
Clear orderBy bindings when calling clearOrders
.
Trim table definitions before searching for aliases. Makes qb more lenient with extra whitespace.
``when
callbacks now automatically scope and group where clauses when an OR
combinator is used.
Combine clearOrders
and orderBy
with a new reorder
method.
Clear current selected columns with clearSelect
.
Combine clearSelect
and either select
or selectRaw
with reselect
and reselectRaw
respectively.
Expose nested where functions to enable advanced query manipulation in downstream libraries like Quick.
Fixes for OracleGrammar including table aliases and wrapped subqueries.
Allow nullable timestamps in MySQL.
Return 0 on null aggregates.
Match type hints to documentation for join functions
Handle enhanced numeric checks with Secure Profile enabled.
Allow raw statements in basic where clauses.
Allow for space-delimited sort directions like column DESC
.
``value
and values
now work with column formatters.
Correctly format RETURNING clauses with column formatters and ignoring table qualifiers.
Handle multi-word columns in queryRemoveColumns
.
Remove elvis operator due to ACF compatibility issues
Add support for MONEY and SMALLMONEY data types to SchemaBuilder.
Fix wrapping of enum types for Postgres.
Compatibility fix for ACF 2018 and listLast
parsing.
Include current_timestamp default for timestamp
columns in SchemaBuilder.
Ignore table qualifiers for insert and update.
Fix a bug with preventDuplicateJoins when using the closure syntax with a join.
Add executionTime to the data output from BaseGrammar, including being available in interceptors.
Fix a case where a column was not wrapped correctly when a where
used a subquery for the value.
Avoid duplicate
function due to cbORM / Hibernate bugs when used in the same application.
Split off a private whereBasic
method. This is used in Quick to provide extra sql type features.
Add a clearOrders
method. Any already configured orders are cleared. Any orders added after this call will be added as normal.
selectRaw
now can take an array of expressions.
Fixed an issue using column formatters with update
and insert
.
Using a new preventDuplicateJoins
setting in the module settings, qb can detect duplicate joins and ignore them. This is especially useful in a heavily filtered and dynamic query where you may or may not need the join at all or more than one column may need the same join. preventDuplicateJoins
defaults to false
, so it is opt-in. It may be turned on by default in a future breaking release of qb.
Enhance order by's with more direction options (c767ac8)
You can now use two shortcut methods: orderByAsc
and orderByDesc
. Additionally, orderBySub
or using orderBy
with a closure or builder instance will respect the direction argument.
Fix using whereBetween
with query param structs (07c9b72)
Ignore orders in aggregate queries (39e1338)
Format with cfformat (dc2a9b6)
Improve column wrapping with trimming (d98a5cb)
Prefer the parent query over magic methods when the parent query has the exact method. (f9fd8d1)
Switch to using ForgeBox Storage.
Allow passing query options in to paginate (cdecfb3)
Fix for inserting null values directly (1de27a6)
Use cfformat for automatic formatting (119e434)
Add a type to the onMissingMethod exception (90d1093)
Correctly wrap comments in MySQLGrammar
.
Publish qb apidocs to Ortus API Docs.
Fix for null values breaking the new checkIsActuallyNumeric
method in QueryUtils
.
Add a parameterLimit
public property to SqlServerGrammar
. This property is used in Quick to split up eager loading to work around the 2100 param limit of SQL Server.
Allow a parent query to be set. A parent query will receive any method calls that are not found on the Query Builder instance. This is especially useful for instances like Quick to allow Quick features like scopes to be available inside any closures.
Lambdas (arrow functions) are now allowed wherever closures are allowed.
Add an orderByRaw
method.
Please see the Migration Guide for more information on these changes.
Drop support for Lucee 4.5 and Adobe ColdFusion 11.
MSSQLGrammar
renamed to SqlServerGrammar
Remove variadic parameters support in builder functions like select
.
The defaultGrammar
mapping needs to be the full WireBox mapping, including the @qb
, if needed.
For instance, MSSQLGrammar
would become MSSQLGrammar@qb
.
This will allow for other grammars to be more easily contributed via third party modules.
The argument names of forPage
changed to match the new paginate
method.
Add defaultValue
and optional exception throwing to value
. (This changed the argument order.)
All methods that could conceivably take a subquery as well as a value now accept a closure or another builder instance to use as a subquery. (This changed the argument names in some instances.)
Completely revamped documentation! (You're looking at it right now.)
Add new flag to toSQL( showBindings = true )
to replace question marks (?
) with cfqueryparam
-compatible structs for debugging.
Preserve column case and order when converting a query to an array using the default "array"
return format.
Add a new paginate method to generate a pagination struct alongside the results. This can be customized using a custom PaginationCollector.
Allow raw values in insert
calls.
Allow default queryExecute
options
to be configure at a Query Builder level. This also enables custom QueryBuilders
a la Hyper.
Add a whereLike
method.
Allow closures to be used in left and right joins.
Provide an addUpdate
method to programmatically build the SET
clause of an update query.
Add a new chunk
method to grab records from the database in small sets.
Add raw
in alterTable
segments.
Add dropAllObjects
support for SqlServerGrammar
and OracleGrammar
to support migrate fresh
from cfmigrations.
Add a renameTable
alias for rename
.
Remove default constraints when dropping columns with a default on SqlServerGrammar
.
Add more column types and column helpers to SchemaBuilder
, including:
datetimeTz
lineString
nullableTimestamps
point
polygon
softDeletes
softDeletesTz
timeTz
timestamps
timestampTz
timestampsTz
withCurrent
****
when
callbacksThis isn't a breaking change that will affect most people. In fact, it will most likely improve your code.
Previously, when using the when
control flow function, you were fully responsible for the wrapping of your where statements. For example, the following query:
Would generate the following SQL:
The problem with this statement is that the OR
can short circuit the active
check.
The fix is to wrap the LIKE
statements in parenthesis. This is done in qb using a function callback to where
.
When using the when
control flow function, it was easy to miss this. This is because you are already in a closure - it looks the same as when using where
to group the clauses.
In qb 8.0.0, when
will automatically group added where clauses when needed. That means our original example now produces the SQL we probably expected.
Grouping is not needed if there is no OR
combinator. In these cases no grouping is added.
If you had already wrapped your expression in a group inside the when
callback, nothing changes. Your code works as before. The OR
combinator check only works on the top most level of added where clauses.
Additionally, if you do not add any where clauses inside a when
callback, nothing changes from qb 7.
The breaking change part is if you were relying on these statements residing at the same level without grouping. In those cases, you may pass the withoutScoping
flag to the when
callback.
Support for Lucee 4.5 and Adobe ColdFusion 11 has been dropped. If you need support for these engines, please remain on an earlier version of qb.
MSSQLGrammar
was visually too close to MySQLGrammar
and was hard to differentiate quickly. SqlServerGrammar
is much more unique and easily identifiable. Additionally, more people that use this library refer to their database engine as "SQL Server" than "MSSQL".
To migrate, replace any instances of MSSQLGrammar
with SqlServerGrammar
. Make sure to also append the @qb
namespace, if needed, as explained below.
Variadic parameter support was the ability to pass any number of arguments to certain methods like select
.
This code came with a slight performance cost and readability cost. That, combined with the fact that the above syntax is very close to an array, we are dropping support for variadic parameters. To migrate, wrap instances of variadic parameters in an array:
In previous versions, the value passed to defaultGrammar
was used to look up a mapping in the @qb
namespace. This made it difficult to add or use grammars that weren't part of qb. (You could get around this be registering your custom grammar in the @qb
namespace, but doing so seemed strange.)
To migrate this code, change your defaultGrammar
to be the full WireBox mapping in your moduleSettings
:
A defaultValue
parameter and optional exception throwing was added to value
. This pushed the options
struct to the end of the method. If you are using positional parameters with value
, you will need to update your method calls to either use named parameters or the new positions.
callback
to query
All methods that could conceivably take a subquery as well as a value now accept a closure or another builder instance to use as a subquery. This led to changing the callback
argument to query
in the following cases:
whereSub
whereInSub
whereExists
orWhereExists
whereNotExists
andWhereNotExists
orWhereNotExists
whereNullSub
orderBySub
subSelect
If you are using named parameters with any of the above methods you will need to migrate your method calls.
Version v5.0.0
brings support for SchemaBuilder
inside qb
. To avoid naming confusion, Builder
was renamed to QueryBuilder
. Any references in your code to Builder@qb
need to be updated to QueryBuilder@qb
.
Used to set the base table for the query.
You can optionally specify an alias for the table.
An alias for from
where you like how calling table
looks.
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.
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.
Using fromRaw
will most likely tie your code to a specific database, so think carefully before using the fromRaw
method if you want your project to be database agnostic.
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.
Complex queries often contain derived tables. Derived tables are essentially a temporal table defined as a subquery in the from
statement.
In additional a function callback, a separate QueryBuilder
instance can be passed to the fromSub
method.
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
.
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.
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 "*"
.
Calling distinct will cause the query to be executed with the DISTINCT
keyword.
distinct
applies to the entire query, not just certain fields.
This method adds the columns passed to it to the currently selected columns.
If the QueryBuilder
is currently selecting all columns ("*"
) when this method is called, the incoming columns will becoming the only columns selected.
A shortcut to use a raw expression in the select clause.
The expression is added to the other already selected columns.
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.
Clears out the selected columns for a query along with any configured select bindings.
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.
`` are also supported as the table
argument (though you may prefer the readability of the method):
You can specify clauses in your joins as well.
A preconfigured can also be passed to the join function. This allows you to extract shared pieces of code out to different functions.
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 for more information.
Uses the raw SQL provided to as the table for the join clause. All the other functionality of joinRaw
matches the method. Additionally, there are , , and crossJoinRaw
methods available.
Adds a join to a derived table. All the functionality of the method applies to constrain the query. The derived table can be defined using a QueryBuilder
instance:
Uses the raw SQL provided to as the table for the left join clause. All the other functionality of leftJoinRaw
matches the method.
Adds a left join to a derived table. All the functionality of the method applies to define and constrain the query.
Uses the raw SQL provided to as the table for the right join clause. All the other functionality of rightJoinRaw
matches the method.
Adds a right join to a derived table. All the functionality of the method applies to define and constrain the 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 . Cross joins cannot be constrained, however.
Creates a new . A is a specialized version of a QueryBuilder
. You may call on
or orOn
to constrain the . You may also call any methods.
Creating a directly is useful when you need to share a join between different queries. You can create and configure the in a function and pass it to queries as needed.
Although a can be passed to , , , and crossJoin
, the type of the will override the type of the function.
A JoinClause
is a specialized version of a QueryBuilder
. You may call on
or orOn
to constrain the JoinClause
. You may also call any methods.
(To learn more about raw and expressions, check out the docs on .)
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 can be passed here.
Name
Type
Required
Default
Description
from
string | Expression
true
​
The name of the table or a Expression object from which the query is based.
Name
Type
Required
Default
Description
table
string | Expression
true
​
The name of the table or a Expression object from which the query is based.
Name
Type
Required
Default
Description
from
string
true
​
The sql snippet to use as the table.
bindings
array
false
[]
Any bindings needed for the expression.
Name
Type
Required
Default
Description
alias
string
true
​
The alias for the derived table.
input
Function | QueryBuilder
true
Either a QueryBuilder
instance or a closure to define the derived query.
Name | Type | Required | Default | Description |
operator | string |
|
| The boolean operator for the join clause. |
where | boolean |
|
| Sets if the value of |
Name | Type | Required | Default | Description |
operator | string |
|
| The boolean operator for the join clause. |
where | boolean |
|
| Sets if the value of |
Name | Type | Required | Default | Description |
Name | Type | Required | Default | Description |
table | string |
| ​ | The raw SQL string to use as the table. |
Name | Type | Required | Default | Description |
alias | string |
| The alias for the derived table. |
input | Function | QueryBuilder |
| ​ | Either a |
Name | Type | Required | Default | Description |
columns | string | array |
| ​ | A single column, list of columns, or array of columns to retrieve. |
Name | Type | Required | Default | Description |
state | boolean |
| ​ | Value to set the distinct flag. |
Name | Type | Required | Default | Description |
columns | string | array |
| ​ | A single column, list of columns, or array of columns to add to the select. |
Name | Type | Required | Default | Description |
expression | any |
| ​ | The raw expression for the select statement. |
bindings | array |
|
| Any bindings needed for the raw expression. |
Name | Type | Required | Default | Description |
alias | string |
| ​ | The alias for the subselect expression. |
query | Function | QueryBuilder |
| The callback or query to use in the subselect. |
Name | Type | Required | Default | Description |
No arguments | `` |
Name | Type | Required | Default | Description |
columns | string | array |
| ​ | A single column, list of columns, or array of columns to retrieve. |
Name | Type | Required | Default | Description |
expression | any |
| ​ | The raw expression for the select statement. |
bindings | array |
|
| Any bindings needed for the raw expression. |
Table of Contents |
Name | Type | Required | Default | Description |
table |
| ​ |
first |
|
operator | string |
|
| The boolean operator for the join clause. |
second |
|
type | string |
|
|
where | boolean |
|
|
Name | Type | Required | Default | Description |
table | string |
| ​ | The raw SQL string to use as the table. |
first |
|
operator | string |
|
| The boolean operator for the join clause. |
second |
|
type | string |
|
|
Name | Type | Required | Default | Description |
table | string |
| ​ | The raw SQL string to use as the table. |
first |
|
operator | string |
|
| The boolean operator for the join clause. |
second |
|
type | string |
|
|
where | boolean |
|
| Sets if the value of |
Name | Type | Required | Default | Description |
alias | string |
| The alias for the derived table. |
input | Function | QueryBuilder |
| ​ | Either a |
first |
|
operator | string |
|
| The boolean operator for the join clause. |
second |
|
type | string |
|
|
where | boolean |
|
| Sets if the value of |
table |
| ​ |
first | string | Expression | Function |
|
second |
|
Name | Type | Required | Default | Description |
table | string |
| ​ | The raw SQL string to use as the table. |
first |
|
operator | string |
|
| The boolean operator for the join clause. |
second |
|
where | boolean |
|
| Sets if the value of |
Name | Type | Required | Default | Description |
alias | string |
| The alias for the derived table. |
input | Function | QueryBuilder |
| ​ | Either a |
first |
|
operator | string |
|
| The boolean operator for the join clause. |
second |
|
where | boolean |
|
| Sets if the value of |
table |
| ​ |
first |
|
second |
|
Name | Type | Required | Default | Description |
table | string |
| ​ | The raw SQL string to use as the table. |
first |
|
operator | string |
|
| The boolean operator for the join clause. |
second |
|
where | boolean |
|
| Sets if the value of |
Name | Type | Required | Default | Description |
alias | string |
| The alias for the derived table. |
input | Function | QueryBuilder |
| ​ | Either a |
first |
|
operator | string |
|
| The boolean operator for the join clause. |
second |
|
where | boolean |
|
| Sets if the value of |
table |
| ​ |
Name | Type | Required | Default | Description |
table |
| ​ |
type | string |
|
| The type of the join. Valid types are |
Name | Type | Required | Default | Description |
first |
|
operator | string |
|
| The boolean operator for the condition. |
second |
|
combinator | string |
|
| The boolean combinator for the clause (e.g. "and" or "or"). |
Name | Type | Required | Default | Description |
first |
|
operator | string |
|
| The boolean operator for the condition. |
second |
|
The query builder also lets you create union statements on your queries using either UNION
or UNION ALL
strategies.
The union
methods take either a Query Builder instance or a closure which you use to define a new QueryBuilder instance.
Union statements are added in the order in which the union
methods are invoked, but the union
statements can be in any order in your API call stack. This means you can safely declare your union
method calls before the select
, from
and orderBy
calls on the source Query Builder instance.
union()
— This method builds a SQL statement using the UNION
clause which combines two SQL queries into a single result set containing all the matching rows. The two queries must have the same defined columns and compatible data types or the SQL engine will generate an error. The union
clause only returns unique rows.
unionAll()
— This builds a SQL statement using the UNION ALL
clause. This is the same as union
but includes duplicate rows.
IMPORTANT: The QueryBuilder instances passed to a union
statement cannot contain a defined order. Any use of the orderBy()
method on the unioned QueryBuilder instances will result in an OrderByNotAllowed
exception. To order the results, add an orderBy()
call to the parent source Query Builder instance.
Adds a UNION statement to the query.
Adding multiple union statements will append it to the query.
It can also add union queries as QueryBuilder instances.
Adds a UNION ALL statement to the query.
Adding multiple unionAll
statements will append it to the query.
It can also add union queries as QueryBuilder instances.
string | |
The name of the table or a object from which the query is based. Alternatively, a configured instance can be passed.
string | | Function
The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.
string |
The second column or to join the table on.
The type of the join. Passing this as an argument is discouraged for readability. Use the dedicated methods like and where possible.
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.
string | | Function
The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.
string |
The second column or to join the table on.
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.
string | | Function
The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.
string |
The second column or to join the table on.
The type of the join. Passing this as an argument is discouraged for readability. Use the dedicated methods like and where possible.
string | | Function
The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.
string |
The second column or to join the table on.
The type of the join. Passing this as an argument is discouraged for readability. Use the dedicated methods like and where possible.
string | |
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.)
The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.
string |
The second column or to join the table on.
string | | Function
The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.
string |
The second column or to join the table on.
string | | Function
The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.
string |
The second column or to join the table on.
string | |
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.)
string | | Function
The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.
string |
The second column or to join the table on.
string | | Function
The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.
string |
The second column or to join the table on.
string | | Function
The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.
string |
The second column or to join the table on.
string | |
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.)
string |
The name of the table or a object from which the query is based.
string | | Function
The first column or of the condition. Alternatively, a function can be passed to nest conditions with parenthesis.
string |
The second column or of the condition.
string | | Function
The first column or of the condition. Alternatively, a function can be passed to nest conditions with parenthesis.
string |
The second column or of the condition.
Name
Type
Required
Default
Description
input
Function | QueryBuilder
true
The function or QueryBuilder instance to use as the unioned query.
all
boolean
false
false
Determines if statement should be a "UNION ALL". Passing this as an argument is discouraged. Use the dedicated unionAll
where possible.
Name
Type
Required
Default
Description
input
Function | QueryBuilder
true
The function or QueryBuilder instance to use as the unioned query.
If you store the builder object in a variable, you can use if
and else
statements like you would expect.
This works, but breaks chainability. To keep chainability you can use the when
helper method.
when
The when
helper is used to allow conditional statements when defining queries without using if statements and having to store temporary variables.
You can pass a third argument to be called in the else
case.
when
callbacks are automatically scoped and grouped. That means that if a where clause is added inside the callback with an OR
combinator the clauses will automatically be grouped (have parenthesis put around them.) You can disable this feature by passing withoutScoping = true
to the when
callback.
When passing a parameter to qb, it will infer the sql type to be used. If you pass a number, CF_SQL_NUMERIC
will be used. If it is a date, CF_SQL_TIMESTAMP
, and so forth. If you need more control, you can pass a struct with the parameters you would pass to cfqueryparam
.
You can pass include any parameters you would use with cfqueryparam
including null
, list
, etc. This applies anywhere parameters are used including where
, update
, and insert
methods.
This can be used when inserting or updating records as well.
By default, qb will try to determine if a variable is a date using the built-in isDate
function. This can have some interesting effects with different formatted strings. You can opt in to stricter date detection which will check the underlying Java class of the value to determine if the value is a date. This is more accurate, but does require you to specifically pass date instances instead of strings. For this reason, it is currently opt-in to not break existing applications. It is likely to become the default in the next major version of qb.
You can opt in to stricter date detection by setting strictDateDetection = true
in your moduleSettings
in config/ColdBox.cfc
.
By default, qb will use the CF_SQL_NUMERIC
SQL type when it detects a numeric binding. You can specify your own default SQL type to use with numeric values using the numericSQLType
setting in your moduleSettings
in config/ColdBox.cfc
.
There is an opt-in feature to better derive the numeric SQL type for database performance reasons. If you do opt in to this, qb will use a different SQL type for integers than decimals. You can opt in to this feature using the autoDeriveNumericType
setting and can customize the SQL types by setting the integerSqlType
and decimalSqlType
settings.
In some combinations of database grammars and CFML engines, the scale
argument on a cfqueryparam
would default to 0
. This would cause issues when attempting to insert a floating point number, even when using the correct SQL type (i.e., CF_SQL_DECIMAL
) . In 8.5.0, qb now automatically calculates a scale based on the value provided if the value is a floating point number. This can be disabled by setting autoAddScale
in your ColdBox config or passing autoAddScale = false
when instantiating your QueryBuilder
instance.
Bindings are the values that will be sent as parameters to a prepared SQL statement. This protects you from SQL injection. In CFML, this uses cfqueryparam
to parameterize the values.
If you need to inspect the bindings for the current query you can retrieve them in order using the getBindings
method.
You can view the current SQL for the query with bindings inline for debugging purposes using the toSQL
method.
Use these methods only for debugging. Modifying the bindings directly will likely cause issues when executing your query. Adding or removing bindings should be done using the public API.
This method returns the current bindings in order to be used for the query.
You can also retrieve the bindings associated to their corresponding types.
This method returns the current bindings to be used for the query associated to their corresponding types.
qb allows you to specify default options when creating the QueryBuilder instance using the defaultOptions
argument.
You can set defaultOptions
for the default QueryBuilder (QueryBuilder@qb
) in your config/ColdBox.cfc
file under moduleSettings
.
You can also combine this with WireBox to create custom QueryBuilder instances pointing to different datasources and even different grammars.
In Application.cfc
you can specify your default datasource which will be used by qb. If you want to retrieve data from other datasources you can specify this in all retrieval functions by using the extra options parameter such as:
If you also want to use a non-default SQL Grammar you have to specify this when creating your QueryBuilder
.
Each query execution method allows for the passing of an options struct. This is the same struct you would pass to .
When mapping to components provided by modules, such as qb, use the interception point inside your config/WireBox.cfc
to ensure all modules are fully loaded and available.
Name
Type
Required
Default
Description
condition
boolean
true
The condition to switch on.
onTrue
Function
true
The callback to execute if the condition is true. It is passed the builder
object as the only parameter.
onFalse
Function
false
function( q ) { return q; }
The callback to execute if the conditions is false. It is passed the builder
object as the only parameter.
withoutScoping
boolean
false
false
Flag to turn off the automatic scoping of where clauses during the callback.
Name
Type
Required
Default
Description
No arguments
Name
Type
Required
Default
Description
No arguments
Two interception points are available from QB: preQBExecute
and postQBExecute
. These fire before and after the queryExecute
call, respectively.
The following information is available in the interceptData
struct:
The following information is available in the interceptData
struct:
Name
Type
Description
sql
String
The SQL string to execute.
bindings
Struct
The struct of bindings (keys and values) for the query.
options
Struct
Any options to pass along to queryExecute
.
returnObject
String
The type to return: query
or result
.
Name
Type
Description
sql
String
The SQL string to execute.
bindings
Struct
The struct of bindings (keys and values) for the query.
options
Struct
Any options to pass along to queryExecute
.
returnObject
String
The type to return: query
or result
.
query
Query | null
The query object or null
if there isn't one.
result
Struct
The query result struct.
Available as an advanced option for framework authors, qb will call out to a column formatter prior to processing a column as part of the SQL query. This allows frameworks like Quick to define queries using aliases and transform them to columns during execution.
You can provide your own column formatter function to qb through the init
method or by calling setColumnFormatter
. It is a function that takes a column string and returns a string
This method allows you to create a table object.
The majority of the work comes from calling methods on the Blueprint
object. A Blueprint
defines the columns and indexes for your tables.
Example:
This would convert to the following SQL in MySQL:
Only one table can be created at a time. If you wanted to create multiple tables, you would call create
multiple times.
The callback
argument is where you define the schema of your table. It is passed a Blueprint
object. This is commonly aliased as table
in the callback. Blueprint
defines the field, index and constraint methods to build your table. You can find a comprehensive list of all available methods here for columns and here for indexes and constraints.
QB ships with a schema builder to help you build your database objects. This provides a few benefits:
The syntax is expressive and fluent, making it easy to understand what is being executed
The syntax is database-agnostic. Specific quirks are isolated in a Grammar file, making it easy to migrate between engines.
You start with a SchemaBuilder
object. The SchemaBuilder
takes the same Grammar that a QueryBuilder
takes. It can additionally take a struct of default query options forwarded on to queryExecute
.
Note: the
SchemaBuilder
is a transient, and a new one should be created for each operation.
The SchemaBuilder
has four main methods to start your database object creation:
Create a new table in the database.
The majority of the work comes from calling methods on the Blueprint
object. A Blueprint
defines the columns and indexes for your tables.
Example:
SchemaBuilder
SQL (MySQL)
Alter an existing table in the database.
In addition to using the columns and indexes off of the passed-in Blueprint
object, the Blueprint
contains helpers such as addConstraint
, removeConstraint
, addColumn
, renameColumn
, and dropColumn
to assist in altering existing tables.
Example:
SchemaBuilder
SQL (MySQL)
Drop a table from the database.
Example:
SchemaBuilder
SQL (MySQL)
SchemaBuilder
as well:rename
Rename a table from an old name to a new name
Example:
SchemaBuilder
SQL (MySQL)
hasTable
Check if a table exists in the database.
Example:
SchemaBuilder
SQL (MySQL)
hasColumn
Check if a column exists in a table in the database.
Example:
SchemaBuilder
SQL (MySQL)
Argument
Type
Required
Default
Description
table
string
true
The name of the table to create.
callback
function
true
A callback function used to define the table body. It is passed a Blueprint
as the only argument.
options
struct
false
{}
Options to pass to queryExecute
.
execute
boolean
false
true
Run the query immediately after building it.
Argument
Type
Required
Default
Description
table
string
true
The name of the table to create.
callback
function
true
A callback function used to define the table body. It is passed a Blueprint
as the only argument.
options
struct
false
{}
Options to pass to queryExecute
.
execute
boolean
false
true
Run the query immediately after building it.
Argument
Type
Required
Default
Description
table
string
true
The name of the table to alter.
callback
function
true
A callback function used to define the changes to the table. It is passed a Blueprint
as the only argument.
options
struct
false
{}
Options to pass to queryExecute
.
execute
boolean
false
true
Run the query immediately after building it.
Argument
Type
Required
Default
Description
table
string
true
The name of the table to drop.
options
struct
false
{}
Options to pass to queryExecute
.
execute
boolean
false
true
Run the query immediately after building it.
Argument
Type
Required
Default
Description
from
string
true
The old table name.
to
string
true
The new table name.
options
struct
false
{}
Options to pass to queryExecute
.
execute
boolean
false
true
Run the query immediately after building it.
Argument
Type
Required
Default
Description
name
string
true
The name of the table to check.
options
struct
false
{}
Options to pass to queryExecute
.
execute
boolean
false
true
Run the query immediately after building it.
Argument
Type
Required
Default
Description
table
string
true
The name of the table to check for the column in.
column
string
true
The column to check for in the table.
options
struct
false
{}
Options to pass to queryExecute
.
execute
boolean
false
true
Run the query immediately after building it.
Dropping tables straightforward in qb
.
For dropping columns or constraints, see Alter.
Drop a table from the database.
Example:
SchemaBuilder
SQL (MySQL)
Drop a table from the database if it exists.
Example:
SchemaBuilder
SQL (MySQL)
Argument
Type
Required
Default
Description
table
string
true
The name of the table to drop.
options
struct
false
{}
Options to pass to queryExecute
.
execute
boolean
false
true
Run the query immediately after building it.
Argument
Type
Required
Default
Description
table
string
true
The name of the table to drop.
options
struct
false
{}
Options to pass to queryExecute
.
execute
boolean
false
true
Run the query immediately after building it.
Installation is easy through CommandBox and ForgeBox. Simply type box install qb
to get started.
To start a new query, instantiate a new Builder: wirebox.getInstance('QueryBuilder@qb')
.
By default, qb uses a generic Grammar. You can specify your specific grammar in ColdBox by setting the defaultGrammar
in your moduleSettings
.
The grammars provided by qb are:
MySQLGrammar
OracleGrammar
PostgresGrammar
SqlServerGrammar
If you are not using WireBox, just make sure to wire up the Builder
object with the correct grammar:
QB binds all parameters by default and guesses the SQL type based on passed values. The default SQL type for numeric values is CF_SQL_NUMERIC
, which is a floating point number, for the widest compatibility. This can cause performance problems with large recordsets in some database engines. You can provide a different default in coldbox.cfc if you wish to override this setting:
Note: These instructions assume a basic knowledge of FW/1, a working FW/1 application structure with qb installed in the
/subsystems
directory (manually or via CommandBox), and a database configured to run with your application.
Once the application structure is setup, now we need to wire up qb to a bean factory using DI/1.
First we will add a mapping in Application.cfc
.
Next we need to tell DI/1 where qb's components are and how to reference them for later use in the application. We can do so by defining the configuration settings in the variables.framework.subsystems
struct in Application.cfc
. The example below makes use of a load listener to declare each component instance and pass in any constructor arguments.
Now that everything is configured, you can launch your application with CommandBox by entering start
in the terminal or use whatever method you're accustomed to.
To access qb from your application's code, you can call on it by using getBeanFactory()
.
A query builder is a stateful, transient object. That means that if you want to execute two different queries, you need two separate instances of QueryBuilder
.
As such, be careful when injecting QueryBuilder in to a component. If the component is a singleton, you will need to create the QueryBuilder inline or use a provider. This applies to ColdBox handlers as well.
While the above may seem innoculous, it can run in to issues as multiple requests come in to your application. Each request is sharing the same query builder instance and subsequent requests will have unintended results as the where
clause keeps growing request after request.
The solution is to either create the QueryBuilder inline, ensuring that each request has its own query to execute:
Or to use a WireBox provider to create a new query each time it is accessed:
One caveat when using a WireBox Provider: WireBox Providers proxy methods on to a new instance of the provided mapping on all methods except get
. get
is a method on the Provider itself. If you call get
as the first method on a Provider it will return a new instance of QueryBuilder, not execute the query. In those (rare) cases you will need to call query.get().get()
.
Once you have access to a QueryBuilder instance, you can create a new query using the same datasource, utils, returnFormat, paginationCollector, columnFormatter, and defaultOptions as the current QueryBuilder instance.
We welcome all types of contributions!
The most common type of contribution is to fix an incorrect SQL generation for a database grammar.
To debug what SQL is being ran, you can always call toSQL
on any QueryBuilder
or SchemaBuilder
object. Additionally, you can listen to the preQBExecute
interception point for the generated SQL.
Each of the database grammars have two tests — {Grammar}QueryBuilderSpec.cfc
and {Grammar}SchemaBuilderSpec.cfc
. These tests run the same qb syntax across the different grammars. In each test are methods that return SQL strings like so:
If you find an issue with the SQL generated from a grammar, please file a pull request with the correct SQL in these tests. It's okay if you don't submit a fix as well. (But we'd greatly appreciate it!) Doing so will help expedite the fix.
If you want to add support for a new database grammar, simply copy these two tests from an existing grammar, rename them, change the getBuilder
method to return your new grammar, and fill out the SQL as it should be. That will guide your implementation to be 100% compatible with the other grammars in qb.
qb is a fluent query builder for CFML. It is heavily inspired by Eloquent from Laravel.
Using qb, you can:
Quickly scaffold simple queries
Make complex, out-of-order queries possible
Abstract away differences between database engines
Adobe ColdFusion 2016+
Lucee 5+
qb supports four major database grammars:
MySQL (MySQLGrammar
)
Oracle (OracleGrammar
)
Postgres (PostgresGrammar
)
Microsoft SQL Server (SqlServerGrammar
)
The Box modules discussion group and community can be found here:
https://community.ortussolutions.com/c/box-modules/qb/27
Installation is easy through CommandBox and ForgeBox. Simply type box install qb
to get started.
Compare these two examples:
The differences become even more stark when we introduce more complexity:
With qb you can easily handle setting order by statements before the columns you want or join statements after a where clause:
qb enables you to explore new ways of organizing your code by letting you pass around a query builder object that will compile down to the right SQL without you having to keep track of the order, whitespace, or other SQL gotchas!
Here's a gist with an example of the powerful models you can create with this! https://gist.github.com/elpete/80d641b98025f16059f6476561d88202
To start a new query, instantiate a new Builder: wirebox.getInstance('QueryBuilder@qb')
.
By default, qb uses a generic Grammar. You can specify your specific grammar in ColdBox by setting the defaultGrammar
in your moduleSettings
.
If you are not using WireBox, just make sure to wire up the Builder
object with the correct grammar:
Sets the limit value for the query.
Sets the limit value for the query. Alias for limit
.
Sets the offset value for the query.
Helper method to calculate the limit and offset given a page number and count per page.
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.
Name
Type
Required
Default
Description
value
numeric
true
The limit value for the query.
Name
Type
Required
Default
Description
value
numeric
true
The limit value for the query.
Name
Type
Required
Default
Description
value
numeric
true
The offset value for the query.
Name
Type
Required
Default
Description
page
numeric
true
The page number to retrieve.
maxRows
numeric
true
The number of records per page. If a number less than 0 is passed, 0 is used instead.
Passing a single string will group by that one column.
You can also pass a list of column names. A single comma (","
) will be used as the delimiter.
An array of column names can be provided.
Calling groupBy
multiple times will to the current groups.
An Expression
can be passed in place of a column.
Adds a having clause to a query.
Expressions
can be used in place of the column or the value.
Name
Type
Required
Default
Description
groups
string | array
true
A single column name, a list of column names, or an array of column names to group by. An Expression
can be passed as well.
Name
Type
Required
Default
Description
column
string | Expression
true
The name of the column or Expression
with which to constrain the query.
operator
any
false
The operator to use for the constraint (i.e. "=", "<", ">=", etc.). A value can be passed as the operator
and the value
left null as a shortcut for equals (e.g. where( "column", 1 ) == where( "column", "=", 1 ) ).
value
any
false
The value with which to constrain the column. An Expression
can be passed as well.
combinator
string
false
"and"
The boolean combinator for the clause. Valid options are "and"
or "or"
. Avoid passing this parameter explicitly. Where possible use the andHaving
and orHaving
methods instead.
Raw expressions are the qb escape hatch. While qb strives to provide ways to execute the majority of queries, you will occasionally need to provide raw sql values that are not processed by qb. These SQL snippets are called raw
or Expressions
in qb.
raw
expressions are useful, but shoud be used only if there is not another way to accomplish the same action using other qb methods. This is because a raw
expression has the potential to use syntax specific to one database grammar or another, preventing you from easily switching from one grammar to another, one of the major benefits of using qb.
The first way to retrieve an Expression
is to call the raw
method on the QueryBuilder
object.
The sql snippet passed to raw
is not processed by qb at all. With that in mind, it is important to follow all best practices and security recommendations with the sql you use with raw
.
Expressions can be passed to most qb methods, like select
, from
, where
, or orderBy
, among others. Additionally, qb provides some convenience methods to add raw values in different parts of the query:
Name
Type
Required
Default
Description
sql
string
true
The raw sql to wrap up in an Expression.
qb includes a few methods to help you lock certain rows when executing select
statements.
Note: For locks to work properly, they must be nested inside a transaction
. qb does not handle any of the transaction lifecycle for you.
A shared lock prevents the selected rows from being modified until your transaction is committed.
A lock for update lock prevents the selected rows from being modified or selected with another shared lock until your transaction is committed.
The main difference between a sharedLock
and lockForUpdate
is that a lockForUpdate
prevents other reads or selects as well as updates.
When using the skipLocked
flag, the query will skip over locked records and only return and lock available records.
noLock
will instruct your grammar to ignore any shared locks when executing the query.
Currently this only makes a difference in SQL Server grammars.
The lock
method will allow you to add a custom lock directive to your query. Think of it as the raw
method for lock directives.
These lock directives vary from grammar to grammar.
Clears any lock directive on the query.