Relationships are the heart of any ORM engine. They let you interact with relational database tables in an object-oriented way.

Quick's relationship engine provides readable relationship types, extendible relations at runtime, eager loading, and much more.

Start by checking out the different relationship types.

Defining

A hasOne relationship is a "one-to-one" relationship. For instance, a User entity might have an UserProfile entity attached to it.

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

    function profile() {
       return hasOne( "UserProfile" );
    }

}

The first value passed to hasOne is a WireBox mapping to the related entity.

Quick determines the foreign key of the relationship based on the entity name and key values. In this case, the UserProfile entity is assumed to have a userId foreign key. You can override this by passing a foreign key in as the second argument:

return hasOne("UserProfile", "FK_userID");

If your parent entity does not use id as its primary key, or you wish to join the child entity to a different column, you may pass a third argument to the belongsTo method specifying your parent table's custom key.

return belongsTo("UserProfile", "FK_userID", "profile_id");

The inverse of hasOne is belongsTo. It is important to choose the right relationship for your database structure. hasOne assumes that the related model has the foreign key for the relationship.

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

    function user() {
        return belongsTo( "User" );
    }

}

delete

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

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.

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

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:

var users = getInstance("User").all();

for (var user in users) {
    writeOutput(user.getUsername());
}

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

var users = getInstance("User").all();

prc.users.each(function(user) {
    writeOutput(user.getUsername());
});

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

var users = getInstance("User")
    .where("active", 1)
    .orderBy("username", "desc")
    .limit(10)
    .get();

For more information on what is possible with qb, check out the qb documentation.

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:

component {
    property name="userService" inject="quickService:User"
}

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

var users = userService
    .where("active", 1)
    .orderBy("username", "desc")
    .limit(10)
    .get();

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.

A belongsTo relationship is a many-to-one relationship. For instance, a Post may belong to a User.

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

    function user() {
       return belongsTo( "User" );
    }

}

The first value passed to belongsTo is a WireBox mapping to the related entity.

Quick determines the foreign key of the relationship based on the entity name and key values. In this case, the Post entity is assumed to have a userId foreign key. You can override this by passing a foreign key in as the second argument:

return belongsTo("User", "FK_userID");

If your parent entity does not use id as its primary key, or you wish to join the child entity to a different column, you may pass a third argument to the belongsTo method specifying your parent table's custom key.

return belongsTo("User", "FK_userID", "relatedPostId");

The inverse of belongsTo is hasMany or hasOne.

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

    function posts() {
        return hasMany( "Post" );
    }

    function latestPost() {
        // remember, relationships are just queries!
        return hasOne( "Post" ).orderBy( "createdDate", "desc" );
    }

}

Updating

To update a belongsTo relationship, use the associate method. associate takes the entity to associate as the only argument.

var post = getInstance("Post").findOrFail(1);

var user = getInstance("User").findOrFail(1);

post.user().associate(user);

post.save();

Note: associate does not automatically save the entity. Make sure to call save when you are ready to persist your changes to the database.

Removing

To remove a belongsTo relationship, use the dissociate method.

var post = getInstance("Post").findOrFail(1);

post.user().dissociate();

post.save();

Note: dissociate does not automatically save the entity. Make sure to call save when you are ready to persist your changes to the database.

Relationship Setter

You can also influence the associated entities by calling "set" & relationshipName and passing in an entity or key value.

var post = getInstance( "Post" ).first();
post.setAuthor( 1 );

After executing this code, the post would be updated in the database to be associated with the user with an id of 1.

2.5.0

• Define custom collections per entity.

2.4.0

• Apply custom setters when hydrating from the database. (Reverted in 2.5.3 for unintended consequences with things like password hashing.)

• Query scopes can return any value. This allows you to use scopes to perform query functions and return values. (If you do not want to return a custom value, return the QueryBuilder instance or nothing.)

• Improve error messages for not loaded entities.

• Return the correct memento with accessors on.

2.3.0

• Option to ignore non-existent attributes

2.2.0

• Relationship Fetch Methods (first and find methods)

2.1.0

• Subselect Helper

• Global Scopes

• saveMany

• Mapping foreign keys for relationships is now optional

• Either entities or primary key values can be passed to relationship persistance methods

• Relationships can also be saved by calling "set" & relationshipName

• Virtual Inheritance works on ColdBox 5.2+

Collections are what are returned when calling get or all on an entity. By default, it returns an array. Every entity can override its newCollection method and return a custom collection. This method accepts an array of entities and should return your custom collection.

QuickCollection is a custom collection included in Quick as an extra component. It is a specialized version of . It smooths over the various CFML engines to provide an extendible, reliable array wrapper with functional programming methods. You may be familiar with methods like map (ArrayMap), filter (ArrayFilter), or reduce (ArrayReduce). These methods work in every CFML engine with CFCollection.

To use collections you need to install cfcollection and configure it as your as your newCollection.

Here's how you would configure an entity to return a QuickCollection.

Collections are more powerful than plain arrays. There are many methods that can make your work easier. For instance, let's say you needed to group each active user by the first letter of their username in a list.

So powerful! We think you'll love it.

load

Additionally, QuickCollection includes a load method. load lets you eager load a relationship after executing the initial query.

This is the same as if you had initially executed:

$renderData

QuickCollection includes a $renderData method that lets you return a QuickCollection directly from your handler and translates the results and the entities within to a serialized version. Check out more about it in the chapter.

A polymorphicBelongsTo relationship is a many-to-one relationship. This relationship is used when an entity can belong to multiple types of entities. The classic example for this type of relationship is Posts, Videos, and Comments. For instance, a Comment may belong to a Post or a Video.

The only value passed to polymorphicBelongsTo is a prefix for the polymorphic type. A common convention where is to add able to the end of the entity name, though this is not automatically done. In our example, this prefix is commentable. This tells quick to look for a commentable_type and a commentable_id column in our Comment entity. It stores our entity's mapping as the _type and our entity's primary key value as the _id.

When retrieving a polymorphicBelongsTo relationship the _id is used to retrieve a _type from the database.

The inverse of polymorphicBelongsTo is also polymorphicHasMany. It is important to choose the right relationship for your database structure. hasOne assumes that the related model has the foreign key for the relationship.

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.

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.

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.

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.

var user = getInstance( "User" ).find( 1 );
user.setPassword( "newpassword" );
user.save();

update

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

var user = getInstance( "User" ).find( 1 );
user.update( {
   email = "[email protected]",
   password = "newpassword"
} );

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.

var user = getInstance( "User" ).find( 1 );
user.update( rc, true );

updateAll

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

getInstance( "User" )
    .where( "lastLoggedIn", ">", dateAdd( "m", 3, now() ) )
    .updateAll( {
        "active" = 0
    } );

There are two ways to debug Quick entities, both by hooking in to qb.

qb logs all queries it runs as debug logs. Configure LogBox to output debug logs for the qb.models.Grammars.BaseGrammar component to view them.

Additionally, qb announces a preQBExecute and a postQBExecute interception point. These interception points contain the sql and bindings being executed. You can hook in to these interception points to enable your own logging.

A belongsToMany relationship is a many-to-many relationship. For instance, a User may have multiple Permissions while a Permission can belong to multiple Users.

The first value passed to belongsToMany is a WireBox mapping to the related entity.

belongsToMany makes some assumptions about your table structure. To support a many-to-many relationship, you need a pivot table. This is, at its simplest, a table with each of the foreign keys as columns.

As you can see, Quick uses a convention of combining the entity table names in alphabetical order with an underscore (_) to create the new pivot table name. If you want to override this convention, you can do so by passing the desired table name as the second parameter or the table parameter.

Quick determines the foreign key of the relationship based on the entity name and key values. In this case, the User entity is assumed to have a userId foreign key and the Permission entity a permissionId foreign key. You can override this by passing a foreignKey in as the third argument and a relatedKey as the fourth argument:

Finally, if you are not joining on the primary keys of the current entity or the related entity, you can specify those keys using the last two parameters:

The inverse of belongsToMany is also belongsToMany. The foreignKey and relatedKey arguments are swapped on the inverse side of the relationship.

If you find yourself needing to interact with the pivot table (permissions_users) in the example above, you can create an intermediate entity, like UserPermission. You will still be able to access the end of the relationship chain using the hasManyThrough relationship type.

attach

Use the attach method to relate two belongsToMany entities together. attach can take a single id, a single entity, or an array of ids or entities (even mixed and matched) to associate.

detach

Use the detach method to remove an existing entity from a belongsToMany relationship. detatch can also take a single id, a single entity, or an array of ids or entities (even mixed and matched) to remove.

sync

Sometimes you just want the related entities to be a list you give it. For these situations, use the sync method.

Now, no matter what relationships existed before, this Post will only have three tags associated with it.

Relationship Setter

You can also influence the associated entities by calling "set" & relationshipName and passing in an entity or key value.

This code calls sync on the relationship. After executing this code, the post would be updated in the database to be associated with the tags passed in (4, 12, and 2). Any tags that were previously associated with this post would no longer be and only the tags passed in would be associated now.

Defining

A hasMany relationship is a one-to-many relationship. For instance, a User may have multiple Posts.

The first value passed to hasMany is a WireBox mapping to the related entity.

Quick determines the foreign key of the relationship based on the entity name and key values. In this case, the Post entity is assumed to have a userId foreign key. You can override this by passing a foreign key in as the second argument:

If your parent entity does not use id as its primary key, or you wish to join the child entity to a different column, you may pass a third argument to the belongsTo method specifying your parent table's custom key.

The inverse of hasMany is also .

Inserting & Updating

There are two ways to add an entity to a hasMany relationship. Both mirror the for entities.

save

You can call the save method on the relationship passing in an entity to relate.

This will add the User entity's id as a foreign key in the Post and save the Post to the database.

Note: the save method is called on the posts relationship, not the getPosts collection.

saveMany

You can also add many entities in a hasMany relationship by calling saveMany. This method takes an array of key values or entities and will associate each of them with the base entity.

create

Use the create method to create and save a related entity directly through the relationship.

This example will have the same effect as the previous example.

Removing

Removing a hasMany relationship is handled in two ways: either by using the dissociate method on the side of the relationship or by deleting the side of the relationship.

Relationship Setter

You can also influence the associated entities by calling "set" & relationshipName and passing in an array of entities or key values.

After running this code, this user would only have two posts, the posts with ids 2 and 4. Any other posts would now be disassociated with this user. Likely your database will be guarding against creating these orphan records. Admittedly, this method is not as likely to be used as the others, but it does exist if it solves your use case.

A polymorphicHasMany relationship is a one-to-many relationship. This relationship is used when an entity can belong to multiple types of entities. The classic example for this type of relationship is Posts, Videos, and Comments.

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

    function comments() {
       return polymorphicHasMany( "Comment", "commentable" );
    }

}

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

    function comments() {
        return polymorphicHasMany( "Comment", "commentable" );
    }

}

The first value passed to polymophicHasMany is a WireBox mapping to the related entity.

The second value is a prefix for the polymorphic type. A common convention where is to add able to the end of the entity name, though this is not automatically done. In our example, this prefix is commentable. This tells quick to look for a commentable_type and a commentable_id column in our Comment entity. It stores our entity's mapping as the _type and our entity's primary key value as the _id.

The inverse of polymophicHasMany is polymorphicBelongsTo.

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

    function post() {
        return polymorphicBelongsTo( "commentable" );
    }

}

To assist you in migrating from CBORM, Quick ships with a small compatibility shim. To use it, have your entity extend quick.models.CBORMCompatEntity. This will map common CBORM methods to their Quick counterparts as well as provide a partial CriteriaBuilder shim. The compatibility shim does not cover differences in properties or relationships.

Entity / Service Methods

• list

• countWhere

• deleteById

• deleteWhere

• exists

• findAllWhere

• findWhere

• get

• getAll

• new

• populate

• save

• saveAll

• newCriteria

Criteria Builder Methods

• getSQL

• between

• eqProperty

• isEQ

• isGT

• gtProperty

• isGE

• geProperty

• idEQ

• like

• ilike

• isIn

• isNull

• isNotNull

• isLT

• ltProperty

• neProperty

• isLE

• leProperty

• maxResults

• firstResult

• order

• list

• get

• count

• onMissingMethod

Sometimes you want to use a different value in your code than is stored in your database. Perhaps you want to enforce that setting a password always is hashed with BCrypt. Maybe you have a Date value object that you want wrapping each of your dates. You can accomplish this using custom getters and setters.

A custom getter or setter is simply a function in your entity.

To retrieve the attribute value fetched from the database, call retrieveAttribute passing in the name of the attribute.

To set an attribute for saving to the database, call assignAttribute passing in the name and the value.

Note: Custom getters and setters with not be called when hydrating a model from the database.

The Problem

Let's imagine a scenario where you are displaying a list of posts. You fetch the posts:

prc.posts = getInstance( "Post" ).limit( 25 ).get():

And start looping through them:

<cfoutput>
    <h1>Posts</h1>
    <ul>
        <cfloop array="#prc.posts#" item="post">
            <li>#post.getTitle()# by #post.getAuthor().getUsername()#</li>
        </cfloop>
    </ul>
</cfoutput>

When you visit the page, though, you notice it takes a while to load. You take a look at your SQL console and you've executed 26 queries for this one page! What?!?

Turns out that each time you loop through a post to display its author's username you are executing a SQL query to retreive that author. With 25 posts this becomes 25 SQL queries plus one initial query to get the posts. This is where the N+1 problem gets its name.

So what is the solution? Eager Loading.

Eager Loading means to load all the needed users for the posts in one query rather than separate queries and then stitch the relationships together. With Quick you can do this with one method call.

The Solution

with

You can eager load a relationship with the with method call.

prc.posts = getInstance( "Post" )
    .with( "author" )
    .limit( 25 )
    .get();

with takes one parameter, the name of the relationship to load. Note that this is the name of the function, not the entity name. For example:

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

    function author() {
        return belongsTo( "User" );
    }

}

To eager load the User in the snippet above you would call pass author to the with method.

getInstance( "Post" ).with( "author" ).get();

For this operation, only two queries will be executed:

SELECT * FROM `posts` LIMIT 25

SELECT * FROM `users` WHERE `id` IN (1, 2, 3, 4, 5, 6, ...)

Quick will then stitch these relationships together so when you call post.getAuthor() it will use the fetched relationship value instead of going to the database.

Nested Relationships

You can eager load nested relationships using dot notation. Each segment must be a valid relationship name.

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

    function country() {
        return belongsTo( "User" );
    }

}

getInstance( "Post" ).with( "author.country" );

You can eager load multiple relationships by passing an array of relation names to with or by calling with multiple times.

getInstance( "Post" ).with( [ "author.country", "tags" ] );

Constraining Eager Loaded Relationships

In most cases when you want to constrain an eager loaded relationship, the better approach is to create a new relationship.

// User.cfc
component {

    function posts() {
        return hasMany( "Post" );
    }

    function publishedPosts() {
        return hasMany( "Post" ).published(); // published is a query scope on Post
    }

}

You can eager load either option.

getInstance( "User" ).with( "posts" ).get();
getInstance( "User" ).with( "publishedPosts" ).get();

Occassionally that decision needs to be dynamic. For example, maybe you only want to eager load the posts created within a timeframe defined by a user. To do this, pass a struct instead of a string to the with function. The key should be the name of the relationship and the value should be a function. This function will accept the related entity as its only argument. Here is an example:

getInstance( "User" ).with( { "posts" = function( query ) {

} } ).latest().get();

If you need to load nested relationships with constraints you can call with in your constraint callback to continue eager loading relationships.

getInstance( "User" ).with( { "posts" = function( q1 ) {
    return q1
        .whereBetween( "published_date", rc.startDate, rc.endDate )
        .with( { "comments" = function( q2 ) {
            return q2.where( "body", "like", rc.search );
        } } );
} } ).latest().get();

load

Finally, you can postpone eager loading until needed by using the load method on QuickCollection. load has the same function signature as with. QuickCollection is the object returned for all Quick queries that return more than one record. Read more about it in Collections.

2.0.0

Quick 2.0 brings with it a lot of changes to make things more flexible and more performant. This shouldn't take too long — maybe 2-5 minutes per entity.

Internal properties renamed

There were some common name clashes between internal Quick properties and custom attributes of your entities (the most common being fullName). All Quick internals have been obfuscated to avoid this situation. If you relied on these properties, please consult the following table below for the new property names.

Additionally, some method names have also changed to avoid clashing with automatically generated getters and setters. Please consult the table below for method changes.

Lastly, the following properties and methods have been removed:

Key Types

Defining Key Types

Key Types are the way to define setting and retrieving a primary key in Quick. In Quick 1.0 these were injected in to the component. This made reusability hard for simple things like sequence names. In order to allow for more flexible key types, key types are no longer injected. Instead, they should be returned from a keyType method.

The keyType is lazily created and cached on the component, so this is both a more flexible approach as well as being more performant. If you are injecting custom key types in your entities you will need to move them to the method syntax.

Changed Key Types

A few key types have been renamed and will need to be updated in your codebase:

New Key Types

In additional to the changes to defining key types, there is a few new key types introduced in Quick 2.0.

ReturningKeyType

Used with grammars that return their primary key in the query response when inserting to the database. An example of this is NEWSEQUENTIALID in Microsoft SQL Server.

Scopes

The way arguments are passed to scopes have been updated to allow for default arguments. query is still the first argument. Other arguments will be passed in order after that. The args struct is no longer passed.

Relationships

The relationship methods are still named the same but some of the arguments have been changed to fix bugs and support better eager loading performance. Please for more details.

Additionally, the alternative syntax for defining relationships on a relationships struct has been removed. It created an unnecessary code path that had it's own share of bugs. All relationships should be defined as methods on the entity.

Removing CFCollection

CFCollection was included in Quick 1.0 as both a way to lazily eager load a relationship and as a compatibility layer for older CF versions. The compatibility that CFCollection provides, however, comes with a performance cost. Additionally, the majority of users wanted to use plain arrays as the return format. For those reasons, arrays are now the default return format for collections. CFCollections can still be used by specifying a different return format in the module settings.

Converting to Null

Null is a tricky thing in CFML. The same goes for interacting with nulls in a database. By default, we will support the CFML convention of using an empty string to represent null. When interacting with the database empty strings will be converted to nulls. You can adjust this behavior on the property level with two new annotations:

• convertToNull - Determines if the property will be automatically checked to convert to null at all. Defaults to true.

• nullValue - This is the value that is equivalent to null for this property. Defaults to an empty string.

Returning Null instead of Unloaded Entities

In an effort to avoid dealing with CFML's version of null, Quick originally returned unloaded entities. You could check if an entity was loaded using the isLoaded method. This doesn't make as much sense as null however and even made it more difficult to interact with other libraries. Now Quick will return null when it encounters an empty query result either from a retrieval or from a belongsTo or hasOne relationship. Any instances that you were checking isLoaded should be updated. isLoaded will continue to exist for when you are creating a new entity not from the database.

AutoDiscover Grammar

The default grammar for Quick is now AutoDiscover. This provides a better first run experience. The grammar can still be set in the moduleSettings.

BaseService

As a new way to interact with Quick, you can use Quick Services to interact with your entities in a service-oriented fashion. These are equivalent to VirtualEntityServices in cborm.

The easiest way to use a Quick Service is to use the quickService: injection dsl.

All methods available on the Quick entity are available on the service.

Eager Loading

Eager loading is now supported for nested relationships using a dot-separated syntax. Additionally, constraints can be added to an eager loaded relationship. See the for more information.

Column Aliases in Queries

Column aliases can now be used in queries. They will be transformed to columns before executing the query.

Quick entities in Setters

If you pass a Quick entity to a setter method the entity's keyValue value will be passed.

Update and Insert Guards

Columns can be prevented from being inserted or updated using property attributes — insert="false" and update="false".

cbvalidation removed as a default dependency

Quick no longer automatically validates entities before saving them. Having cbvalidation baked in made it hard to extend it. If desired, validation can be added back in using Quick's lifecycle hooks.

instanceReady Lifecycle Method

Quick now announces an instanceReady event after the entity has gone through dependency injected and had its metadata inspected. This can be used to hook in other libraries, like cbvalidation and mementifier.

Automatic Attribute Casting

You can automatically cast a property to a boolean value while retrieving it from the database and back to a bit value when serializing to the database by setting casts="boolean" on the property.

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:

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:

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?

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:

Now, we can use this scope in our query:

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

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.

Now our query is as follows:

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.

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.

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:

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

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:

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

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

We'd add this subselect by calling our scope:

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).

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

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.

This can be executed as follows:

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.

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

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

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.

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.

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.

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.

Properties

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

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.

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.

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.

Read Only

The readOnly attribute will prevent setters, updates, and inserts to a property when set to 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.

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.

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.

Formula, Computed, or Subselect properties

Quick handles formula, computed, or subselect properties using query scopes and the addSubselect helper method.

Multiple datasource support

Quick uses a default datasource and default grammar, as described . 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.

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

Quick allows you to hook in to multiple points in the entity lifecycle. If the event is on the component, you do not need to prefix it with quick. If you are listening to an interception point, include quick at the beginning.

quickInstanceReady

Fired after dependency injection has been performed on the entity and the metadata has been inspected.

interceptData structure

quickPreLoad

Fired before attempting to load an entity from the database.

This method is only called for find actions.

interceptData structure

quickPostLoad

Fired after loading an entity from the database.

This method is only called for find actions.

interceptData structure

quickPreSave

Fired before saving an entity to the database.

This method is called for both insert and update actions.

interceptData structure

quickPostSave

Fired after saving an entity to the database.

This method is called for both insert and update actions.

interceptData structure

quickPreInsert

Fired before inserting an entity into the database.

interceptData structure

quickPostInsert

Fired after inserting an entity into the database.

interceptData structure

quickPreUpdate

Fired before updating an entity in the database.

interceptData structure

quickPostUpdate

Fired after updating an entity in the database.

interceptData structure

quickPreDelete

Fired before deleting a entity from the database.

interceptData structure

quickPostDelete

Fired after deleting a entity from the database.

interceptData structure

getMemento

The memento pattern is an established pattern in ColdBox apps. A memento in this case is a simple representation of your entity using arrays, structs, and simple values.

For instance, the following example shows a User entity and its corresponding memento:

component extends="quick.models.BaseEntity" {

    property name="id";
    property name="username";
    property name="email";
    property name="password";
    property name="createdDate";
    property name="modifiedDate";

}

{
    "id" = 1,
    "username" = "JaneDoe",
    "email" = "[email protected]",
    "password" = "$2a$04$2nVI5rPOfl6.hrflkhBWOObO5Z7lXGJpi1vlosY74NrL/CKdpWqZS"
    "createdDate" = "{ts '2018-03-12 16:14:10'}",
    "modifiedDate" = "{ts '2018-03-12 16:14:10'}"
}

You can modify the memento by overriding the getMemento function on your entity.

component extends="quick.models.BaseEntity" {

    property name="id";
    property name="username";
    property name="email";
    property name="password";
    property name="createdDate";
    property name="modifiedDate";

    function getMemento() {
        return {
            id = getId(),
            username = getUsername(),
            email = getEmail(),
            createdDate = dateFormat( getCreatedDate(), "MM/DD/YYYY" ),
            // can also use getAttribute if you want to bypass a custom getter
            modifiedDate = dateFormat( retrieveAttribute( "modifiedDate" ), "MM/DD/YYYY" )
        };
    }

}

{
    "id" = 1,
    "username" = "JaneDoe",
    "email" = "[email protected]",
    "createdDate" = "03/12/2018",
    "modifiedDate" = "03/12/2018"
}

$renderData

The $renderData method is a special method for ColdBox. When returning a model from a handler, this method will be called and the value returned will be used as the serialized response. This let's you simply return an entity from a handler for your API. By default this will call getMemento().

component {

    // /users/:id
    function show( event, rc, prc ) {
        return getInstance( "User" ).findOrFail( rc.id );
    }

}

{
    "id" = 1,
    "username" = "JaneDoe",
    "email" = "[email protected]",
    "createdDate" = "03/12/2018",
    "modifiedDate" = "03/12/2018"
}

QuickCollection also defines a $renderData method, which will delegate the call to each entity in the collection and return the array of serialized entities.

component {

    function index( event, rc, prc ) {
        return getInstance( "User" ).all();
    }

}

[
    {
        "id" = 1,
        "username" = "JaneDoe",
        "email" = "[email protected]",
        "createdDate" = "03/12/2018",
        "modifiedDate" = "03/12/2018"
    },
    {
        "id" = 2,
        "username" = "JohnDoe",
        "email" = "[email protected]",
        "createdDate" = "03/14/2018",
        "modifiedDate" = "03/15/2018"
    }
]

Relationships can be used in two ways.

The first is as a getter. Calling user.getPosts() will execute the relationship, cache the result, and return it.

var posts = user.getPosts();

The second is as a relationship. Calling user.posts() returns a Relationship instance to retrieve the posts that can be further constrained. A Relationship is backed by qb as well, so feel free to call any qb method to further constrain the relationship.

var newestPosts = user
    .posts()
    .orderBy( "publishedDate", "desc" )
    .get();

You can also call the other Quick fetch methods: first, firstOrFail, find, and findOrFail are all supported. This is especially useful to constrain the entities available to a user by using the user's relationships:

// This will only find posts the user has written.
var post = user.posts().findOrFail( rc.id );

A CFML ORM Engine

Why?

Quick was built out of lessons learned and persistent challenges in developing complex RDBMS applications using built-in Hibernate ORM in CFML.

• Hibernate ORM error messages often obfuscate the actual cause of the error

  because they are provided directly by the Java classes.

• Complex CFML Hibernate ORM applications can consume significant memory and

  processing resources, making them cost-prohibitive and inefficient when used

  in microservices architecture.

• Hibernate ORM is tied to the engine releases. This means that updates come

  infrequently and may be costly for non-OSS engine users.

• Hibernate ORM is built in Java. This limits contributions from CFML

  developers who don't know Java or don't feel comfortable contributing to a

  Java project.

• Hibernate ORM doesn't take advantage of a lot of dynamic- and

  meta-programming available in CFML. (Tools like CBORM have helped to bridge

  this gap.)

We can do better.

What?

Quick is an ORM (Object Relational Mapper) written in CFML for CFML. It provides an ActiveRecord implementation for working with your database. With it you can map database tables to components, create relationships between components, query and manipulate data, and persist all your changes to your database.

Prerequisites

You need the following configured before using Quick:

• Configure a default datasource in your CFML engine

• ColdBox 4.3+

• Add a mapping for quick in your Application.cfc

• Configure your BaseGrammar in config/ColdBox.cfc

See Getting Started for more details.

Supported Databases

Quick supports all databases supported by qb.

Example

Here's a "quick" example to whet your appetite.

We'll show the database structure using a migrations file. This isn't required to use quick, but it is highly recommended.

// 2017_11_10_122835_create_users_table.cfc
component {

    function up() {
        schema.create( "users", function( table ) {
            table.increments( "id" );
            table.string( "username" ).unique();
            table.string( "email" ).unique();
            table.string( "password" );
            table.timestamp( "createdDate" );
            table.timestamp( "updatedDate" );
        } );
    }

}

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

    // the name of the table is the pluralized version of the model
    // all fields in a table are mapped by default
    // both of these points can be configured on a per-entity basis

}

// handlers/Users.cfc
component {

    // /users/:id
    function show( event, rc, prc ) {
        // this finds the User with an id of 1 and retrieves it
        prc.user = getInstance( "User" ).findOrFail( rc.id );
        event.setView( "users/show" );
    }

}

<!-- views/users/show.cfm -->
<cfoutput>
    <h1>Hi, #prc.user.getUsername()#!</h1>
</cfoutput>

Now that you've seen an example, dig in to what you can do with Quick!

Prior Art, Acknowledgements, and Thanks

Quick is backed by qb. Without qb, there is no Quick.

Quick is inspired heavily by Eloquent in Laravel. Thank you Taylor Otwell and the Laravel community for a great library.

Development of Quick is sponsored by Ortus Solutions. Thank you Ortus Solutions for investing in the future of CFML.

A hasManyThrough relationship is a many-to-many relationship. It is used when you want to access a related entity through another entity. The most common example for this is through a pivot table. For instance, a User may have multiple Permissions via a UserPermission entity. This allows you to store additional data on the UserPermission entity, like a createdDate .

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

    function permissions() {
       return hasManyThrough( "Permission" );
    }

}

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

    function users() {
       return hasManyThrough( "User" );
    }

}

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

    function user() {
        return belongsTo( "User" );
    }

    function permission() {
        return belongsTo( "Permission" );
    }

}

The first value passed to hasManyThrough is a WireBox mapping to the related entity.

The second value passed is a WireBox mapping to the intermediate entity.

Quick determines the foreign key of the relationship based on the entity name and key values. In this case, the Permission entity is assumed to have a permissionId foreign key. You can override this by passing a foreign key in as the third argument:

return hasManyThrough("Permission", "UserPermission", "FK_permissionID");

The secondKey is also determined by Quick. It is the foreign key of the current entity for the intermediate entity's table. In our example, this would be userId, since User is our entity and it is for the UserPermissions table. You can override this by passing in the secondKey as the fourth argument.

return hasManyThrough(
    "Permission",
    "UserPermission",
    "FK_permissionID",
    "FK_userID"
);

Lastly, the localKey and secondLocalKey are the primary keys of the entity and the intermediate entities. Usually this is just id. You can override these as the fifth and sixth argument.

return hasManyThrough(
    relationName = "Permission",
    intermediate = "UserPermission",
    firstKey = "FK_permissionID", // foreign key on the UserPermission table
    secondKey = "FK_userID", // foreign key on the Permission table
    localKey = "userID", // local key on the owning entity table
    secondLocalKey = "id" // local key on the UserPermission table
);

The inverse of hasManyThrough is also hasManyThrough. A note that the intermediate entity would use belongsTo relationships to link back to each side of the hasManyThrough relationship. These relationships are not needed to use a hasManyThrough relationship.