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.
Name | Type | Required | Default | Description |
options | struct | false | {} | Any additional queryExecute options. |
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%'
Name | Type | Required | Default | Description |
column | string | false | "*" | The column on which to count records. |
options | struct | false | {} | Any additional queryExecute options. |
Returns an integer number of rows returned by the query.
QueryBuilder
query.from( "users" ).count();
SQL (MySQL)
SQL Server
SELECT COUNT(*) AS aggregate
FROM `users`
SELECT COUNT(*) FROM [users]
Name | Type | Required | Default | Description |
column | string | true | | The column on which to find the max. |
options | struct | false | {} | Any additional queryExecute options. |
Returns the maximum value for the given column.
QueryBuilder
query.from( "users" ).max( "age" );
SQL (MySQL)
SELECT MAX(age) AS aggregate FROM `users`
Name | Type | Required | Default | Description |
column | string | true | | The column on which to find the min. |
options | struct | false | {} | Any additional queryExecute options. |
Returns the minimum value for the given column.
QueryBuilder
query.from( "users" ).min( "age" );
SQL (MySQL)
SELECT MIN(age) AS aggregate FROM `users`
Name | Type | Required | Default | Description |
column | string | true | | The column to sum. |
options | struct | false | {} | Any additional queryExecute options. |
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`
Name | Type | Required | Default | Description |
column | string | true | | The column to sum. |
options | struct | false | {} | Any additional queryExecute options. |
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`
Name | Type | Required | Default | Description |
asQuery | boolean | false | false | Flag to retrieve the columnList as a query instead of an array. |
datasource | string | false | | Optional datasource to from which to retrieve the columnList. |
Retrieves the columns for the configured table.
QueryBuilder
query.from( "users" ).columnList();
Result
[ "id", "firstName", "lastName", "username", "email", "password" ]
Last modified 4mo ago