Configure a default datasource in your CFML engine

You can do this any way you'd like: through the web admin, in Application.cfc, or using cfconfig.

Make sure to set this.datasource in your Application.cfc so Quick knows which datasource to use.

Download Quick

The easiest way to download quick is to use forgebox with commandbox. Just run the following from the root of your application:

box install quick

Add a mapping for quick in your Application.cfc

For a default installation in a ColdBox template, the following line will do the trick.

this.mappings[ "/quick" ] = COLDBOX_APP_ROOT_PATH & "/modules/quick";

Configure your defaultGrammar in config/ColdBox.cfc

Quick will auto discover your grammar by default on startup. To avoid this check, set a BaseGrammar.

BaseGrammar is a module setting for Quick. Set it in your config/ColdBox.cfc like so:

moduleSettings = {
    quick = {
        defaultGrammar = "MySQLGrammar"
    }
};

Valid options are any of the qb supported grammars. At the time of writing valid grammar options are: MySQLGrammar, PostgresGrammar, MSSQLGrammar and OracleGrammar. Please check the qb docs for additional options.

If you want to use a different datasource and/or grammar for individual entitities you can do so by adding some metadata attributes to your entities.

Once you have an entity and its associated database table you can start retrieving data from your database.

Active Record

You start every interaction with Quick with an instance of an entity. The easiest way to do this is using WireBox. getInstance is available in all handlers by default. WireBox can easily be injected in to any other class you need using inject="wirebox".

Quick is backed by qb, a CFML Query Builder. With this in mind, think of retrieving records for your entities like interacting with qb. For example:

In addition to using for you can utilize the each function on arrays. For example:

You can add constraints to the query just the same as you would using qb directly:

Quick Service

A second way to retrieve results is to use a Quick Service. It is similar to a VirtualEntityService from cborm.

The easiest way to create a Quick Service is to inject it using the quickService: dsl:

Any method you can call on an entity can be called on the service:

Aggregates

Calling qb's aggregate methods (count, max, etc.) will return the appropriate value instead of an entity or collection of entities.

Custom Quick Retrieval Methods

There are a few custom retrieval methods for Quick:

all

Retrieves all the records for an entity. Calling all will ignore any constraints on the query.

findOrFail & firstOrFail

These two methods will throw a EntityNotFound exception if the query returns no results.

The findOrFail method should be used in place of find, passing an id in to retrieve.

The firstOrFail method should be used in place of first, being called after constraining a query.

delete

You can delete an entity by calling the delete method on it.

var user = getInstance( "User" ).find( 1 );
user.delete();

Note: The entity will still exist in any variables you have stored it in, even though it has been deleted from the database.

deleteAll

Just like updateAll, you can delete many records from the database by specifying a query with constraints and then calling the deleteAll method.

getInstance( "User" )
    .whereActive( false )
    .deleteAll();

Additionally, you can pass in an array of ids to deleteAll to delete only those ids.

getInstance( "User" ).deleteAll( [ 4, 10, 22 ] );

save

New Quick entities can be created and persisted to the database by creating a new entity instance, setting the attributes on the entity, and then calling the save method.

var user = getInstance( "User" );
user.setUsername( "JaneDoe" );
user.setEmail( "jane@example.com" );
user.setPassword( "mypass1234" );
user.save();

When we call save, the record is persisted from the database and the primary key is set to the auto-generated value (if any).

create

Another option is to use the create method. This method accepts a struct of data and creates a new instance with the data specified.

var user = getInstance( "User" ).create( {
    "username" = "JaneDoe",
    "email" = "jane@example.com",
    "password" = "mypass1234"
} );

To get started with Quick, you need an entity. You start by extending quick.models.BaseEntity.

// User.cfc
component extends="quick.models.BaseEntity" {}

Alternatively, you can use the quick virtual inheritance mapping in ColdBox 5.2+.

component quick {}

Both are equivalent, so use the one you prefer. That's all that is needed to get started with Quick. There are a few defaults of Quick worth mentioning here.

Tables

We don't need to tell Quick what table name to use for our entity. By default, Quick uses the pluralized name of the component for the table name. That means for our User entity Quick will assume the table name is users. You can override this by specifying a table metadata attribute on the component.

// User.cfc
component table="my_users" extends="quick.models.BaseEntity" {}

Primary Key

By default, Quick assumes a primary key of id. The name of this key can be configured by setting variables._key in your component.

// User.cfc
component extends="quick.models.BaseEntity" {

    variables._key = "user_id";

}

Quick also assumes a key type that is auto-incrementing. If you would like a different key type, define a function called `keyType` and return the key type from that function.

// User.cfc
component extends="quick.models.BaseEntity" {

    function keyType() {
        return variables._wirebox.getInstance( "UUIDKeyType@quick" );
    }

}

Quick ships with the following key types:

• AutoIncrementingKeyType

• NullKeyType

• ReturningKeyType

• UUIDKeyType

keyType can be any component that adheres to the keyType interface, so feel free to create your own and distribute them via ForgeBox.

interface displayname="KeyType" {

    /**
    * Called to handle any tasks before inserting into the database.
    * Recieves the entity as the only argument.
    */
    public void function preInsert( required entity );

    /**
    * Called to handle any tasks after inserting into the database.
    * Recieves the entity and the queryExecute result as arguments.
    */
    public void function postInsert( required entity, required struct result );

}

Properties

You specify what columns are retrieved by adding properties to your component.

// User.cfc
component extends="quick.models.BaseEntity" {

    property name="id";
    property name="username";
    property name="email";

}

Now, only the id, username, and email columns will be retrieved.

Note: Make sure to include the primary key (id by default) as a property.

Persistent

To prevent Quick from mapping a property to the database add the persistent="false" attribute to the property.

// User.cfc
component extends="quick.models.BaseEntity" {

    property name="bcrypt" inject="@BCrypt" persistent="false";

    property name="id";
    property name="username";
    property name="email";

}

Column

If the column name in your table is not the column name you wish to use in quick, you can alias it using the column metadata attribute.

component extends="quick.models.BaseEntity" {

    property name="id";
    property name="username" column="user_name";
    property name="countryId" column="FK_country_id";

}

Null Values

To work around CFML's lack of null, you can use the nullValue and convertToNull attributes.

nullValue defines the value that is considered null for a property. By default it is an empty string. ("")

convertToNull is a flag that, when false, will not try to insert null in to the database. By default this flag is true.

component extends="quick.models.BaseEntity" {

    property name="id";
    property name="number" convertToNull="false";
    property name="title" nullValue="REALLY_NULL";

}

Read Only

The readOnly attribute will prevent setters, updates, and inserts to a property when set to true.

component extends="quick.models.BaseEntity" {

    property name="id";
    property name="createdDate" readonly="true";

}

SQL Type

In some cases you will need to specify an exact SQL type for your property. Any value set for the sqltype attribute will be used when inserting or updating the property in the database.

component extends="quick.models.BaseEntity" {

    property name="id";
    property name="number" sqltype="cf_sql_varchar";

}

Casts

The casts attribute allows you to use a value in your CFML code as a certain type while being a different type in the database. A common example of this is a boolean which is usually represented as a BIT in the database.

component extends="quick.models.BaseEntity" {

    property name="id";
    property name="active" casts="boolean";

}

Currently, only boolean is supported as a cast type.

Insert & Update

You can prevent inserting and updating a property by setting the insert or update attribute to false.

component extends="quick.models.BaseEntity" {

    property name="id";
    property name="email" column="email" update="false" insert="true";

}

Formula, Computed, or Subselect properties

Quick handles formula, computed, or subselect properties using query scopes and the addSubselect helper method. Check out the docs in query scopes to learn more.

Multiple datasource support

Quick uses a default datasource and default grammar, as described here. If you are using multiple datasources you can override default datasource by specifying a datasource metadata attribute on the component. If your extra datasource has a different grammar you can override your grammar as well by specifying a grammar attribute.

// User.cfc
component datasource="myOtherDatasource" grammar="PostgresGrammar" extends="quick.models.BaseEntity" {}

At the time of writing Valid grammar options are: MySQLGrammar, PostgresGrammar, MSSQLGrammar and OracleGrammar. Please check the qb docs for additional options.

Definition

Query scopes are a way to encapsulate query constraints in your entities while giving them readable names .

A Practical Example

For instance, let's say that you need to write a report for subscribers to your site. Maybe you track subscribers in a users table with a boolean flag in a subscribed column. Additionally, you want to see the oldest subscribers first. You keep track of when a user subscribed in a subscribedDate column. Your query might look as follows:

var subscribedUsers = getInstance( "User" )
    .where( "subscribed", true )
    .orderBy( "subscribedDate" )
    .get();

Now nothing is wrong with this query. It retrieves the data correctly and you continue on with your day.

Later, you need to retrieve a list of subscribed users for a different part of the site. So, you write a query like this:

var subscribedUsers = getInstance( "User" )
    .where( "subscribed", true )
    .get();

We've duplicated the logic for how to retrieve active users now. If the database representation changed, we'd have to change it in multiple places. For instance, what if instead of keeping track of a boolean flag in the database, we just checked that the subscribedDate column wasn't null?

var subscribedUsers = getInstance( "User" )
    .whereNotNull( "subscribedDate" )
    .get();

Now we see the problem. Let's look at the solution.

The key here is that we are trying to retrieve subscribed users. Let's add a scope to our User entity for subscribed:

component extends="quick.models.BaseEntity" {

    function scopeSubscribed( query ) {
        return query.where( "subscribed", true );
    }

}

Now, we can use this scope in our query:

var subscribedUsers = getInstance( "User" )
    .subscribed()
    .get();

We can use this on our first example as well, for our report.

var subscribedUsers = getInstance( "User" )
    .subscribed()
    .orderBy( "subscribedDate" )
    .get();

We've successfully encapsulated our concept of a subscribed user!

We can add as many scopes as we'd like. Let's add one for longestSubscribers.

component extends="quick.models.BaseEntity" {

    function scopeLongestSubscribers( query ) {
        return query.orderBy( "subscribedDate" );
    }

    function scopeSubscribed( query ) {
        return query.where( "subscribed", true );
    }

}

Now our query is as follows:

var subscribedUsers = getInstance( "User" )
    .subscribed()
    .longestSubscribers()
    .get();

Best of all, we can reuse those scopes anywhere we see fit without duplicating logic.

Usage

All query scopes are methods on an entity that begin with the scope keyword. You call these functions without the scope keyword (as shown above).

Each scope is passed the query, a reference to the current QueryBuilder instance, as the first argument. Any other arguments passed to the scope will be passed in order after that.

component extends="quick.models.BaseEntity" {

    function scopeOfType( query, type ) {
        return query.where( "type", type );
    }

}

var subscribedUsers = getInstance( "User" )
    .ofType( "admin" )
    .get();

Scopes that Return Values

All of the examples so far either returned the query object or nothing. Doing so lets you continue to chain methods on your Quick entity. If you instead return a value, Quick will pass on that value to your code. This lets you use scopes as shortcut methods that work on a query.

For example, maybe you have a domain method to reset passwords for a group of users, and you want the count of users updated returned.

component quick {

    property name="id";
    property name="username";
    property name="password";
    property name="type";

    function scopeOfType( query, type = "limited" ) {
        return query.where( "type", type );
    }

    function scopeResetPasswords( query ) {
        return query.updateAll( { "password" = "" } ).result.recordcount;
    }

}

getInstance( "User" ).ofType( "admin" ).resetPasswords(); // 1

Global Scopes

Occasionally, you want to apply a scope to each retrieval of an entity. An example of this is an Admin entity which is just a User entity with a type of admin. Global Scopes can be registered in the applyGlobalScopes method on an entity. Inside this entity you can call any number of scopes:

component extends="User" table="users" {

    function applyGlobalScopes() {
        this.ofType( "admin" );
    }

}

These scopes will be applied to the query without needing to call the scope again.

var admins = getInstance( "Admin" ).all();
// SELECT * FROM users WHERE type = 'admin'

If you have a global scope applied to an entity that you need to temporarily disable, you can disable them individually using the withoutGlobalScope method:

var admins = getInstance( "Admin" ).withoutGlobalScope( [ "ofType" ] ).all();
// SELECT * FROM users

Subselects

Subselects are a useful way to grab data from related tables without having to execute the full relationship. Sometimes you just want a small piece of information like the last_login_date of a user, not the entire Login relationship. Subselects are perfect for this use case. You can even use subselects to provide the correct key for subselect relationships. We'll show how both work here.

Quick handles subselect properties (or computed or formula properties) through query scopes. This allows you to dynamically include a subselect. If you would like to always include a subselect, add it to your entity's list of global scopes.

Here's an example of grabbing the last_login_date for a User:

component extends="quick.models.BaseEntity" {

    /* properties */

    function logins() {
        return hasMany( "Login" );
    }

    function scopeWithLastLoginDate( query ) {
        addSubselect( "lastLoginDate", newEntity( "Login" )
            .select( "timestamp" )
            .whereColumn( "users.id", "user_id" )
            .latest()
        );
    }

}

We'd add this subselect by calling our scope:

var user = getInstance( "User" ).withLastLoginDate().first();
user.getLastLoginDate(); // {ts 2019-05-02 08:24:51}

We can even constrain our User entity based on the value of the subselect, so long as we've called the scope adding the subselect first (or made it a global scope).

 var user = getInstance( "User" )
     .withLastLoginDate()
     .where( "lastLoginDate", ">", "2019-05-10" )
     .all();

Or add a new scope to User based on the subselect:

function scopeLoggedInAfter( query, required date afterDate ) {
    return query.where( "lastLoginDate", ">", afterDate );
}

In this example, we are using the addSubselect helper method. Here is that function signature:

You might be wondering why not use the logins relationship? Or even logins().latest().limit( 1 ).get()? Because that executes a second query. Using a subselect we get all the information we need in one query, no matter how many entities we are pulling back.

Subselects can be used in conjunction with relationships to provide a dynamic, constrained relationship. In this example we will pull the latest post for a user.

component extends="BaseEntity" {

    /* properties */

    function scopeWithLatestPost( query ) {
        return addSubselect( "latestPostId", newEntity( "Post" )
            .select( "id" )
            .whereColumn( "user_id", "users.id" )
            .orderBy( "created_date", "desc" )
        ).with( "latestPost" );
    }

    function latestPost() {
        return belongsTo( "Post", "latestPostId" );
    }

}

This can be executed as follows:

var users = getInstance( "User" ).withLatestPost().all();
for ( var user in users ) {
    user.getLatestPost().getTitle(); // My awesome post, etc.
}

As you can see, we are loading the id of the latest post in a subquery and then using that value to eager load the latestPost relationship. This sequence will only execute two queries, no matter how many records are loaded.

save

Updates are handled identically to inserts when using the save method. The only difference is that instead of starting with a new entity, we start with an existing entity.

update

You can update multiple fields at once using the update method. This is similar to the create method for creating new entities.

There is no need to call save when using the update method.

By default, if you have a key in the struct that doesn't match a property in the entity the update method will fail. If you add the optional argument ignoreNonExistentAttributes set to true, those missing keys are ignored. Now you can pass the rc scope from your submitted form directly into the update method and not worry about any other keys in the rc like event that would cause the method to fail.

updateAll

Updates can be performed against any number of entities that match a given query.