# What's New?

## 8.9.1

* `HOLDLOCK` and `READPAST` are mutually exclusive table locks in SQL Server but were mistakenly being applied together.

## 8.9.0

* Specify `defaultOptions` [inside of your ColdBox config.](https://qb.ortusbooks.com/8.9.0/query-builder/options-and-utilities/query-options)

## 8.8.1

* Better parsing of `raw` statements when deriving [`insertUsing`](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/inserts-updates-deletes#insertusing) columns.

## 8.8.0

### New Features and Improvements

* Insert data based off of a callback or builder using [`insertUsing`](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/inserts-updates-deletes#insertusing).
* Insert data ignoring duplicate key errors using [`insertIgnore`](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/inserts-updates-deletes#insertignore).
* Use a callback or builder as the source for an [`upsert`](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/inserts-updates-deletes#upsert) statement.
* Allow for deleting unmatched source records in [upserts](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/inserts-updates-deletes#upsert) (SQL Server only).
* Add a new `skipLocked` flag to [`lockForUpdate`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/locks#lockforupdate).

### Bug Fixes

* 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.

## 8.7.8

* Fix for Oracle returning custom column types when renaming a column.

## 8.7.7

* Explicit arguments scoping.

## 8.7.6

* `arrayEach` is slow compared to merging arrays.

## 8.7.5

* Fix wheres with joins in update statements.

## 8.7.2

* Add better null handling to `inferSqlType`.

## 8.7.1

* Correctly format columns being updated.

## 8.7.0

### New Features and Improvements

* Add an [upsert](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/inserts-updates-deletes#upsert) method.  `upsert` can update or insert multiple records at once depending on if a column is matched.
* Allow expressions in [`value`](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/retrieving-results#value) and [`values`](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/retrieving-results#values).  Also add a [`valueRaw`](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/retrieving-results#valueraw) and [`valuesRaw`](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/retrieving-results#valuesraw) helper method to make that pattern more ergonomic.
* Allow [`JOIN` statements in `UPDATE` statements](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/inserts-updates-deletes#updating-with-joins).  (This is **not** supported on Oracle.)
* Allow [updates with subselects](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/inserts-updates-deletes#updating-with-subselects) using closures or builder instances.

### Bug Fixes

* Better handling of [`group by`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/group-by-and-having#groupby) and [`having`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/group-by-and-having#having) clauses in [pagination](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/limit-offset-and-pagination#simplepaginate-and-paginate) queries.
* Allow any value to be returned from [aggregates](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/aggregates) including strings, numbers, and dates.
* Provide default values for [sum](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/aggregates#sum) and [count](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/aggregates#count) if no records are returned.
* Test in CI with [full](https://coldfusion.adobe.com/2018/07/null-support-in-coldfusion-2018/) [null](https://docs.lucee.org/guides/cookbooks/NullSupport.html) support.

## 8.6.1

* Correctly wrap CTE expressions with parenthesis when required in certain grammars.

## 8.6.0

* `SchemaBuilder` can now be configured with [default query options](https://qb.ortusbooks.com/8.9.0/schema-builder/schema-builder).  (Default options will still be overridden by options passed to each `SchemaBuilder` method.)

## 8.5.0

### QueryBuilder

* Add a [`reset`](https://qb.ortusbooks.com/8.9.0/query-builder/options-and-utilities/clone-and-reset#reset) method to QueryBuilder.
* Add [locking](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/locks) helpers such as [`lock`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/locks#lock), [`noLock`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/locks#nolock), [`lockForUpdate`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/locks#lockforupdate), and [`sharedLock`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/locks#sharedlock).
* Correct return aggregate values for date values from `max` and `min` executors.
* [Automatically add a `scale`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/parameters-and-bindings#automatic-scale-detection) to an incoming query param when needed.
* Add a [`whereNotLike`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/wheres#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.

### SchemaBuilder

* Add support for [stored computed columns](https://qb.ortusbooks.com/8.9.0/schema-builder/column-modifiers#storedas) and [virtual computed columns](https://qb.ortusbooks.com/8.9.0/schema-builder/column-modifiers#virtualas).

## 8.4.9

* Swap `master` branch to `main` branch.

## 8.4.8

* Remove unnecessary injection for QueryUtils.

## 8.4.7

* Account for raw expressions when generating mementos for comparison

## 8.4.6

* Add support for [mediumtext](https://qb.ortusbooks.com/8.9.0/schema-builder/columns#mediumtext) & [longtext](https://qb.ortusbooks.com/8.9.0/schema-builder/columns#longtext) types for MySQLGrammar.

## 8.4.5

* Fix limit on [simplePaginate](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/retrieving-results#simplepaginate).

## 8.4.1 - 8.4.4

* Migrate release process to GitHub Actions.

## 8.4.0

* Add a `simplePaginate` pagination method for quicker performance when total records or total pages are not needed or too slow.

## 8.3.0

* Introduce a [`numericSQLType`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/parameters-and-bindings#numeric-sql-type) setting to specify the default numeric SQL type.

## 8.2.2

* Default to `html` for the `dump` format argument to `writeDump`.

## 8.2.1

* Correctly use the passed in `strictDateDetection` to the `QueryUtils.cfc`.

## 8.2.0

{% hint style="success" %}
📹 [Watch a walkthrough of this change on CFCasts.](https://cfcasts.com/series/whats-new-in-qb-8)
{% endhint %}

* Added a [`dump`](https://qb.ortusbooks.com/8.9.0/query-builder/debugging#dump) command to aid in debugging a query while chaining.

## 8.1.0

{% hint style="success" %}
📹 [Watch a walkthrough of these changes on CFCasts.](https://cfcasts.com/series/whats-new-in-qb-8)
{% endhint %}

* \`\`[`orderByRaw`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/ordering-grouping-and-limit#order-by-raw) now can accept bindings.
* A new, optional [`strictDateDetection`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/parameters-and-bindings#strict-date-detection) setting is available to check the underlying Java class of a date object instead of using `isDate`.

## 8.0.3

* Ignore select bindings for aggregate queries.
* Allow spaces in table aliases.
* Split FLOAT and DECIMAL column types in SQL Server.

## 8.0.2

* Clear orderBy bindings when calling `clearOrders`.

## 8.0.1

* Trim table definitions before searching for aliases.  Makes qb more lenient with extra whitespace.

## 8.0.0

{% hint style="success" %}
📹 [Watch a walkthrough of these changes on CFCasts.](https://cfcasts.com/series/whats-new-in-qb-8)
{% endhint %}

#### BREAKING CHANGES

* \`\`[`when`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/when#when) callbacks now automatically scope and group where clauses when an `OR` combinator is used.

#### Other Changes

* Combine [`clearOrders`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/ordering-grouping-and-limit#clearorders) and `orderBy` with a new [`reorder`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/ordering-grouping-and-limit#reorder)method.
* Clear current selected columns with [`clearSelect`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/selects#clearselect).
* Combine [`clearSelect`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/selects#clearselect) and either [`select`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/selects#get) or [`selectRaw`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/selects#get-3) with [`reselect`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/selects#reselect) and [`reselectRaw`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/selects#reselectraw) respectively.

## 7.10.0

* Expose nested where functions to enable advanced query manipulation in downstream libraries like Quick.

## 7.9.9

* Fixes for OracleGrammar including table aliases and wrapped subqueries.

## 7.9.8

* Allow nullable [timestamps](https://qb.ortusbooks.com/8.9.0/schema-builder/columns#timestamp) in MySQL.

## 7.9.7

* Return 0 on null [aggregates](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/aggregates).

## 7.9.6

* Match type hints to documentation for [join](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/joins) functions

## 7.9.5

* Handle enhanced numeric checks with Secure Profile enabled.

## 7.9.4

* Allow raw statements in basic where clauses.

## 7.9.3

* Passed along the options struct to the [`count`](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/aggregates#count) method when calling [`paginate`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/limit-offset-and-pagination#paginate).

## 7.9.2

* Allow for space-delimited [sort](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/ordering-grouping-and-limit) directions like `column DESC`.
* Add helpful message when trying to use a closure with [`from`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/from#get) instead of [`fromSub`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/from#get-3).
* \`\`[`value`](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/retrieving-results#value) and [`values`](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/retrieving-results#values) now work with [column formatters.](https://qb.ortusbooks.com/8.9.0/query-builder/options-and-utilities/column-formatter)
* Correctly format RETURNING clauses with [column formatters](https://qb.ortusbooks.com/8.9.0/query-builder/options-and-utilities/column-formatter) and ignoring table qualifiers.

## 7.9.1

* Handle multi-word columns in `queryRemoveColumns`.

## 7.9.0

* Remove elvis operator due to ACF compatibility issues

## 7.8.0

* Add support for [MONEY](https://qb.ortusbooks.com/8.9.0/schema-builder/columns#money) and [SMALLMONEY](https://qb.ortusbooks.com/8.9.0/schema-builder/columns#smallmoney) data types to [SchemaBuilder](https://qb.ortusbooks.com/8.9.0/schema-builder/create).

## 7.7.3

* Fix wrapping of [enum](https://qb.ortusbooks.com/8.9.0/schema-builder/columns#enum) types for Postgres.

## 7.7.2

* Compatibility fix for ACF 2018 and `listLast` parsing.
* Include current\_timestamp default for [`timestamp`](https://qb.ortusbooks.com/8.9.0/schema-builder/columns#timestamp) columns in SchemaBuilder.
* Ignore table qualifiers for insert and update.

## 7.7.1

* Fix a bug with preventDuplicateJoins when using the closure syntax with a join.

## 7.7.0

* Add executionTime to the data output from BaseGrammar, including being available in interceptors.

## 7.6.2

* Fix a case where a column was not wrapped correctly when a `where` used a subquery for the value.

## 7.6.1

* Avoid `duplicate` function due to cbORM / Hibernate bugs when used in the same application.

## 7.6.0

* Split off a private `whereBasic` method.  This is used in Quick to provide extra sql type features.
* Add a [`clearOrders`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/ordering-grouping-and-limit#clearorders) method.  Any already configured orders are cleared.  Any orders added after this call will be added as normal.
* [`selectRaw`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/selects#get-3) now can take an array of expressions.

## 7.5.1

Fixed an issue using column formatters with `update` and `insert`.

## 7.5.0

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.

## 7.4.0

Enhance order by's with more direction options ([c767ac8](https://github.com/coldbox-modules/qb/commit/c767ac8764fab70d70dc77baa7bb9fb27c1d4eeb))

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.

## 7.3.15

* Fix using `whereBetween` with query param structs ([07c9b72](https://github.com/coldbox-modules/qb/commit/07c9b728bdbad6bf02ccd9d21dbdf6968062c02e))

## 7.3.14

* Ignore orders in aggregate queries ([39e1338](https://github.com/coldbox-modules/qb/commit/39e1338a147838165e05225bd91ef7e6cde2319a))

## 7.3.13

* Format with cfformat ([dc2a9b6](https://github.com/coldbox-modules/qb/commit/dc2a9b61503690d753a71c3b7bce002ebdf4ccda))

## 7.3.12

* Improve column wrapping with trimming ([d98a5cb](https://github.com/coldbox-modules/qb/commit/d98a5cb65851c154b6755e90254d1a2c1df82833))
* Prefer the parent query over magic methods when the parent query has the exact method. ([f9fd8d1](https://github.com/coldbox-modules/qb/commit/f9fd8d157cdc0d7480811c4659c130ee1d58888f))

## 7.3.9, 7.3.10, 7.3.11

* Switch to using [ForgeBox Storage](https://commandbox.ortusbooks.com/forgebox-enterprise/storage#storing-package-binaries-on-forgebox).

## 7.3.8

* Allow passing query options in to paginate ([cdecfb3](https://github.com/coldbox-modules/qb/commit/cdecfb36f5acab87edd3a478c570f77d285df554))

## 7.3.7

* Fix for inserting null values directly ([1de27a6](https://github.com/coldbox-modules/qb/commit/1de27a697f65bfdeed63442ad66be47cd0d30344))

## 7.3.5, 7.3.6

* Use cfformat for automatic formatting ([119e434](https://github.com/coldbox-modules/qb/commit/119e434b307a2cc2323b857a214c20842cafbbd4))
* Add a type to the onMissingMethod exception ([90d1093](https://github.com/coldbox-modules/qb/commit/90d109312b2ea86c00db34020b12b5ab22bb377b))

## 7.3.4

* Correctly wrap [comments](https://qb.ortusbooks.com/8.9.0/schema-builder/column-modifiers#comment) in `MySQLGrammar`.

## 7.3.2, 7.3.3

* Publish qb apidocs to [Ortus API Docs](https://apidocs.ortussolutions.com/#/coldbox-modules/qb/).

## 7.3.1

* Fix for null values breaking the new `checkIsActuallyNumeric` method in `QueryUtils`.

## 7.3.0

* 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.

## 7.2.0

* Allow a [parent query](https://qb.ortusbooks.com/8.9.0/query-builder/options-and-utilities/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](https://quick.ortusbooks.com/) to allow Quick features like scopes to be available inside any closures.

## 7.1.0

* Lambdas (arrow functions) are now allowed wherever closures are allowed.
* Add an [`orderByRaw`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/ordering-grouping-and-limit#order-by-raw) method.
* Allow for fully-qualified column names (`table_name.column.name`) in the [`value`](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/retrieving-results#value) and [`values`](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/retrieving-results#values) methods.

## 7.0.0

#### **BREAKING CHANGES**

*Please see the* [*Migration Guide*](https://qb.ortusbooks.com/8.9.0/migration-guide#v-7-0-0) *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.)

#### **Other Changes**

* Completely revamped documentation! (You're looking at it right now.)
* Add new flag to [`toSQL( showBindings = true )`](https://qb.ortusbooks.com/8.9.0/query-builder/debugging#tosql) 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](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/retrieving-results#paginate) method to generate a pagination struct alongside the results.  This can be customized using a custom [PaginationCollector](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/retrieving-results#custom-pagination-collectors).
* Allow raw values in [`insert`](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/inserts-updates-deletes#insert) calls.
* Allow [default `queryExecute` `options`](https://qb.ortusbooks.com/8.9.0/query-builder/options-and-utilities/query-options#default-options) to be configure at a Query Builder level.  This also enables custom `QueryBuilders` a la [Hyper](https://www.forgebox.io/view/hyper).
* Add a [`whereLike`](https://qb.ortusbooks.com/8.9.0/query-builder/building-queries/wheres#wherelike) method.
* Allow closures to be used in left and right joins.
* Provide an [`addUpdate`](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/inserts-updates-deletes#addupdate) method to programmatically build the `SET` clause of an update query.
* [Add a new `chunk` method](https://qb.ortusbooks.com/8.9.0/query-builder/executing-queries/retrieving-results#chunking-results) 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`

\*\*\*\*

## 6.4.0

* [Allow Expressions (`query.raw`) in update statements.](https://qb.ortusbooks.com/8.9.0/whats-new)