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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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