LogoLogo
11.1.0
11.1.0
  • Introduction
  • What's New?
  • Installation & Usage
  • Migration Guide
  • Contributing & Filing Issues
  • Query Builder
    • Getting a New Query
    • Building Queries
      • Selects
      • From
      • Joins
      • Wheres
      • Order By
      • Group By and Having
      • Limit, Offset, and Pagination
      • Locks
      • Unions
      • Common Table Expressions (i.e. CTEs)
      • Raw Expressions
      • When / Conditionals
      • Query Parameters and Bindings
    • Executing Queries
      • Retrieving Results
      • Aggregates
      • Inserts, Updates, and Deletes
    • Options and Utilities
      • Query Options and Utilities
      • Clone and Reset
      • Return Format
      • Column Formatter
      • Interception Points
    • Debugging
      • sqlCommenter
  • Schema Builder
    • Overview
    • Create
    • Columns
    • Column Modifiers
    • Column Constraints
    • Creating Table Constraints
    • Alter
    • Drop
    • Debugging
  • External Links
    • API Docs
    • Source Code
    • Issue Tracker
Powered by GitBook
On this page
  • Introduction
  • Requirements
  • Discussion & Help
  • Installation
  • Code Samples
  • Usage

Was this helpful?

Edit on GitHub
Export as PDF

Introduction

NextWhat's New?

Was this helpful?

Introduction

qb is a fluent query builder for CFML. It is heavily inspired by from .

Using qb, you can:

  • Quickly scaffold simple queries

  • Make complex, out-of-order queries possible

  • Abstract away differences between database engines

Requirements

  • Adobe ColdFusion 2018+

  • Lucee 5+

qb supports four major database grammars:

  • MySQL (MySQLGrammar@qb)

  • Oracle (OracleGrammar@qb)

  • Postgres (PostgresGrammar@qb)

  • Microsoft SQL Server (SqlServerGrammar@qb)

  • SQLite (SQLiteGrammar@qb)

Discussion & Help

The Box modules discussion group and community can be found here:

Installation

Code Samples

Compare these two examples:

// Plain old CFML
var results = queryExecute( "SELECT * FROM users" );

// qb
var qb = wirebox.getInstance( "QueryBuilder@qb" );
var results = qb.from( "users" ).get();

The differences become even more stark when we introduce more complexity:

// Plain old CFML
var results = queryExecute(
    "SELECT * FROM posts WHERE published_at IS NOT NULL AND author_id IN ?",
    [ { value = "5,10,27", cfsqltype = "CF_SQL_NUMERIC", list = true } ]
);

// qb
var qb = wirebox.getInstance( "QueryBuilder@qb" );
var results = qb.from( "posts" )
    .whereNotNull( "published_at" )
    .whereIn( "author_id", [ 5, 10, 27 ] )
    .get();

With qb you can easily handle setting order by statements before the columns you want or join statements after a where clause:

var qb = wirebox.getInstance( "QueryBuilder@qb" );
var results = qb.from( "posts" )
         .orderBy( "published_at" )
         .select( "post_id", "author_id", "title", "body" )
         .whereLike( "author", "Ja%" )
         .join( "authors", "authors.id", "=", "posts.author_id" )
         .get();

// Becomes
var results = queryExecute(
    "SELECT post_id, author_id, title, body FROM posts INNER JOIN authors ON authors.id = posts.author_id WHERE author LIKE ? ORDER BY published_at",
    [ { value = "Ja%", cfsqltype = "CF_SQL_VARCHAR", list = false, null = false } ]
);

qb enables you to explore new ways of organizing your code by letting you pass around a query builder object that will compile down to the right SQL without you having to keep track of the order, whitespace, or other SQL gotchas!

Usage

To start a new query, instantiate a new Builder: wirebox.getInstance( "QueryBuilder@qb" ).

By default, qb uses a generic Grammar. You can specify your specific grammar in ColdBox by setting the defaultGrammar in your moduleSettings.

moduleSettings = {
    qb = {
        defaultGrammar = "MySQLGrammar@qb"
    }
};

If you are not using WireBox, just make sure to wire up the Builder object with the correct grammar:

var grammar = new qb.models.Query.Grammars.MySQLGrammar();
var builder = new qb.models.Query.Builder( grammar );

Installation is easy through and . Simply type box install qb to get started.

Here's a gist with an example of the powerful models you can create with this!

Eloquent
Laravel
https://community.ortussolutions.com/c/box-modules/qb/27
CommandBox
ForgeBox
https://gist.github.com/elpete/80d641b98025f16059f6476561d88202