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)

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

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.

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

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