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.

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

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

count

Returns an integer number of rows returned by the query.

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

SELECT COUNT(*) AS aggregate FROM `users`

SELECT COUNT(*) FROM [users]

max

Returns the maximum value for the given column.

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

SELECT MAX(age) AS aggregate FROM `users`

min

Returns the minimum value for the given column.

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

SELECT MIN(age) AS aggregate FROM `users`

sum

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

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

SELECT SUM(salary) AS aggregate FROM `employees`

insert

You can insert a single record by passing a struct:

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

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.

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

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

Raw values can be supplied to an insert statement.

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

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

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

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

returning

Specifies columns to be returned from the insert query.

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"

update

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

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

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.

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

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

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

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

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

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

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

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 Lucee with full null support the following (easier) syntax is also allowed:

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

addUpdate

Adds values to a later update, similar to addSelect.

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

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.

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

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

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

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

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

delete

Deletes all records that the query returns.

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

DELETE FROM `users`
WHERE `email` = ?

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

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

DELETE FROM `users`
WHERE `id` = ?

get

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

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

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.

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

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.

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

SELECT * FROM `users`
 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.

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

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

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.

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

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

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.

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.

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

{
    "pagination": {
        "maxRows": 25,
        "offset": 0,
        "page": 1,
        "totalPages": 2,
        "totalRecords": 45
    },
    "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.