# Introduction ![All Contributors](https://img.shields.io/badge/all_contributors-6-orange.svg?style=flat-square) ![Master Branch Build Status](https://img.shields.io/travis/coldbox-modules/qb/master.svg?style=flat-square\&label=master) ![Development Branch Build Status](https://img.shields.io/travis/coldbox-modules/qb/development.svg?style=flat-square\&label=development) ## Introduction qb is a fluent query builder for CFML. It is **heavily** inspired by [Eloquent](https://laravel.com/docs/5.3/eloquent) from [Laravel](https://laravel.com/). Using qb, you can: * Quickly scaffold simple queries * Make complex, out-of-order queries possible * Abstract away differences between database engines ## Requirements * Adobe ColdFusion 11+ * Lucee 4.5+ qb supports four major database grammars: * MSSQL (`MSSQLGrammar`) * MySQL (`MySQLGrammar`) * Oracle (`OracleGrammar`) * Postgres (`PostgresGrammar`) ## Installation Installation is easy through [CommandBox](https://www.ortussolutions.com/products/commandbox) and [ForgeBox](https://www.coldbox.org/forgebox). Simply type `box install qb` to get started. ## Code Samples Compare these two examples: ``` // Plain old CFML q = queryExecute("SELECT * FROM users"); // qb query = wirebox.getInstance('QueryBuilder@qb'); q = query.from('users').get(); ``` The differences become even more stark when we introduce more complexity: ``` // Plain old CFML q = 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 query = wirebox.getInstance('QueryBuilder@qb'); q = query.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: ``` query = wirebox.getInstance('QueryBuilder@qb'); q = query.from('posts') .orderBy('published_at') .select('post_id', 'author_id', 'title', 'body') .whereLike('author', 'Ja%') .join('authors', 'authors.id', '=', 'posts.author_id') .get(); // Becomes q = 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! Here's a gist with an example of the powerful models you can create with this! ## 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" } }; ``` 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 ); ``` ### Return Format You can influence the return format of the result in two ways. By default, qb returns an array of structs as the result of your query. You can turn this behavior off by setting `builder.setReturningArrays( false )` for one-offs or setting `returningArrays = false` in your ColdBox config. ``` moduleSettings = { qb = { returningArrays = false } }; ``` If you want complete control over your return result, you can provide a closure as a `returnFormat`. The results of the closure will be returned as the results of the builder. ``` moduleSettings = { qb = { returnFormat = function( q ) { return application.wirebox.getInstance( name = "Collection", initArguments = { collection = q } ); } } }; ``` ## Interception Points Two interception points are available from QB: `preQBExecute` and `postQBExecute`. These fire before and after the `queryExecute` call, respectively. The following information is available in the `interceptData` struct: | Name | Type | Description | | -------- | ------ | ------------------------------------------------------ | | sql | String | The sql string to execute | | bindings | Struct | The struct of bindings (keys and values) for the query | | options | Struct | Any options to pass along to `queryExecute` | ## Contributors Thanks goes to these wonderful people ([emoji key](https://github.com/kentcdodds/all-contributors#emoji-key)): |


📝 📖

|


💬 📝 🐛 💻 🎨 📖 💡 👀 📢 ⚠️

|


🐛 💻 📖

|


🐛 💻

|


💬 💻

|


💻

| | ---------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------- | This project follows the [all-contributors](https://github.com/kentcdodds/all-contributors) specification. Contributions of any kind welcome! --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://qb.ortusbooks.com/6.4.0/master.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.