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.

// This will cause you pain and grief...

var user = query.from( "users" )
  .where( "username", rc.username )
  .first();

var posts = query.from( "posts" ).get();
// This will error because `username` is not a column in `posts`.

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.

component {

    property name="query" inject="QueryBuilder@qb";

    function create( event, rc, prc ) {
        query.table( "posts" )
            .where( "id", rc.id )
            .update( event.getOnly( [ "body" ] ) );
    }

}

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:

component {

    function create( event, rc, prc ) {
        getInstance( "QueryBuilder@qb" )
            .table( "posts" )
            .where( "id", rc.id )
            .update( event.getOnly( [ "body" ] ) );
    }

}

Or to use a WireBox provider to create a new query each time it is accessed:

component {

    property name="query" inject="provider:QueryBuilder@qb";

    function create( event, rc, prc ) {
        query.table( "posts" )
            .where( "id", rc.id )
            .update( event.getOnly( [ "body" ] ) );
    }

}

newQuery

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.

// This will cause you pain and grief...

var user = query.from( "users" )
  .where( "username", rc.username )
  .first();

var posts = query.newQuery().from( "posts" ).get();
// This will work as we expect it to.

from

Used to set the base table for the query.

query.from( "users" );

SELECT * FROM `users`

You can optionally specify an alias for the table.

query.from( "users as u" );

SELECT * FROM `users` AS `u`

table

An alias for from where you like how calling table looks.

query.table( "users" ).insert( { "name" = "jon" } );

INSERT INTO `users` (`name`) VALUES (?)

fromRaw

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.

query.fromRaw( "[users] u (nolock)" ).get();

SELECT * FROM [users] u (nolock) 

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.

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.

query.fromRaw(
    "dbo.generateDateTable(?, ?, ?) as dt",
    [ "2017-01-01", "2017-12-31", "m" ]
).get();

SELECT * FROM dbo.generateDateTable(?, ?, ?) as dt

fromSub

Complex queries often contain derived tables. Derived tables are essentially a temporal table defined as a subquery in the from statement.

query.select( [ "firstName", "lastName" ] )
    .fromSub( "legalUsers", function ( q ) {
        q.select( [ "lName as lastName", "fName as firstName" ] )
            .from( "users" )
            .where( "age", ">=", 21 )
        ;
    } )
    .orderBy( "lastName" )
    .get()

SELECT `firstName`, `lastName`
FROM (
    SELECT `lName` as `lastName`, `fName` as `firstName`
    FROM `users`
    WHERE `age` >= 21
) AS `legalUsers`
ORDER BY `lastName`

In additional a function callback, a separate QueryBuilder instance can be passed to the fromSub method.

var legalUsersQuery = query
    .select( [ "lName as lastName", "fName as firstName" ] )
    .from( "users" )
    .where( "age", ">=", 21 );

query.select( [ "firstName", "lastName" ] )
    .fromSub( "legalUsers", legalUsersQuery )
    .orderBy( "lastName" )
    .get();

SELECT `firstName`, `lastName`
FROM (
    SELECT `lName` as `lastName`, `fName` as `firstName`
    FROM `users`
    WHERE `age` >= 21
) AS `legalUsers`
ORDER BY `lastName`

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:

// MSSQLQueryBuilderSpec.cfc
function orWhere() {
    // If just a string is returned, we assume the bindings is an empty array ([])
    return {
        sql = "SELECT * FROM [users] WHERE [id] = ? OR [email] = ?",
        bindings = [ 1, "foo" ]
    };
}

// OracleSchemaBuilderSpec.cfc
function boolean() {
    // returns an array since schema builder can execute multiple statements.
    return [ "CREATE TABLE ""USERS"" (""ACTIVE"" NUMBER(1, 0) NOT NULL)" ];
}

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.

Installation

Installation is easy through CommandBox and ForgeBox. Simply type box install qb to get started.

Usage

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.

moduleSettings = {
    qb = {
        defaultGrammar = "MySQLGrammar@qb"
    }
};

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:

var grammar = new qb.models.Grammars.MySQLGrammar();
var builder = new qb.models.Query.QueryBuilder( grammar );

SQL Type Inferral

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:

moduleSettings = {
    qb = {
        defaultGrammar = "MySQLGrammar@qb",
        numericSQLType = "CF_SQL_BIGINT"
    }
};

Integrating With FW/1

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.

Wiring Up With DI/1

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.

this.mappings = {
    "/qb" = expandPath("./subsystems/qb")
};

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.

qb = {
  diLocations = "/qb/models",
  diConfig = {
    loadListener = function( di1 ) {
      di1.declare( "BaseGrammar" ).instanceOf( "qb.models.Query.Grammars.Grammar" ).done()
         .declare( "MySQLGrammar" ).instanceOf( "qb.models.Query.Grammars.MySQLGrammar" ).done()
         .declare( "QueryUtils" ).instanceOf( "qb.models.Query.QueryUtils" ).done()
         .declare( "QueryBuilder" ).instanceOf( "qb.models.Query.QueryBuilder" )
         .withOverrides({
            grammar = di1.getBean( "MySQLGrammar" ),
            utils = di1.getBean( "QueryUtils" ),
            returnFormat = "array"
         })
         .asTransient();
    }
  }
}

Usage In Your FW/1 Application

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().

// Create an instance of qb
builder = getBeanFactory( "qb" ).getBean( "QueryBuilder" );
// Query the database
posts = builder.from( "Posts" ).get();
posts = builder.from( "Posts" ).where( "IsDraft", "=", 0 ).get();

For further instructions on getting started with qb & FW/1, refer to this blog post.

v8.0.0

Where clauses with an OR combinator are now automatically wrapped inside when callbacks

This 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:

qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );   
    } );

Would generate the following SQL:

SELECT *
FROM "users"
WHERE "active" = ?
    AND "username" = ?
    OR "email" = ?

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.

qb.from( "users" )
    .where( "active", 1 )
    .where( function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );
    } );

SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

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.

// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );   
    } );

SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

Grouping is not needed if there is no OR combinator. In these cases no grouping is added.

// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when( url.keyExists( "admin" ), function( q ) {
        q.where( "admin", 1 )
            .whereNotNull( "hireDate" );
    } );

SELECT *
FROM "users"
WHERE "active" = ?
    AND "admin" = ?
    AND "hireDate IS NOT NULL

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.

qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( function( q2 ) {
            q2.where( "username", "LIKE", q & "%" )
                .orWhere( "email", "LIKE", q & "%" );
        } );
    } );

SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

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.

// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when(
        condition = len( url.q ),
        onTrue = function( q ) {
            q.where( "username", "LIKE", q & "%" )
                .orWhere( "email", "LIKE", q & "%" );   
        },
        withoutScoping = true
    );

SELECT *
FROM "users"
WHERE "active" = ?
    AND "username" = ?
    OR "email" = ?

v7.0.0

Lucee 4.5 and Adobe ColdFusion 11 EOL

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 renamed to SqlServerGrammar

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 Parameters Support Removed

Variadic parameter support was the ability to pass any number of arguments to certain methods like select.

qb.select( "name", "email", "createdDate" );

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:

qb.select( [ "name", "email", "createdDate" ] );

defaultGrammar updated to be the full WireBox mapping

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:

moduleSettings = {
    "qb": {
        "defaultGrammar": "MSSQLGrammar@qb"
    }
};

value method argument order changed

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.

public any function value(
    required string column,
    string defaultValue = "",
    boolean throwWhenNotFound = false,
    struct options = {}
);

Some methods renamed 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.

v5.0.0

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.

Specifying A Select Clause

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.

select

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

query.select( [ "fname AS firstName", "age" ] ).from( "users" );

SELECT `fname` AS `firstName`, `age` FROM `users`

distinct

Calling distinct will cause the query to be executed with the DISTINCT keyword.

query.select( "username" ).distinct().from( "users" );

SELECT DISTINCT `username` FROM `users`

addSelect

This method adds the columns passed to it to the currently selected columns.

query.addSelect( [ "fname AS firstName", "age" ] ).from( "users" );

SELECT `fname` AS `firstName`, `age` FROM `users`

selectRaw

A shortcut to use a raw expression in the select clause.

The expression is added to the other already selected columns.

(To learn more about raw and expressions, check out the docs on Raw Expressions.)

query.selectRaw( "YEAR(birthdate) AS birth_year" ).from( "users" );

SELECT YEAR(birthdate) AS birth_year FROM `users`

subSelect

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.

query.subSelect( "last_login_date", function( q ) {
    q.selectRaw( "MAX(created_date)" )
        .from( "logins" )
        .whereColumn( "users.id", "logins.user_id" );
} ) ).from( "users" );

SELECT (
    SELECT MAX(created_date)
    FROM `logins`
    WHERE `users`.`id` = `logins`.`user_id`
) AS `last_login_date`
FROM `users

clearSelect

Clears out the selected columns for a query along with any configured select bindings.

query.from( "users" )
    .select( [ "fname AS firstName", "age" ] )
    .clearSelect();

SELECT * FROM `users`

reselect

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 select can be passed here.

query.from( "users" )
    .select( [ "fname AS firstName", "age" ] )
    .reselect( "username" );

SELECT `username` FROM `users`

reselectRaw

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.

query.from( "users" )
    .select( [ "fname AS firstName", "age" ] )
    .reselectRaw( "YEAR(birthdate) AS birth_year" );

SELECT YEAR(birthdate) AS birth_year FROM `users`

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 method. upsert can update or insert multiple records at once depending on if a column is matched.

• Allow expressions in value and values. Also add a valueRaw and valuesRaw helper method to make that pattern more ergonomic.

• Allow JOIN statements in UPDATE statements. (This is not supported on Oracle.)

• Allow updates with subselects using closures or builder instances.

Bug Fixes

• Better handling of group by and having clauses in pagination queries.

• Allow any value to be returned from aggregates including strings, numbers, and dates.

• Provide default values for sum and count if no records are returned.

• Test in CI with full null 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. (Default options will still be overridden by options passed to each SchemaBuilder method.)

8.5.0

QueryBuilder

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

SchemaBuilder

• Add support for stored computed columns and virtual computed columns.

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 & longtext types for MySQLGrammar.

8.4.5

• Fix limit on 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 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

• Added a dump command to aid in debugging a query while chaining.

8.1.0

• ``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.

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

BREAKING CHANGES

• ``when callbacks now automatically scope and group where clauses when an OR combinator is used.

Other Changes

• Combine clearOrders and orderBy with a new reordermethod.

• Clear current selected columns with clearSelect.

• Combine clearSelect and either select or selectRaw with reselect and 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 in MySQL.

7.9.7

• Return 0 on null aggregates.

7.9.6

• Match type hints to documentation for join 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 method when calling paginate.

7.9.2

• Allow for space-delimited sort directions like column DESC.

• Add helpful message when trying to use a closure with from instead of fromSub.

• ``value and values now work with column formatters.

• Correctly format RETURNING clauses with column formatters 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 and SMALLMONEY data types to SchemaBuilder.

7.7.3

• Fix wrapping of enum types for Postgres.

7.7.2

• Compatibility fix for ACF 2018 and listLast parsing.

• Include current_timestamp default for 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 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.

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)

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)

7.3.14

• Ignore orders in aggregate queries (39e1338)

7.3.13

• Format with cfformat (dc2a9b6)

7.3.12

• Improve column wrapping with trimming (d98a5cb)

• Prefer the parent query over magic methods when the parent query has the exact method. (f9fd8d1)

7.3.9, 7.3.10, 7.3.11

• Switch to using ForgeBox Storage.

7.3.8

• Allow passing query options in to paginate (cdecfb3)

7.3.7

• Fix for inserting null values directly (1de27a6)

7.3.5, 7.3.6

• Use cfformat for automatic formatting (119e434)

• Add a type to the onMissingMethod exception (90d1093)

7.3.4

• Correctly wrap comments in MySQLGrammar.

7.3.2, 7.3.3

• Publish qb apidocs to Ortus API Docs.

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

7.1.0

• Lambdas (arrow functions) are now allowed wherever closures are allowed.

• Add an orderByRaw method.

• Allow for fully-qualified column names (table_name.column.name) in the value and values methods.

7.0.0

BREAKING CHANGES

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

Other Changes

• 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

****

6.4.0

• Allow Expressions (query.raw) in update statements.

Introduction

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

Requirements

• Adobe ColdFusion 2016+

• Lucee 5+

qb supports four major database grammars:

• MySQL (MySQLGrammar)

• Oracle (OracleGrammar)

• Postgres (PostgresGrammar)

• Microsoft SQL Server (SqlServerGrammar)

Discussion & Help

The Box modules discussion group and community can be found here:

https://community.ortussolutions.com/c/box-modules/qb/27

Installation

Installation is easy through CommandBox and ForgeBox. Simply type box install qb to get started.

Code Samples

Compare these two examples:

// Plain old CFML
q = queryExecute("SELECT * FROM users");

// qb
query = wirebox.getInstance('QueryBuilder@qb');
q = query.from('users').get();

The differences become even more stark when we introduce more complexity:

// Plain old CFML
q = queryExecute(
    "SELECT * FROM posts WHERE published_at IS NOT NULL AND author_id IN ?",
    [ { value = '5,10,27', cfsqltype = 'CF_SQL_NUMERIC', list = true } ]
);

// qb
query = wirebox.getInstance('QueryBuilder@qb');
q = query.from('posts')
         .whereNotNull('published_at')
         .whereIn('author_id', [5, 10, 27])
         .get();

With qb you can easily handle setting order by statements before the columns you want or join statements after a where clause:

query = wirebox.getInstance('QueryBuilder@qb');
q = query.from('posts')
         .orderBy('published_at')
         .select('post_id', 'author_id', 'title', 'body')
         .whereLike('author', 'Ja%')
         .join('authors', 'authors.id', '=', 'posts.author_id')
         .get();

// Becomes

q = queryExecute(
    "SELECT post_id, author_id, title, body FROM posts INNER JOIN authors ON authors.id = posts.author_id WHERE author LIKE ? ORDER BY published_at",
    [ { value = 'Ja%', cfsqltype = 'CF_SQL_VARCHAR', list = false, null = false } ]
);

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

Usage

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.

moduleSettings = {
    qb = {
        defaultGrammar = "MySQLGrammar@qb"
    }
};

If you are not using WireBox, just make sure to wire up the Builder object with the correct grammar:

var grammar = new qb.models.Query.Grammars.MySQLGrammar();
var builder = new qb.models.Query.Builder( grammar );

limit

Sets the limit value for the query.

query.from( "users" )
    .limit( 5 );

SELECT *
FROM `users`
LIMIT 5

take

Sets the limit value for the query. Alias for limit.

query.from( "users" )
    .take( 5 );

SELECT *
FROM `users`
LIMIT 5

offset

Sets the offset value for the query.

query.from( "users" )
    .offset( 25 );

SELECT *
FROM `users`
OFFSET 25

forPage

Helper method to calculate the limit and offset given a page number and count per page.

query.from( "users" )
    .forPage( 3, 15 );

SELECT *
FROM `users`
LIMIT 15
OFFSET 30

simplePaginate & paginate

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.

Common Table Expressions (CTEs) are powerful SQL concept that allow you to create re-usable temporal result sets, which can be referenced as a table within your SQL. CTEs are available in many common database engines and are available in latest versions of all of the support grammars.

CTEs come in two basic types:

• Non-recursive — These are statements that do not reference themselves, in simplified terms they are like a derived table that can be referenced by a user-defined name.

• Recursive — Recursive CTEs reference themselves and are generally used for creating hierarchical data—such as creating a parent/child relationship within a table.

While all of the grammars currently support CTEs, there is enough difference between the various databases implementations of CTEs that unless your CTEs are fairly basic, using CTEs within your project will most likely tie your project to a specific database, unless you account for the differences in your code.

However, CTEs are can be extremely useful to solve certain use cases.

To add CTEs to your queries, you have two methods available:

• with() — Allows you to define a non-recursive CTE.

• withRecursive() — Allows you to define a recursive CTE.

with

You can build a CTE using a function:

// qb
query.with( "UserCTE", function ( q ) {
        q
            .select( [ "fName as firstName", "lName as lastName" ] )
            .from( "users" )
            .where( "disabled", 0 );
    } )
    .from( "UserCTE" )
    .get();

WITH `UserCTE` AS (
    SELECT
        `fName` as `firstName`,
        `lName` as `lastName`
    FROM `users`
    WHERE `disabled` = 0
) SELECT * FROM `UserCTE`

Alternatively, you can use a QueryBuilder instance instead of a function:

// qb
var cte = query
    .select( [ "fName as firstName", "lName as lastName" ] )
    .from( "users" )
    .where( "disabled", 0 );

query.with( "UserCTE", cte )
    .from( "UserCTE" )
    .get();

WITH `UserCTE` AS (
    SELECT
        `fName` as `firstName`,
        `lName` as `lastName`
    FROM `users`
    WHERE `disabled` = 0
)
SELECT * FROM `UserCTE`

A single query can reference multiple CTEs:

query.with( "UserCTE", function ( q ) {
        q.select( [ "id", "fName as firstName", "lName as lastName" ] )
            .from( "users" )
            .where( "disabled", 0 );
    } )
    .with( "BlogCTE", function ( q ) {
        q.from( "blogs" )
            .where( "disabled", 0 );
    } )
    .from( "BlogCTE as b" )
    .join( "UserCTE as u", "b.Creator", "u.id" )
    .get();

WITH `UserCTE` AS (
    SELECT
        `id`,
        `fName` as `firstName`,
        `lName` as `lastName`
    FROM `users`
    WHERE `disabled` = 0
),
`BlogCTE` AS (
    SELECT *
    FROM `blogs`
    WHERE `disabled` = 0
)
SELECT *
FROM `BlogCTE` AS `b`
INNER JOIN `UserCTE` AS `u`
ON `b`.`Creator` = `u`.`id`

withRecursive

Here is an example of building a recursive CTE using SQL Server which would return all parent/child rows and show their generation/level depth:

query
.withRecursive( "Hierarchy", function ( q ) {
    q.select( [ "Id", "ParentId", "Name", q.raw( "0 AS [Generation]" ) ] )
        .from( "Sample" )
        .whereNull( "ParentId" )
        // use recursion to join the child rows to their parents
        .unionAll( function ( q ) {
            q.select( [
                    "child.Id",
                    "child.ParentId",
                    "child.Name",
                    q.raw( "[parent].[Generation] + 1" )
                ] )
                .from( "Sample as child" )
                .join( "Hierarchy as parent", "child.ParentId", "parent.Id" );
        } );
    }, [ "Id", "ParentId", "Name", "Generation" ] )
    .from( "Hierarchy" )
    .get();

WITH [Hierarchy] ([Id], [ParentId], [Name], [Generation]) AS (
    SELECT
        [Id],
        [ParentId],
        [Name],
        0 AS [Generation]
    FROM [Sample]
    WHERE [ParentId] IS NULL
    UNION ALL
    SELECT
        [child].[Id],
        [child].[ParentId],
        [child].[Name],

[parent].[Generation] + 1
    FROM [Sample] AS [child]
    INNER JOIN [Hierarchy] AS [parent]
        ON [child].[ParentId] = [parent].[Id]
) SELECT * FROM [Hierarchy]

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.

union

Adds a UNION statement to the query.

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .union( function ( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 2 );
    } );

SELECT `name`
FROM `users`
WHERE `id` = ?
UNION
SELECT `name`
FROM `users`
WHERE `id` = ?

Adding multiple union statements will append it to the query.

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .union( function ( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 2 );
    } )
    .union( function ( q ) {
        q.from( "users" )
            .select("name")
            .where( "id", 3 );
    } );

SELECT `name`
FROM `users`
WHERE `id` = ?
UNION
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION
SELECT `name`
FROM `users`
WHERE `id` = ?

It can also add union queries as QueryBuilder instances.

var q1 = query.newQuery()
    .from( "users" )
    .select( "name" )
    .where( "id", 2 );
    
var q2 = query.newQuery()
    .from( "users" )
    .select( "name" )
    .where( "id", 3 );

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .union( q1 )
    .union( q2 );

SELECT `name`
FROM `users`
WHERE `id` = ?
UNION
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION
SELECT `name`
FROM `users`
WHERE `id` = ?

unionAll

Adds a UNION ALL statement to the query.

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .unionAll( function( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 2 );
     } );

SELECT `name`
FROM `users`
WHERE `id` = ?
UNION ALL
SELECT `name`
FROM `users`
WHERE `id` = ?

Adding multiple unionAll statements will append it to the query.

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .unionAll( function( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 2 );
     } )
    .unionAll( function( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 3 );
     } );

SELECT `name`
FROM `users`
WHERE `id` = ?
UNION ALL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION ALL
SELECT `name`
FROM `users`
WHERE `id` = ?

It can also add union queries as QueryBuilder instances.

var q1 = query.newQuery()
    .from( "users" )
    .select( "name" )
    .where( "id", 2 );
    
var q2 = query.newQuery()
    .from( "users" )
    .select( "name" )
    .where( "id", 3 );

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .unionAll( q1 )
    .unionAll( q2 );

SELECT `name`
FROM `users`
WHERE `id` = ?
UNION ALL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION ALL
SELECT `name`
FROM `users`
WHERE `id` = ?

Custom Parameter Types

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.

query.from( "users" )
    .where( "id", "=", { value = 18, cfsqltype = "CF_SQL_VARCHAR" } );

SELECT *
FROM `users`
WHERE `id` = ?

This can be used when inserting or updating records as well.

query.table( "users" )
    .insert( {
        "id" = { value 1, cfsqltype = "CF_SQL_VARCHAR" },
        "age" = 18,
        "updatedDate" = { value = now(), cfsqltype = "CF_SQL_DATE" }
    } );

INSERT INTO `users`
    (`id`, `age`, `updatedDate`)
VALUES
    (?, ?, ?)

Strict Date Detection

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.

moduleSettings = {
    "qb": {
        "strictDateDetection": true
    }
};

Numeric SQL Type

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.

moduleSettings = {
    "qb": {
        "numericSQLType": "CF_SQL_INTEGER"
    }
};

Automatic Scale Detection

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

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.

getBindings

This method returns the current bindings in order to be used for the query.

query.from( "users" )
    .join( "logins", function( j ) {
        j.on( "users.id", "logins.user_id" );
        j.where( "logins.created_date", ">", dateAdd( "m", -1, "01 Jun 2019" ) );
    } )
    .where( "active", 1 );

[
    { value = "01 May 2019", cfsqltype = "CF_SQL_TIMESTAMP"  },
    { value = 1, cfsqltype = "CF_SQL_NUMERIC" }
]

You can also retrieve the bindings associated to their corresponding types.

getRawBindings

This method returns the current bindings to be used for the query associated to their corresponding types.

query.from( "users" )
    .join( "logins", function( j ) {
        j.on( "users.id", "logins.user_id" );
        j.where( "logins.created_date", ">", dateAdd( "m", -1, "01 Jun 2019" ) );
    } )
    .where( "active", 1 );

{
    "commonTables" = [],
    "select" = [],
    "join" = [
        { value = "01 May 2019", cfsqltype = "CF_SQL_TIMESTAMP"  },
    ],
    "where" = [
        { value = 1, cfsqltype = "CF_SQL_NUMERIC" }
    ],
    "union" = [],
    "insert" = [],
    "insertRaw" = [],
    "update" = []
};

groupBy

Passing a single string will group by that one column.

query.from( "users" )
    .groupBy( "country" );

SELECT *
FROM `users`
GROUP BY `country`

You can also pass a list of column names. A single comma (",") will be used as the delimiter.

query.from( "users" )
    .groupBy( "country,city" );

SELECT *
FROM `users`
GROUP BY `country`, `city`

An array of column names can be provided.

query.from( "users" )
    .groupBy( [ "country", "city" ] );

SELECT *
FROM `users`
GROUP BY `country`, `city`

Calling groupBy multiple times will to the current groups.

query.from( "users" )
    .groupBy( "country" )
    .groupBy( "city" );

SELECT *
FROM `users`
GROUP BY `country`, `city`

An Expression can be passed in place of a column.

query.from( "users" )
    .groupBy( query.raw( "DATE(created_at)" ) );

SELECT *
FROM `users`
GROUP BY DATE(created_at)

having

Adds a having clause to a query.

query.from( "users" )
    .groupBy( "email" )
    .having( "email", ">", 1 );

SELECT *
FROM `users`
GROUP BY `email`
HAVING `email` > ?

Expressions can be used in place of the column or the value.

query.from( "users" )
    .groupBy( "email" )
    .having( query.raw( "COUNT(email)" ), ">", 1 );

SELECT *
FROM `users`
GROUP BY `email`
HAVING COUNT(email) > ?

The orderBy method seems simple but has a lot of depth depending on the type of arguments you pass in.

Order By (String)

query.from( "users" )
    .orderBy( "email" );

SELECT *
FROM `users`
ORDER BY `email` ASC

Calling orderBy multiple times will append to the order list.

query.from( "users" )
    .orderBy( "email" )
    .orderBy( "username", "desc" );

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

You can also provide an Expression.

query.from( "users" )
    .orderBy( query.raw( "DATE(created_at)" ) );

SELECT *
FROM `users`
ORDER BY DATE(created_at)

Order By (List)

query.from( "users" )
    .orderBy( "email|asc,username", "desc" );

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Array of Strings)

query.from( "users" )
    .orderBy( [ "email|asc", "username" ], "desc" );

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Array of Structs)

query.from( "users" )
    .orderBy( [
        { "column": "email", "direction": "asc" },
        "username"
    ], "desc" );

SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Subquery)

You can order with a subquery using either a function or a QueryBuilder instance.

query.from( "users" )
    .orderBy( function( q ) {
        q.selectRaw( "MAX(created_date)" )
            .from( "logins" )
            .whereColumn( "users.id", "logins.user_id" );
    } );

SELECT *
FROM `users`
ORDER BY (
    SELECT MAX(created_date)
    FROM `logins`
    WHERE `users`.`id` = `logins`.`user_id`
)

Order By Raw

query.from( "users" )
    .orderByRaw( "CASE WHEN status = ? THEN 1 ELSE 0 END DESC", [ 1 ] );

SELECT *
FROM `users`
ORDER BY CASE WHEN status = ? THEN 1 ELSE 0 END DESC

clearOrders

Clears the currently configured orders for the query. Usually used by downstream libraries like Quick.

query.from( "users" )
    .orderBy( "email" )
    .clearOrders();

SELECT *
FROM `users`

reorder

Clears the currently configured orders for the query and sets the new orders passed in. Any valid argument to orderBy can be passed here. Usually used by downstream libraries like Quick.

query.from( "users" )
    .orderBy( "email" )
    .reorder( "username" );

SELECT *
FROM `users`
ORDER BY `username` ASC

Where Methods

where

Adds a where clause to a query.

query.from( "users" )
    .where( "active", "=", 1 );

SELECT *
FROM `users`
WHERE `active` = ?

You can also pass an Expression as the value.

query.from( "users" )
    .where( "last_logged_in", ">", query.raw( "NOW()" ) );

SELECT *
FROM `users`
WHERE `last_logged_in` > NOW()

Any of the following operators can be used in a where clause.

When using the "=" constraint, you can use a shortcut and define the value as the second argument.

query.from( "users" )
    .where( "active", 1 );

SELECT *
FROM `users`
WHERE `active` = ?

To group where statements together, pass a function to the where clause as the only parameter.

query.from( "users" )
    .where( function( q ) {
        q.where( "active", 1 )
            .where( "last_logged_in", ">", dateAdd( "ww", -1, now() ) )
    } );

SELECT *
FROM `users`
WHERE (
    `active` = ?
    AND
    `last_logged_in` > ?
)

A Function or QueryBuilder can be used as a subselect expression when passed to value.

query.from( "users" )
    .where( "email", "foo" )
    .orWhere( "id", "=", function( q ) {
        q.select( q.raw( "MAX(id)" ) )
            .from( "users" )
            .where( "email", "bar" );
    } );

SELECT *
FROM `users`
WHERE `email` = ?
  OR `id` = (
    SELECT MAX(id)
    FROM `users`
    WHERE `email` = ?
  )

andWhere

This method is simply an alias for where with the combinator set to "and".

orWhere

This method is simply an alias for where with the combinator set to "or".

whereBetween

Adds a where between clause to the query.

query.from( "users" )
    .whereBetween( "id", 1, 2 );

SELECT *
FROM `users`
WHERE `id` BETWEEN ? AND ?

If a function or QueryBuilder is passed it is used as a subselect expression.

query.from( "users" )
    .whereBetween(
        "id",
        function( q ) {
            q.select( q.raw( "MIN(id)" ) )
                .from( "users" )
                .where( "email", "bar" );
        },
        builder.newQuery()
            .select( builder.raw( "MAX(id)" ) )
            .from( "users" )
            .where( "email", "bar" )
    );

SELECT *
FROM `users`
WHERE `id` BETWEEN (
    SELECT MIN(id)
    FROM `users`
    WHERE `email` = ?
)
AND (
    SELECT MAX(id)
    FROM `users`
    WHERE `email` = ?
)

whereNotBetween

Adds a where not in clause to the query. This behaves identically to the whereBetween method with the negateflag set to true. See the documentation for whereBetween for usage and examples.

whereColumn

Adds a where clause to a query that compares two columns.

query.from( "users" )
    .whereColumn( "first_name", "=", "last_name" );

SELECT *
FROM `users`
WHERE `first_name` = `last_name`

Just as with where, when using "=" as the operator you can use a shorthand passing the second column in as the operator and leaving the second column null.

query.from( "users" )
    .whereColumn( "first_name", "last_name" );

SELECT *
FROM `users`
WHERE `first_name` = `last_name`

Expressions can be passed in place of either column.

query.from( "users" )
    .whereColumn( "first_name", query.raw( "LOWER(first_name)" ) );

SELECT *
FROM `users`
WHERE `first_name` = LOWER(first_name)

whereExists

Adds a where exists clause to the query.

It can be configured with a function.

query.from( "orders" )
    .whereExists( function( q ) {
        q.select( q.raw( 1 ) )
            .from( "products" )
            .whereColumn( "products.id", "orders.id" );
    } );

SELECT *
FROM `orders`
WHERE EXISTS (
    SELECT 1
    FROM `products`
    WHERE `products`.`id` = `orders`.`id`
)

It can also be configured with a QueryBuilder instance.

var existsQuery = query.newQuery()
    .select( q.raw( 1 ) )
    .from( "products" )
    .whereColumn( "products.id", "orders.id" );

query.from( "orders" )
    .whereExists( existsQuery );

SELECT *
FROM `orders`
WHERE EXISTS (
    SELECT 1
    FROM `products`
    WHERE `products`.`id` = `orders`.`id`
)

whereNotExists

Adds a where not in clause to the query. This behaves identically to the whereExists method with the negateflag set to true. See the documentation for whereExists for usage and examples.

whereLike

A shortcut for calling where with "like" set as the operator.

query.from( "users" )
    .whereLike( "username", "J%" );

SELECT *
FROM `users`
WHERE `username` LIKE ?

whereNotLike

A shortcut for calling where with "not like" set as the operator.

query.from( "users" )
    .whereNotLike( "username", "J%" );

SELECT *
FROM `users`
WHERE `username` NOT LIKE ?

whereIn

Adds a where in clause to the query.

The values passed to whereIn can be a single value, a list of values, or an array of values.

query.from( "orders" )
    .whereIn( "id", [ 1, 4, 66 ] );

SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)

If a list of values is passed in, it is converted to an array of values using a single comma (",") delimiter.

query.from( "orders" )
    .whereIn( "id", "1,4,66" );

SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)