Blog

Eric Peterson

December 19, 2017

Spread the word


Share your thoughts

In the CFML scene, there seems to be two names when it comes to data persistance plain ole' `cfquery` and ORM — not much inbetween. Our module highlight today is out to change that.

Meet qb.

qb

qb is a query builder. It allows you to specify SQL commands with a fluent syntax. The builder is then compiled to different database grammars such as MySQL, Oracle, and SQL Server. The result is fluent, readable code that can compile to any database grammar you need. Let's take a quick high-level overview of the syntax:

Selects, Joins, and Wheres

Query methods can be specified in any order — qb will take care of putting them in the right order for you. You end with a call to get to execute the query.

query.from( "posts" )
    .select( "post_id", "author_id", "title", "body" )
    .join( "authors", "authors.id", "=", "posts.author_id" )
    .whereLike( "authors.name", "Ja%" )
    .orderBy( "posts.published_at" )
    .get();

/*
  SELECT `post_id`,
         `author_id`,
         `title`,
         `body`
    FROM `posts`
    JOIN `authors`
      ON `authors`.`id` = `posts`.`author_id`
   WHERE `authors`.`name` LIKE 'Ja%'
ORDER BY `posts`.`published_at`
*/

Inserts, Updates, and Deletes

Inserts, updates, and deletes use the same methods to restrict the data set. The only difference is the method called at the end of the builder chain — insert, update, or delete instead of get.

query.from( "posts" )
    .whereId( 1 )
    .update( {
        "title" = "New Title",
        "updated_at" = now()
    } );

/*
UPDATE `posts`
   SET `title` = 'New Title',
       `updated_at` = '2017-12-19 08:00:   00'
 WHERE `id` = 1
*/

Aggregates

qb provides helpers for common aggregate functions on data sets. As usual, use any other qb methods to shape the data set before getting the aggregate.

query.from( "users" ).count();

/*
SELECT COUNT(*)
  FROM `users`
*/

query.from( "orders" )
    .whereBetween("created_date", dateAdd( "d", -7, now() ), now() )
    .sum( "total" );

/*
SELECT SUM(`total`)
  FROM `orders`
 WHERE `created_date` BETWEEN '2017-12-12 08:00:   00' AND '2017-12-19 08:00:   00'
*/

Interception Points

qb supports two interception points — preQBExecute and postQBExecute. Use it to log or modify the sql as you need.

Wrap Up

qb enables a slew of new patterns. Check out an example of one in this gist. Be sure to check out all the examples in the official docs to harness the raw power qb offers.

Add Your Comment

Recent Entries

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
Want native Redis Support in Lucee/CFML? Ortus Redis Extension v2.0.0 Released!

Want native Redis Support in Lucee/CFML? Ortus Redis Extension v2.0.0 Released!

We are very excited to bring you another release for our Redis Lucee Extension. This is a major release spawning research of over 9 months. We have done great strides in this new extension to not only bring you the best scaling capabilities via Redis, but also now to introduce to the CFML world: Real-Time messaging with Redis Pub/Sub! Here is our full release notes in our brand new documentation book: What's New With 2.0.0!

Luis Majano
Luis Majano
August 05, 2022