1 of 4

Executing Queries

Retrieving Results

get

The get method is the most common method used for retrieving results. It executes using the configured QueryBuilder and returns the results.

QueryBuilder

query.from( "users" ).get();

SQL (MySQL)

SELECT * FROM `users`

get can also take a list or array of columns to use as a shortcut. If any are passed, those columns will be used instead of any columns previously set on the QueryBuilder.

QueryBuilder

query.from( "users" ).get( [ "id", "name" ] );

SQL (MySQL)

SELECT `id`, `name` FROM `users`

first

If you just need to retrieve a single row from the database table, you may use the first method. This method will return a single record (a Struct by default). If no row is found an empty Struct will be returned by default.

QueryBuilder

query.from( "users" ).first();

SQL (MySQL)

SELECT * FROM `users`
 LIMIT(1)

firstOrFail

throws: RecordNotFound

Returns the first matching row for the configured query, just like first. If no records are found, it throws an RecordNotFound exception.

QueryBuilder

query.from( "users" ).firstOrFail();

SQL (MySQL)

SELECT * FROM `users`
 LIMIT(1)

find

Adds an id constraint to the query and returns the first record from the query.

QueryBuilder

query.from( "users" ).find( 1 );

SQL (MySQL)

SELECT * FROM `users`
WHERE `id` = ?
LIMIT(1)

findOrFail

Throws: RecordNotFound

Adds an id constraint to the query and returns the first record from the query. If no record is found, it throws an RecordNotFound exception.

QueryBuilder

query.from( "users" ).find( 415015 );

SQL (MySQL)

SELECT * FROM `users`
WHERE `id` = ?
LIMIT(1)

values

If you don't even need an entire row, you may extract a single value from each record using the values method. The values method will return the column of your choosing as a simple array.

QueryBuilder

query.from( "users" ).values( "firstName" );

Result

[ "jon", "jane", "jill", ... ]

An expression can also be passed to values:

qb.from( "users" ).values( qb.raw( "CONCAT(fname, ' ', lname) AS fullName" ) );

The valuesRaw function can make this pattern more ergonomic.

valuesRaw

The values method will return the expression given for each row as a simple array.

query.from( "users" ).valuesRaw( "CONCAT(fname, ' ', lname) AS fullName" );

value

This method is similar to values except it only returns a single, simple value. Where values calls get under the hood, this method calls first.

QueryBuilder

query.from( "users" ).value( "firstName" );

Result

"jon"

If no records are returned from the query, one of two things will happen. If the throwWhenNotFound boolean is set to true, a RecordCountException will be thrown. Otherwise the defaultValue provided to the method will be returned.

An expression can also be passed to value:

qb.from( "users" ).value( qb.raw( "CONCAT(fname, ' ', lname) AS fullName" ) );

The valueRaw function can make this pattern more ergonomic.

valueRaw

The value method will return the expression given for the first row found.

query.from( "users" ).valueRaw( "CONCAT(fname, ' ', lname) AS fullName" );

chunk

Large datasets can be broken up and retrieved in chunks. This allows you to work with a subset of results at once to keep your memory footprint under control.

chunk can be called on any query like you would call get. You can stop the retrieving and processing early by returning false from the callback.

QueryBuilder

query.from( "users" ).chunk( 100, function( users ) {
    // Process the users here
    // Returning false from the callback stops processing
} );

paginate

Generates a pagination struct along with the results of the executed query. It does this by calling both count and forPage.

QueryBuilder

query.from( "users" )
    .paginate();

Results

{
    "pagination": {
        "maxRows": 25,
        "offset": 0,
        "page": 1,
        "totalPages": 2,
        "totalRecords": 45
    },
    "results": [ { /* ... */ }, ]
}

The behavior when a maxRows of 0 or lower is passed is determined by the shouldMaxRowsOverrideToAll callback function. The default callback returns all rows for values <= 0. You can customize this behavior by passing a new callback to the shouldMaxRowsOverrideToAll setting or init argument.

simplePaginate

Generates a simple pagination struct along with the results of the executed query. It does so without getting a count of the number of records the query would return. This can be desirable for performance reasons if your query count is rather large. It instead determines if there are more records by asking for one more row that your specified maxRows. If the number of rows returned exceeds your specified maxRows then the pagination returns hasMore: true. The results will always contain your specified maxRows (or less, if there aren't enough records).

QueryBuilder

query.from( "users" )
    .simplePaginate();

Results

{
    "pagination": {
        "maxRows": 25,
        "offset": 0,
        "page": 1,
        "hasMore": true
    },
    "results": [ { /* ... */ }, ]
}

Custom Pagination Collectors

A pagination collector is the name given to the struct returned from calling the paginate method. It can be a struct or a component. It needs one function defined and will be passed the following parameters.

generateWithResults

You can set your custom pagination collector either in the constructor using the paginationCollector argument or by calling setPaginationCollector on a query builder instance.

By default, qb ships with cbpaginator as its pagination collector. The return format of cbpaginator is the example shown above.

In qb 8.4.0 the simplePaginate method was added. This uses a new method on the paginationCollector.

generateSimpleWithResults

If you use a custom paginationCollector, ensure it has been updated with this new generateSimpleWithResults method before calling simplePaginate.

Aggregates

The query builder also provides a variety of aggregate methods such as count, max, min, and sum. These methods take the headache out of setting up these common aggregate functions.

When executing any of the aggregate functions, any where restrictions on your query will still be applied.

Instead of returning a query, these methods return a simple value.

exists

Returns true if the query returns any rows. Returns false otherwise.

QueryBuilder

query.from( "users" ).where( "username", "like", "jon%" ).exists();

SQL (MySQL)

SELECT COUNT(*) AS aggregate FROM `users` WHERE `username` LIKE 'jon%'

existsOrFail

Name

Type

Required

Default

Description

Returns true if the query returns any rows. Throws a RecordNotFound exception otherwise.

QueryBuilder

query.from( "users" ).where( "username", "like", "jon%" ).existsOrFail();

SQL (MySQL)

SELECT COUNT(*) AS aggregate FROM `users` WHERE `username` LIKE 'jon%'

count

Returns an integer number of rows returned by the query.

QueryBuilder

query.from( "users" ).count();

SELECT COUNT(*) AS aggregate FROM `users`

SELECT COUNT(*) FROM [users]

max

Returns the maximum value for the given column.

QueryBuilder

query.from( "users" ).max( "age" );

SQL (MySQL)

SELECT MAX(age) AS aggregate FROM `users`

min

Returns the minimum value for the given column.

QueryBuilder

query.from( "users" ).min( "age" );

SQL (MySQL)

SELECT MIN(age) AS aggregate FROM `users`

sum

Returns the sum of all returned rows for the given column.

QueryBuilder

query.from( "employees" ).sum( "salary" );

SQL (MySQL)

SELECT SUM(salary) AS aggregate FROM `employees`

sumRaw

Returns the sum of all returned rows for the expression.

QueryBuilder

query.from( "accounts" ).sumRaw( "netAdditions + netTransfers" )

SQL (MySQL)

SELECT SUM(netAdditions + netTransfers) AS aggregate FROM `accounts`

columnList

Retrieves the columns for the configured table.

QueryBuilder

query.from( "users" ).columnList();

Result

[ "id", "firstName", "lastName", "username", "email", "password" ]

Retrieving Results

get

The get method is the most common method used for retrieving results. It executes using the configured QueryBuilder and returns the results.

QueryBuilder

query.from( "users" ).get();

SQL (MySQL)

SELECT * FROM `users`

get can also take a list or array of columns to use as a shortcut. If any are passed, those columns will be used instead of any columns previously set on the QueryBuilder.

QueryBuilder

query.from( "users" ).get( [ "id", "name" ] );

SQL (MySQL)

SELECT `id`, `name` FROM `users`

first

QueryBuilder

query.from( "users" ).first();

SQL (MySQL)

SELECT * FROM `users`
 LIMIT(1)

firstOrFail

Name

Type

Required

Default

Description

throws: RecordNotFound

Returns the first matching row for the configured query, just like first. If no records are found, it throws an RecordNotFound exception.

QueryBuilder

query.from( "users" ).firstOrFail();

SQL (MySQL)

SELECT * FROM `users`
 LIMIT(1)

find

Name

Type

Required

Default

Description

Adds an id constraint to the query and returns the first record from the query.

QueryBuilder

query.from( "users" ).find( 1 );

SQL (MySQL)

SELECT * FROM `users`
WHERE `id` = ?
LIMIT(1)

findOrFail

Name

Type

Required

Default

Description

Throws: RecordNotFound

Adds an id constraint to the query and returns the first record from the query. If no record is found, it throws an RecordNotFound exception.

QueryBuilder

query.from( "users" ).find( 415015 );

SQL (MySQL)

SELECT * FROM `users`
WHERE `id` = ?
LIMIT(1)

values

If you don't even need an entire row, you may extract a single value from each record using the values method. The values method will return the column of your choosing as a simple array.

QueryBuilder

query.from( "users" ).values( "firstName" );

Result

[ "jon", "jane", "jill", ... ]

An expression can also be passed to values:

qb.from( "users" ).values( qb.raw( "CONCAT(fname, ' ', lname) AS fullName" ) );

The valuesRaw function can make this pattern more ergonomic.

valuesRaw

The values method will return the expression given for each row as a simple array.

query.from( "users" ).valuesRaw( "CONCAT(fname, ' ', lname) AS fullName" );

value

This method is similar to values except it only returns a single, simple value. Where values calls get under the hood, this method calls first.

QueryBuilder

query.from( "users" ).value( "firstName" );

Result

"jon"

An expression can also be passed to value:

qb.from( "users" ).value( qb.raw( "CONCAT(fname, ' ', lname) AS fullName" ) );

The valueRaw function can make this pattern more ergonomic.

valueRaw

The value method will return the expression given for the first row found.

query.from( "users" ).valueRaw( "CONCAT(fname, ' ', lname) AS fullName" );

chunk

Large datasets can be broken up and retrieved in chunks. This allows you to work with a subset of results at once to keep your memory footprint under control.

chunk can be called on any query like you would call get. You can stop the retrieving and processing early by returning false from the callback.

QueryBuilder

query.from( "users" ).chunk( 100, function( users ) {
    // Process the users here
    // Returning false from the callback stops processing
} );

paginate

Generates a pagination struct along with the results of the executed query. It does this by calling both count and forPage.

QueryBuilder

query.from( "users" )
    .paginate();

Results

{
    "pagination": {
        "maxRows": 25,
        "offset": 0,
        "page": 1,
        "totalPages": 2,
        "totalRecords": 45
    },
    "results": [ { /* ... */ }, ]
}

simplePaginate

QueryBuilder

query.from( "users" )
    .simplePaginate();

Results

{
    "pagination": {
        "maxRows": 25,
        "offset": 0,
        "page": 1,
        "hasMore": true
    },
    "results": [ { /* ... */ }, ]
}

Custom Pagination Collectors

generateWithResults

You can set your custom pagination collector either in the constructor using the paginationCollector argument or by calling setPaginationCollector on a query builder instance.

By default, qb ships with cbpaginator as its pagination collector. The return format of cbpaginator is the example shown above.

In qb 8.4.0 the simplePaginate method was added. This uses a new method on the paginationCollector.

generateSimpleWithResults

If you use a custom paginationCollector, ensure it has been updated with this new generateSimpleWithResults method before calling simplePaginate.

Inserts, Updates, and Deletes

The following methods all have the same return value:

{
    "result": "Value of the `result` parameter to `queryExecute`",
    "query": "Return value of running `queryExecute` - a CFML query object"
}

insert, update, and delete actions always return a query object for query, regardless of your configured returnFormat.

insert

Name

Type

Required

Default

Description

This call must come after setting the query's table using from or table.

You can insert a single record by passing a struct:

QueryBuilder

query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "robert@test.com",
        "age" = 55
    } );

MySQL

INSERT INTO `users` (`age`, `email`, `name`)
VALUES (?, ?, ?)

You can specify any query param options such as the SQL type by passing a struct with the parameters you would pass to cfqueryparam.

QueryBuilder

query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "robert@test.com",
        "age" = { value = 55, cfsqltype = "CF_SQL_INTEGER" }
    } );

MySQL

INSERT INTO `users` (`age`, `email`, `name`)
VALUES (?, ?, ?)

Raw values can be supplied to an insert statement.

QueryBuilder

query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "robert@test.com",
        "updatedDate" = query.raw( "NOW()" )
    } );

MySQL

INSERT INTO `users` (`age`, `email`, `updatedDate`)
VALUES (?, ?, NOW())

Multiple rows can be inserted in a batch by passing an array of structs to insert.

This is not the same as looping over and array and calling insert in the loop. Using an array with insert will batch the inserts in one SQL call. Looping over an array and calling insert each time will create a SQL request for each item in the array. Bottom line, pass your array to insert!

QueryBuilder

query.from( "users" ).insert( [
    { "email" = "john@example.com", "name" = "John Doe" },
    { "email" = "jane@example.com", "name" = "Jane Doe" }
] );

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

INSERT ALL
INTO "USERS" ("EMAIL", "NAME") VALUES (?, ?)
INTO "USERS" ("EMAIL", "NAME") VALUES (?, ?)
SELECT 1 FROM dual

insertIgnore

Name

Type

Required

Default

Description

This call must come after setting the query's table using from or table.

Inserts data into a table while ignoring duplicate key conflicts.

target is only required for SQLServerGrammar and OracleGrammar

QueryBuilder

query.from( "users" )
    .insertIgnore(
        values = [
            { "email" = "foo", "name" = "bar" },
            { "email" = "baz", "name" = "bam" }
        ],
        target = [ "email" ]
    );

INSERT IGNORE INTO `users` (`email`, `name`)
VALUES (?, ?), (?, ?)

MERGE [users] AS [qb_target]
USING (VALUES (?, ?), (?, ?)) AS [qb_src] ([email], [name])
ON [qb_target].[email] = [qb_src].[email]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([email], [name]) VALUES ([email], [name]);

INSERT INTO "users" ("email", "name")
VALUES (?, ?), (?, ?)
ON CONFLICT DO NOTHING

MERGE INTO "USERS" "QB_TARGET"
USING (SELECT ?, ? FROM dual UNION ALL SELECT ?, ? FROM dual) "QB_SRC"
ON "QB_TARGET"."EMAIL" = "QB_SRC"."EMAIL"
WHEN NOT MATCHED THEN
INSERT ("EMAIL", "NAME")
VALUES ("QB_SRC"."EMAIL", "QB_SRC"."NAME")

insertUsing

Name

Type

Required

Default

Description

This call must come after setting the query's table using from or table.

Inserts data into a table using a subquery as the source.

qb.from( "users" )
    .insertUsing( function( q ) {
        q.from( "activeDirectoryUsers" )
            .select( [ "email", "modifiedDate AS createdDate" ] )
            .where( "active", 1 );
    } );

INSERT INTO `users` (`email`, `createdDate`)
SELECT `email`, `modifiedDate` AS `createdDate`
FROM `activeDirectoryUsers`
WHERE `active` = ?

You can also pass in an array of column names to avoid aliasing in your source query.

qb.from( "users" )
    .insertUsing(
        columns = [ "email", "createdDate" ],
        source = function( q ) {
            q.from( "activeDirectoryUsers" )
                 .select( [ "email", "modifiedDate" ] )
                 .where( "active", 1 );
        }
    );

INSERT INTO `users` (`email`, `createdDate`)
SELECT `email`, `modifiedDate`
FROM `activeDirectoryUsers`
WHERE `active` = ?

Alternatively, the source can be defined as a QueryBuilder object:

qb.from( "users" )
    .insertUsing(
        qb.newQuery()
            .from( "activeDirectoryUsers" )
            .select( [ "email", "modifiedDate AS createdDate" ] )
            .where( "active", 1 )
    );

INSERT INTO `users` (`email`, `createdDate`)
SELECT `email`, `modifiedDate` AS `createdDate`
FROM `activeDirectoryUsers`
WHERE `active` = ?

update

This call must come after setting the query's table using from or table.

Updates a table with a struct of column and value pairs.

QueryBuilder

query.from( "users" )
    .update( {
        "email" = "foo",
        "name" = "bar"
    } );

MySQL

UPDATE `users`
SET `email` = ?,
    `name` = ?

You can specify any query param options such as the SQL type by passing a struct with the parameters you would pass to cfqueryparam.

QueryBuilder

query.from( "users" )
    .update( {
        "email" = "foo",
        "name" = "bar",
        "updatedDate" = { value = now(), cfsqltype = "CF_SQL_TIMESTAMP" }
    } );

MySQL

UPDATE `users`
SET `email` = ?,
    `name` = ?,
    `updatedDate` = ?

Any constraining of the update query should be done using the appropriate WHERE statement before calling update.

QueryBuilder

query.from( "users" )
    .whereId( 1 )
    .update( {
        "email" = "foo",
        "name" = "bar"
    } );

MySQL

UPDATE `users`
SET `email` = ?,
    `name` = ?
WHERE `Id` = ?

You can update a column based on another column using a raw expression.

QueryBuilder

query.from( "hits" )
    .where( "page", "someUrl" )
    .update( {
        "count" = query.raw( "count + 1" )
    } );

MySQL

UPDATE `hits`
SET `count` = count + 1
WHERE `page` = ?

Updating Null values

Null values can be inserted by using queryparam syntax:

query.from("user")
		.whereId( 10 )
		.update( {
			manager_FK = { value = "", null=true },
		} )

if you are using full null support the following (easier) syntax is also allowed:

query.from("user")
		.whereId( 10 )
		.update( {
			manager_FK = { value = null },
		} )

Updating with Subselects

Subselects can be used to update values by passing a closure as the value

qb.table( "employees" )
    .update( {
		    "departmentName" = function( q ) {
		        q.from( "departments" )
		            .select( "name" )
		            .whereColumn( "employees.departmentId", "departments.id" );
		    } )
		} );

UPDATE `employees`
SET `departmentName` = (
    SELECT `name`
    FROM `departments`
    WHERE `employees`.`departmentId` = `departments`.`id`
)

You can also pass a builder instance in place of the closure.

qb.table( "employees" )
    .update( {
		    "departmentName" = qb.newQuery()
		        .from( "departments" )
		        .select( "name" )
		        .whereColumn( "employees.departmentId", "departments.id" )
		    } )
		} );

Updating with Joins

qb will correctly format JOIN clauses in your UPDATE statements for your database grammar.

OracleGrammar does not support JOIN clauses inUPDATE statements. Consider using subselects in your UPDATE statement instead.

qb.table( "employees" )
    .join( "departments", "departments.id", "employees.departmentId" )
    .update( {
        "employees.departmentName": qb.raw( "departments.name" )
    } );

UPDATE `employees`
INNER JOIN `departments`
    ON `departments`.`id` = `employees`.`departmentId`
SET `employees`.`departmentName` = departments.name

UPDATE [employees]
SET [employees].[departmentName] = departments.name
FROM [employees]
INNER JOIN [departments]
    ON [departments].[id] = [employees].[departmentId]

UPDATE "employees"
SET "employees"."departmentName" = departments.name
FROM "departments"
WHERE "departments"."id" = "employees"."departmentId"

addUpdate

Adds values to a later update, similar to addSelect.

QueryBuilder

query.from( "users" )
    .whereId( 1 )
    .addUpdate( {
        "email" = "foo",
        "name" = "bar"
    } )
    .when( true, function( q ) {
        q.addUpdate( {
            "foo": "yes"
        } );
    } )
    .when( false, function( q ) {
        q.addUpdate( {
            "bar": "no"
        } );
    } )
    .update();

MySQL

UPDATE `users`
SET `email` = ?,
    `foo` = ?,
    `name` = ?
WHERE `Id` = ?

updateOrInsert

Performs an update statement if the configured query returns true for exists. Otherwise, performs an insert statement.

If an update statement is performed qb applies a limit( 1 ) to the update statement.

QueryBuilder

query.from( "users" )
    .where( "email", "foo" )
    .updateOrInsert( {
        "email" = "foo",
        "name" = "baz"
    } );

MySQL

UPDATE `users`
SET `email` = ?,
    `name` = ?
WHERE `email` = ?
LIMIT 1

If the configured query returns 0 records, then an insert statement is performed.

QueryBuilder

query.from( "users" )
    .where( "email", "foo" )
    .updateOrInsert( {
        "email" = "foo",
        "name" = "baz"
    } );

MySQL

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

upsert

An upsert is a batch operation that either inserts or updates a row depending on if a target match is found. If a row is matched with the target column(s), then the matched row is updated. Otherwise a new row is inserted.

In most database grammars, the target columns are required to be primary key or unique indexes.

qb.table( "users" )
    .upsert(
        values = [
            {
                "username": "johndoe",
                "active": 1,
                "createdDate": "2021-09-08 12:00:00",
                "modifiedDate": "2021-09-08 12:00:00"
            },
            {
                "username": "janedoe",
                "active": 1,
                "createdDate": "2021-09-10 10:42:13",
                "modifiedDate": "2021-09-10 10:42:13"
            },
        ],
        target = [ "username" ],
        update = [ "active", "modifiedDate" ],
    );

INSERT INTO `users`
    (`active`, `createdDate`, `modifiedDate`, `username`)
VALUES
    (?, ?, ?, ?),
    (?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
    `active` = VALUES(`active`),
    `modifiedDate` = VALUES(`modifiedDate`)

MERGE [users] AS [qb_target]
USING (VALUES (?, ?, ?, ?), (?, ?, ?, ?)) AS [qb_src]
    ([active], [createdDate], [modifiedDate], [username])
ON [qb_target].[username] = [qb_src].[username]
WHEN MATCHED THEN UPDATE
    SET [active] = [qb_src].[active],
        [modifiedDate] = [qb_src].[modifiedDate]
WHEN NOT MATCHED BY TARGET THEN INSERT
    ([active], [createdDate], [modifiedDate], [username])
    VALUES
    ([active], [createdDate], [modifiedDate], [username])

INSERT INTO "users"
    ("active", "createdDate", "modifiedDate", "username")
VALUES
    (?, ?, ?, ?),
    (? ,? ,? ,?)
ON CONFLICT ("username") DO UPDATE
    "active" = EXCLUDED."active",
    "modifiedDate" = EXCLUDED."modifiedDate"

MERGE INTO "USERS" "QB_TARGET"
USING (
    SELECT ?, ?, ?, ? FROM dual
    UNION ALL
    SELECT ?, ?, ?, ? FROM dual
) "QB_SRC"
ON "QB_TARGET"."USERNAME" = "QB_SRC"."USERNAME"
WHEN MATCHED THEN UPDATE
    SET "ACTIVE" = "QB_SRC"."ACTIVE",
        "MODIFIEDDATE" = "QB_SRC"."MODIFIEDDATE"
WHEN NOT MATCHED THEN INSERT
    ("ACTIVE", "CREATEDDATE", "MODIFIEDDATE", "USERNAME")
    VALUES
    ("QB_SRC"."ACTIVE", "QB_SRC"."CREATEDDATE", "QB_SRC"."MODIFIEDDATE", "QB_SRC"."USERNAME")

The update clause in a upsert can also accept raw values, making it very useful for tracking data like statistics.

qb.table( "stats" )
    .upsert(
        values = [
            { "postId": 1, "viewedDate": "2021-09-08", "views": 1 },
            { "postId": 2, "viewedDate": "2021-09-08", "views": 1 }
        ],
        target = [ "postId", "viewedDate" ],
        update = { "views": qb.raw( "stats.views + 1" ) }
    );

INSERT INTO `stats`
    (`postId`, `viewedDate`, `views`)
VALUES
    (?, ?, ?),
    (?, ?, ?)
ON DUPLICATE KEY UPDATE
    `views` = stats.views + 1

MERGE [stats] AS [qb_target]
USING (VALUES (?, ?, ?), (?, ?, ?)) AS [qb_src]
    ([postId], [viewedDate], [views])
ON [qb_target].[postId] = [qb_src].[postId]
    AND [qb_target].[viewedDate] = [qb_src].[viewedDate]
WHEN MATCHED THEN UPDATE
    SET [views] = stats.views + 1
WHEN NOT MATCHED BY TARGET THEN INSERT
    ([postId], [viewedDate], [views])
    VALUES
    ([postId], [viewedDate], [views])

INSERT INTO "stats"
    ("postId", "viewedDate", "views")
VALUES
    (?, ?, ?),
    (?, ?, ?)
ON CONFLICT ("postId", "viewedDate") DO UPDATE
    "views" = stats.views + 1

MERGE INTO "STATS" "QB_TARGET"
USING (
    SELECT ?, ?, ? FROM dual
    UNION ALL
    SELECT ?, ?, ? FROM dual
) "QB_SRC"
ON "QB_TARGET"."POSTID" = "QB_SRC"."POSTID"
    AND "QB_TARGET"."VIEWEDDATE" = "QB_SRC"."VIEWEDDATE"
WHEN MATCHED THEN UPDATE
    SET "VIEWS" = stats.views + 1
WHEN NOT MATCHED THEN INSERT
    ("POSTID", "VIEWEDDATE", "VIEWS")
    VALUES
    ("QB_SRC"."POSTID", "QB_SRC"."VIEWEDDATE", "QB_SRC"."VIEWS")

A source callback or QueryBuilder instance can be used instead of explicit values. This allows you to do upserts across tables or subqueries.

To do this, provide a source that is either a function to configure a new QueryBuilder instance or an already configured QueryBuilder instance. Then specify the columns that will be affected as an array of strings to values.

qb.table( "stats" )
    .upsert(
        source = function( q ) {
            q.from( "activeDirectoryUsers" )
                .select( [
                    "username",
                    "active",
                    "createdDate",
                    "modifiedDate"
                ] );
        },
        values = [ "username", "active", "createdDate", "modifiedDate" ],
        target = [ "username" ],
        update = [ "active", "modifiedDate" ]
    );

INSERT INTO `users`
    (`username`, `active`, `createdDate`, `modifiedDate`)
SELECT `username`, `active`, `createdDate`, `modifiedDate`
FROM `activeDirectoryUsers`
ON DUPLICATE KEY UPDATE
    `active` = VALUES(`active`),
    `modifiedDate` = VALUES(`modifiedDate`)

MERGE [users] AS [qb_target]
USING (
    SELECT [username], [active], [createdDate], [modifiedDate]
    FROM [activeDirectoryUsers]
) AS [qb_src]
ON [qb_target].[username] = [qb_src].[username]
WHEN MATCHED THEN UPDATE
    SET [active] = [qb_src].[active],
        [modifiedDate] = [qb_src].[modifiedDate]
WHEN NOT MATCHED BY TARGET THEN INSERT
    ([username], [active], [createdDate], [modifiedDate])
VALUES ([username], [active], [createdDate], [modifiedDate]);

INSERT INTO "users"
("username", "active", "createdDate", "modifiedDate")
SELECT "username", "active", "createdDate", "modifiedDate"
FROM "activeDirectoryUsers"
ON CONFLICT ("username") DO UPDATE
    "active" = EXCLUDED."active",
    "modifiedDate" = EXCLUDED."modifiedDate"

MERGE INTO "USERS" "QB_TARGET"
USING (
    SELECT "USERNAME", "ACTIVE", "CREATEDADATE", "MODIFIEDDATE"
    FROM "ACTIVEDIRECTORYUSERS"
) "QB_SRC"
ON "QB_TARGET"."USERNAME" = "QB_SRC"."USERNAME"
WHEN MATCHED THEN UPDATE
    SET "ACTIVE" = "QB_SRC"."ACTIVE",
        "MODIFIEDDATE" = "QB_SRC"."MODIFIEDDATE"
WHEN NOT MATCHED THEN INSERT
    ("USERNAME", "ACTIVE", "CREATEDDATE", "MODIFIEDDATE")
    VALUES
    (
        "QB_SRC"."USERNAME",
        "QB_SRC"."ACTIVE",
        "QB_SRC"."CREATEDDATE",
        "QB_SRC"."MODIFIEDDATE"
    )

delete

Deletes all records that the query returns.

QueryBuilder

query.from( "users" )
    .where( "email", "foo" )
    .delete();

MySQL

DELETE FROM `users`
WHERE `email` = ?

The id argument is a convenience to delete a single record by id.

QueryBuilder

query.from( "users" )
    .delete( 1 );

MySQL

DELETE FROM `users`
WHERE `id` = ?

returning

returning is only supported in PostgresGrammar, SqlServerGrammar, and SQLiteGrammar. Using this method on unsupported grammars will result in an UnsupportedOperation exception. Be aware that using this method constrains your grammar choices.

Specifies columns to be returned from the insert query.

QueryBuilder

query.from( "users" )
    .returning( "id" )
    .insert( {
        "email" = "foo",
        "name" = "bar"
    } );

INSERT INTO [users] ([email], [name])
OUTPUT INSERTED.[id]
VALUES (?, ?)

INSERT INTO "users" ("email", "name")
VALUES (?, ?)
RETURNING "id"

INSERT INTO "users" ("email", "name")
VALUES (?, ?)
RETURNING "id"

The returning function also applies to update and delete calls.

QueryBuilder

query.table( "users" )
    .returning( [ "id", "modifiedDate" ] )
    .where( "id", 1 )
    .update( { "email": "john@example.com" } );

UPDATE [users]
SET [email] = ?
OUTPUT INSERTED.[id], INSERTED.[modifiedDate]
WHERE [id] = ?

UPDATE "users"
SET "email" = ?
WHERE "id" = ?
RETURNING "id", "modifiedDate"

UPDATE "users"
SET "email" = ?
WHERE "id" = ?
RETURNING "id", "modifiedDate"

QueryBuilder

query.table( "users" )
    .returning( "id" )
    .where( "active", 0 )
    .delete();

DELETE FROM [users]
OUTPUT DELETED.[id]
WHERE [active] = ?

DELETE FROM "users" WHERE "active" = ?
RETURNING "id"

DELETE FROM "users" WHERE "active" = ?
RETURNING "id"

You can also use raw Expressions in a returning call. This is especially useful for SQL Server returning both the old and new values from an update call.

QueryBuilder

qb.from( "users" )
    .where( "id", 1 )
    .returningRaw( [
        "DELETED.modifiedDate AS oldModifiedDate",
        "INSERTED.modifiedDate AS newModifiedDate"
    ] )
    .update( { "email": "john@example.com" } );

UPDATE [users]
SET [email] = ?
OUTPUT
    DELETED.modifiedDate AS oldModifiedDate,
    INSERTED.modifiedDate AS newModifiedDate
WHERE [id] = ?