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

Ortus Content Digest for week of August 19th

Ortus Content Digest for week of August 19th

It's August 19th... Into the Box is approaching fast, what has Ortus been publishing this week? We have the CFML News Podcast, some CFCasts and YouTube Videos, lots of Ortus and ITB Blog Posts. We have a lot more planned for next week as well.

Gavin Pickin
Gavin Pickin
August 19, 2022
Integrating ColdBox with Existing Code Series 5: Using Wirebox

Integrating ColdBox with Existing Code Series 5: Using Wirebox

Recently, I did a webinar on Refactoring Legacy Code and the question came up about whether or not it was possible to use ColdBox with existing code without converting everything to a ColdBox module or making changes to the existing codebase. In the first installation in this series, we took a tour of the various elements which make up ColdBox. In the second installation, we looked at creating layouts, views, and routes in the main site. In the third, we created a module and did the first integration of our existing code into our ColdBox site. In the fourth, we continued developing our module with a handler and passing variable back to Coldbox. Now we'll use Wirebox with and without ColdBox Modules to see how these approaches differ from a traditional approach.

Dan Card
Dan Card
August 18, 2022
Ortus Content Digest for week of August 12th

Ortus Content Digest for week of August 12th

It's August 12th... what has Ortus been publishing this week? We have the CFML News Podcast, some CFCasts and YouTube Videos, lots of Ortus and ITB Blog Posts. We have a lot more planned for next week as well.

Gavin Pickin
Gavin Pickin
August 12, 2022