QB A QUERY BUILDER FOR THE REST OF US 

WHAT THIS TALK IS NOT: > Database performance tips > How to integrate qb in your application* > About a new ORM Engine (though qb is be the backend for one) * WE'LL TOUCH ON THIS A TINY BIT 

WHAT THIS TALK IS: > How to abstract your SQL statements from your database grammar > Help to stop concatenating SQL strings > Ideas for new patterns to manage complex SQL 

WHO AM I? ERIC PETERSON ! Utah " Ortus # ForgeBox, ColdBox Elixir $ Prolific Module Author % 1 wife, 2 kids, (1 on the way) 

WHAT IS QB? 

WHAT IS QB? QUERYBUILDER & SCHEMABUILDER > A fluent, chainable syntax for building SQL statements > An abstraction around different database engines > An abstraction around queryExecute > Heavily inspired by Eloquent from Laravel 

WHY CREATE QB? 

TELL ME IF YOU'VE SEEN THIS ONE BEFORE 

function find( slug, createdBy, popular /* ..and so forth. */ ) { var q = new Query(); // ! var sql = "SELECT id, slug, createdBy, updatedDate"; // need this for one of two arguments if ( ! isNull( popular ) ) { // fun fact, I was missing a comma here from a previous presentation // this wouldn't have even worked! sql &= ", COUNT(*) AS installCount"; } sql &= " FROM entries"; // now we can add the from if ( ! isNull( popular ) ) { sql &= " JOIN installs ON entries.id = installs.entryId"; } // need some sort of initial where statement // otherwise we have to worry about `WHERE` vs `AND` sql &= " WHERE 1 = 1"; if ( ! isNull( slug ) ) { q.addParam( name = "slug", cfsqltype = "CF_SQL_VARCHAR", value = "%#slug#%", ); sql &= " AND slug LIKE :slug"; // don't forget the leading space!! } if ( ! isNull( createdBy ) ) { q.addParam( name = "createdBy", cfsqltype = "CF_SQL_INTEGER", value = "%#createdBy#%", ); sql &= " AND createdBy = :createdBy"; } if ( ! isNull( popular ) ) { sql &= " GROUP BY id, slug, createdBy, updatedDate"; } // make sure to put the order by at the end. if ( ! isNull( popular ) ) { sql &= " ORDER BY installCount DESC"; } else { sql &= " ORDER BY updatedDate DESC"; } q.setSql( sql ); return q.execute().getResult(); } 

WHAT PAIN POINTS DO WE SEE? > Manually concatenating SQL strings > Repeating checks in multiple places > if statements mean no chainability 

WHAT COULD THIS LOOK LIKE WITH QB? 

function find( slug, createdBy, popular, count /* ..and so forth. */ ) { return builder .from( "entries" ) .select( [ "id", "slug", "createdBy", "updatedDate" ] ) .when( ! isNull( slug ), function( query ) { query.where( "slug", "like", "%#slug#%" ); } ) .when( ! isNull( createdBy ), function( query ) { query.where( "createdBy", createdBy ); } ) .when( ! isNull( popular ), function( query ) { query .addSelect( query.raw( "COUNT(*) AS installCount" ) ) .join( "installs", "entries.id", "installs.entryId" ) .groupBy( [ "id", "slug", "createdBy", "updatedDate" ] ); } ) .when( ! isNull( popular ), function( query ) { query.orderBy("installCount", "desc"); }, function( query ) { query.orderBy("updatedDate", "desc"); } ) .get(); } 

AND TRUST ME, WE CAN (AND WILL) MAKE THAT EVEN BETTER! 

QB IS VALUABLE FOR SMALL SQL STATEMENTS AS WELL! 

SPOT THE PROBLEM function loadPosts() { return queryExecute("SELECT * FROM `posts`"); } 

THE SYNTAX IS DATABASE ENGINE SPECIFIC 

THIS IS AN ALTERNATIVE SYNTAX FOR OTHER ENGINES function loadPosts() { return queryExecute('SELECT * FROM "posts"'); } 

IN QB, BOTH OF THESE STATEMENTS ARE COVERED WITH THIS: function loadPosts() { return builder.from("posts").get(); } 

AND THAT'S JUST THE TIP OF THE ICEBERG 

SO, WHY CREATE QB? > Reduce typos > Bridge database engine idiosyncrasies > Stop concatentating SQL strings > Create expressive fluent statements instead of code littered with if statements > Enable new Object-Oriented patterns with SQL statements 

QB BUILDING BLOCKS > Query Builder / Schema Builder > Grammars > Query Utils 

CONFIGURATION 

DEFAULT GRAMMAR CONFIGURATION 

DEFAULT GRAMMAR CONFIGURATION Bundled grammars include: MSSQLGrammar, MySQLGrammar, OracleGrammar, PostgresGrammar (We would love your help adding more.) 

RETURN FORMAT CONFIGURATION > Return a query > Return an array (default) > Return something custom! 

moduleSettings = { qb = { returnFormat = function( query ) { return application.wirebox.getInstance( name = "CFCollection", initArguments = { collection = query } ); } } } 

CONFIGURATION Configure your settings through the moduleSettings struct in config/ColdBox.cfc. component { function configure() { moduleSettings = { qb = { defaultGrammar = "MySQLGrammar" } } } } 

QUERYBUILDER SYNTAX AN OVERVIEW 

FROM query.from( "posts" ); > Specifies the base table for the query 

SELECT query.select( "id" ); > Default is * > Takes a single value, list, or array > Overwrites any existing selected columns 

ADDSELECT query.addSelect( "id" ); > Adds a column to the select list > If the selection is currently *, then the added column will be the only column selected. 

JOIN query.from( "posts" ) .join( "users", "posts.user_id", "=", "users.id" ); > Add a join statement to the query > Simple syntax for simple joins > Complex joins can be specified using a callback 

WHERE query.where( "title", "=", "My Title" ); > Adds a where statement to the query > Automatically params the value passed > Accepts any SQL operator like <=, <>, or LIKE 

WHERE MAGIC query.where( "title", "My Title" ) Can skip the second parameter if you want the operator to be = 

MORE WHERE MAGIC query.whereTitle( "My Title" ) onMissingMethod lets you write really terse equality where statements 

NESTED WHERE query.from( "users" ) .where( "email", "foo" ) .orWhere( function( q ) { q.where( "name", "bar" ) .where( "age", ">=", "21" ); } ); Becomes SELECT * FROM `users` WHERE `email` = ? OR ( `name` = ? AND `age` >= ? ) 

WHERECOLUMN query.whereColumn( "updated_date", ">", "created_date" ); > Compares two columns > Accepts any SQL operator like <=, <>, or LIKE 

WHEREIN query.whereIn( "id", [ 1, 45, 101 ] ); > Adds a where in statement to the query > Automatically params the values passed 

OTHER WHERE METHODS query.whereBetween( "createdDate", startDate, endDate ); query.whereNull( "deletedDate" ); query.whereExists( function( q ) { q.select( q.raw( 1 ) ).from( "products" ) .where( "products.id", "=", q.raw( """orders"".""id""" ) ); } ); 

WHERE METHODS WRAP-UP There are corresponding orWhere and whereNot methods for each where method. 

GROUPBY query.groupBy( "id" ); > Adds a group by statement > Further calls add to any existing groupings > Takes a single value, list, or array 

HAVING query.from( "users" ) .groupBy( "email" ) .having( builder.raw( "COUNT(email)" ), ">", 1 ); > Adds a having statement > Works pretty much like a where statement 

ORDERBY query.orderBy( "id", "desc" ); > Adds a order by statement > Further calls add to any existing orders > Takes a single value, list, or array 

LIMIT & OFFSET query.limit( 1 ).offset( 5 ); > Adds a limit and offset to the query > This one is a great example where normalizing database engines is super nice. 

MYSQL LIMIT SELECT * FROM `users` LIMIT 3 LIMIT & OFFSET SELECT * FROM `users` LIMIT 15 OFFSET 30 

POSTGRES LIMIT SELECT * FROM "users" LIMIT 3 LIMIT & OFFSET SELECT * FROM "users" LIMIT 15 OFFSET 30 

MSSQL LIMIT SELECT TOP (3) * FROM [users] LIMIT & OFFSET SELECT * FROM [users] ORDER BY 1 OFFSET 30 ROWS FETCH NEXT 15 ROWS ONLY 

ORACLE LIMIT SELECT * FROM ( SELECT results.*, ROWNUM AS "QB_RN" FROM (SELECT * FROM "USERS") results ) WHERE "QB_RN" <= 3 LIMIT & OFFSET SELECT * FROM ( SELECT results.*, ROWNUM AS "QB_RN" FROM (SELECT * FROM "USERS") results ) WHERE "QB_RN" > 30 AND "QB_RN" <= 45 

RAW query.select( query.raw( "COUNT(*) AS post_count" ) ); > The qb escape hatch > Outputs exactly the text you give it 

WHEN query.when( rc.isActive, function( q ) { q.where( "is_active", 1 ); }, function( q ) { q.whereNull( "is_active" ); } ); > Control flow for chainable methods 

TOSQL query.from( "users" ).toSQL() > Builds the query into a SQL statement > Bindings will be available in the getBindings method 

GET query.from( "users" ).get(); > Builds and executes the current query > Can accept columns as a shortcut > Also accepts any options that can be passed to queryExecute 

FIND query.from( "users" ).find( 1 ); > Shortcut method for retrieving records by primary key > Default idColumn is id > idColumn can be specified as second argument 

FIRST query.from( "users" ).first(); > Adds a limit( 1 ) to your query > Also returns the struct value, not an array of one > Returns an empty struct if nothing is found 

EXISTS query.from( "logins" ) .where( "user_id", user.getId() ) .exists(); > Returns a boolean > Works with any configured query > Just call exists instead of get 

COUNT, MAX, MIN, SUM (AGGREGATES) query.from( "users" ) .max( "last_logged_in" ); > Returns a single value from query > Takes the column to perform the operation on 

VALUE query.from( "users" ) .where( "id", "=", rc.id ) .value( "username" ); > Fetches only one record (like first) > Returns the value of the column specified 

VALUES query.from( "users" ) .values( "username" ); > Returns an array of all matched records > Returns only the value of the column specified in the array 

INSERT query.from( "users" ).insert( { "username" = "johndoe", "email" = "[email protected]", "password" = bcryptedPassword } ); > Inserts data into a table 

BATCH INSERT query.from( "users" ).insert( [ { "username" = "johndoe", "email" = "[email protected]", "password" = bcryptedPassword }, { "username" = "janedoe", "email" = "[email protected]", "password" = bcryptedPassword } ] ); > Uses the database-specific batch insert syntax 

UPDATE query.from( "users" ) .whereId( rc.id ) .update( { "password" = newPassword } ); > Updates all matched rows, so be careful > Works off of the built query 

UPDATEORINSERT query.from( "users" ) .whereId( rc.id ) .updateOrInsert( { "username" = "johndoe", "email" = "[email protected]", "password" = bcryptedPassword } ); > Updates or inserts dependening on if the query exists 

DELETE query.from( "users" ) .whereConfirmed( 0 ) .delete(); > Deletes all matched rows, so be careful > Works off of the built query 

WHEW... ! 

QueryFilters EXAMPLE 

QueryFilters EXAMPLE // AbstractQueryFilters.cfc component { property name="query"; function apply( required query, rc = {} ) { variables.query = arguments.query; for ( var key in rc ) { if ( variables.keyExists( key ) && isCustomFunction( variables[ key ] ) ) { var func = variables[ key ]; func( rc[ key ] ); } } return variables.query; } } 

QueryFilters EXAMPLE // EntryFilters.cfc component extends="AbstractQueryFilters" { function slug( slug ) { query.where( "slug", "like", "%#slug#%" ); } function createdBy( authorName ) { query.where( "createdBy", createdBy ); } function popular() { query.addSelect( query.raw( "COUNT(*) AS installCount" ) ) .join( "installs", "entries.id", "installs.entryId" ) .groupBy( [ "entries.id", "slug", "createdBy", "updatedDate" ] ) .orderBy( "installCount", "desc" ); } } 

QueryFilters EXAMPLE // handlers/Entries.cfc component { property name="EntryFilters" inject="id"; function index(event, rc, prc) { var query = getInstance( "Builder@qb" ); query.select( [ "entries.id", "slug", "createdBy", "updatedDate" ] ) .from( "entries" ) .orderBy( "updatedDate", "desc" ); EntryFilters.apply( query, rc ); prc.entries = query.get(); } } 

SEE? 

I TOLD YOU WE'D MAKE THAT EVEN BETTER! 

DEMO! 

CONTRIBUTING 

GRAMMARS CONTRIBUTING 

DOCS CONTRIBUTING 

No content

WHO KNOWS WHAT YOU WILL CREATE WITH THIS? 

THANKS! > qb docs > qb on ForgeBox > me on Twitter (_elpete) > me on ForgeBox (elpete)) > CFML Slack (elpete)