Retrieving Results

get

Name	Type	Required	Default	Description
columns	string | array	false		A shortcut parameter to retrieve only these columns overriding any columns previously set on the QueryBuilder.
options	struct	false	{}	Any additional queryExecute options.

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

Name	Type	Required	Default	Description
options	struct	false	{}	Any additional queryExecute options.

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

Name	Type	Required	Default	Description
errorMessage	string	false		An optional string error message or callback to produce a string error message. If a callback is used, it is passed the QueryBuilder instance as the only argument.
options	struct	false	{}	Any additional queryExecute options.

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
id	any	true		The id value to look up.
idColumn	string	false	"id"	The name of the id column to constrain.
options	struct	false	{}	Any additional queryExecute options.

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
id	any	true		The id value to look up.
idColumn	string	false	"id"	The name of the id column to constrain.
options	struct	false	{}	Any additional queryExecute options.

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

Name	Type	Required	Default	Description
column	any	true		The name of the column to retrieve or an Expression to retrieve.
options	struct	false	{}	Any additional queryExecute options.

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

Name	Type	Required	Default	Description
column	string	true		The sql to use as an Expression.
options	struct	false	{}	Any additional queryExecute options.

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

Name	Type	Required	Default	Description
column	any	true		The name of the column to retrieve or an Expression to retrieve.
defaultValue	string	false	(empty string)	The default value returned if there are no records returned for the query.
throwWhenNotFound	boolean	false	false	If true, it throws a RecordCountException if no records are returned from the query.
options	struct	false	{}	Any additional queryExecute options.

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

Name	Type	Required	Default	Description
column	string	true		The sql to use as an Expression.
defaultValue	string	false	(empty string)	The default value returned if there are no records returned for the query.
throwWhenNotFound	boolean	false	false	If true, it throws a RecordCountException if no records are returned from the query.
options	struct	false	{}	Any additional queryExecute options.

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

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

chunk

Name	Type	Default	Description
max	numeric		The number of results to return in each chunk.
callback	Function		The function that will be called with each chunk.
options	struct	{}	Any additional queryExecute options.

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

Name	Type	Required	Default	Description
page	numeric	false	1	The page number to retrieve.
maxRows	numeric	false	25	The number of records per page. If a number less than 0 is passed, 0 is used instead.
options	struct	false	{}	Any additional queryExecute options.

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

Name	Type	Required	Default	Description
page	numeric	false	1	The page number to retrieve.
maxRows	numeric	false	25	The number of records per page. If a number less than 0 is passed, 0 is used instead.
options	struct	false	{}	Any additional queryExecute options.

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": [ { /* ... */ }, ]
}

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.

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

Name	Type	Description
totalRecords	numeric	The total records count.
results	any	The results of the query execution. It will be passed as whatever return format the user has defined.
page	numeric	The current page number.
maxRows	numeric	The maximum number of rows retrieved per page.

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

Name	Type	Description
results	any	The results of the query execution. It will be passed as whatever return format the user has defined.
page	numeric	The current page number.
maxRows	numeric	The maximum number of rows retrieved per page.

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