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 March Newsletter

Ortus March Newsletter

Welcome to Ortus Solutions’ monthly roundup, where we're thrilled to showcase cutting-edge advancements, product updates, and exciting events! Join us as we delve into the latest innovations shaping the future of technology.

Maria Jose Herrera
Maria Jose Herrera
April 01, 2024
Into the Box 2024 Last Early Bird Days!

Into the Box 2024 Last Early Bird Days!

Time is ticking, with less than 60 days remaining until the excitement of Into the Box 2024 unfolds! Don't let this golden opportunity slip away; our exclusive Early Bird Pricing is here for a limited time only, available until March 31st. Why wait? Secure your seat now and take advantage of this steal!

Maria Jose Herrera
Maria Jose Herrera
March 20, 2024
Ortus February Newsletter 2024

Ortus February Newsletter 2024

Welcome to Ortus Solutions’ monthly roundup, where we're thrilled to showcase cutting-edge advancements, product updates, and exciting events! Join us as we delve into the latest innovations shaping the future of technology.

Maria Jose Herrera
Maria Jose Herrera
March 06, 2024