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
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
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(); }
DEFAULT GRAMMAR CONFIGURATION Bundled grammars include: MSSQLGrammar, MySQLGrammar, OracleGrammar, PostgresGrammar (We would love your help adding more.)
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
HAVING query.from( "users" ) .groupBy( "email" ) .having( builder.raw( "COUNT(email)" ), ">", 1 ); > Adds a having statement > Works pretty much like a where statement
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.
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
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
UPDATE query.from( "users" ) .whereId( rc.id ) .update( { "password" = newPassword } ); > Updates all matched rows, so be careful > Works off of the built query