qb 8.0.0 was released this past week, and it brings with it a small handful of new features. While this is technically a major release, I don't expect anyone to actually have a breaking change. In fact, I expect this will save you time and headaches as it has for me.

You can also explore these new features and improvements in a new video series on CFCasts!

What's New?

when callbacks are now automatically grouped when using OR constraints

Previously, when using the when control flow function, you were fully responsible for the wrapping of your where statements. For example, the following query:

qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );   
    } );

Would generate the following SQL:

SELECT *
FROM "users"
WHERE "active" = ?
    AND "username" = ?
    OR "email" = ?

The problem with this statement is that the OR can short circuit the active check.

The fix is to wrap the LIKE statements in parenthesis. This is done in qb using a function callback to where:

qb.from( "users" )
    .where( "active", 1 )
    .where( function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );
    } );

SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

When using the when control flow function, it was easy to miss this. This is because you are already in a closure - it looks the same as when using where to group the clauses.

In qb 8.0.0, when will automatically group added where clauses when needed. That means our original example now produces the SQL we probably expected:

// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );   
    } );

SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

Grouping is not needed if there is no OR combinator. In these cases no grouping is added.

// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when( url.keyExists( "admin" ), function( q ) {
        q.where( "admin", 1 )
            .whereNotNull( "hireDate" );
    } );

SELECT *
FROM "users"
WHERE "active" = ?
    AND "admin" = ?
    AND "hireDate IS NOT NULL

If you had already wrapped your expression in a group inside the when callback, nothing changes. Your code works as before. The OR combinator check only works on the top most level of added where clauses.

qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( function( q2 ) {
            q2.where( "username", "LIKE", q & "%" )
                .orWhere( "email", "LIKE", q & "%" );
        } );
    } );

SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

Additionally, if you do not add any where clauses inside a when callback, nothing changes from qb 7.

The breaking change part is if you were relying on these statements residing at the same level without grouping. In those cases, you may pass the withoutScoping flag to the when callback:

// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when(
        condition = len( url.q ),
        onTrue = function( q ) {
            q.where( "username", "LIKE", q & "%" )
                .orWhere( "email", "LIKE", q & "%" );   
        },
        withoutScoping = true
    );

SELECT *
FROM "users"
WHERE "active" = ?
    AND "username" = ?
    OR "email" = ?

reorder

The clearOrders method was introduced in qb 7. It allowed you to easily clear any existing ORDER BY statements for a query. In practice, this was almost always followed up with another call to orderBy to add new statements. In qb 8, these methods are combined using the reorder method.

qb.from( "users" )
    .orderBy( "username" )
	.reorder( [ "lastName", "firstName" ] );

SELECT *
FROM "users"
ORDER BY "lastName" ASC, "firstName" ASC

clearSelect, reselect, and reselectRaw

A trio of select helpers were added in qb 8. They mirror the order by enhancements discussed above.

If you wanted to clear out all of the selected columns in qb 7, you did it by calling select() with no arguments. That seemed kind of strange and unintuitive. In qb 8, you can use the clearSelect method to do that.

qb.from( "users" )
    .select( "username" )
	.clearSelect();

SELECT *
FROM "users"

You can immediately combine this with another select using reselect.

qb.from( "users" )
    .select( "username" )
	.reselect( [ "firstName", "lastName" ] );

SELECT "firstName", "lastName"
FROM "users"

You can also reselect raw expressions using reselectRaw.

qb.from( "users" )
    .select( "username" )
	.reselectRaw( "COUNT(*)" );

SELECT COUNT(*)
FROM "users"

Wrap Up

That's the new features in qb 8. Don't let the major version number scare you — this is a quick and helpful upgrade to your query-building life!