Blog

qb 8.0.0 Released

Eric Peterson July 31, 2020

Spread the word

Eric Peterson

July 31, 2020

Spread the word


Share your thoughts

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!

Add Your Comment

Recent Entries

CBSecurity 3.x Released

CBSecurity 3.x Released

We are incredibly excited to release CBSecurity 3. This is a significant release with over six months of work invested in it. We have completely revamped our security module to make ColdBox applications secure, flexible, and ready for the upcoming ColdBox 7 release. The first major announcement for this release is that we have a brand-new logo!

Luis Majano
Luis Majano
January 30, 2023
CBWIRE 2.2 Released

CBWIRE 2.2 Released

We're excited to announce the release of CBWIRE 2.2. This release includes several added enhancements, such as new lifecycle hooks and simplified Turbo Drive integration. We changed the previous lifecycle hook of mount() to onMount() to be consistent, and several bugs were squashed.

Maria Jose Herrera
Maria Jose Herrera
January 09, 2023
FORGEBOX v7.1.0 Released

FORGEBOX v7.1.0 Released

We are so happy to announce a minor release for our package management system. In this release, we take care of some bug fixes reported and a lot of work for our elastic search engine. Now better search results with an improved response time. We have updated our core modules to the latest versions for better security and performance.

Javier Quintero
Javier Quintero
December 30, 2022