Comment on page
Retrieving Results
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`
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)
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:
EntityNotFound
Returns the first matching row for the configured query, just like
first
. If no records are found, it throws an EntityNotFound
exception.QueryBuilder
query.from( "users" ).firstOrFail();
SQL (MySQL)
SELECT * FROM `users`
LIMIT(1)
Name | Type | Required | Default | Description |
column | any | true | | |
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" ) );
Name | Type | Required | Default | Description |
column | string | true | | |
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" );
Name | Type | Required | Default | Description |
column | any | true | | |
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" ) );
Name | Type | Required | Default | Description |
column | string | true | | |
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" );
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
} );
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.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.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.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
.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
.Last modified 6mo ago