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 hasMany relationship is a one-to-many relationship. For instance, a User may have multiple Posts.

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

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

}

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:

return hasMany( "Post", "FK_userID" );

The inverse of hasMany is also belongsTo.

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

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

}

Inserting & Updating

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

save

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

var post = getInstance( "Post" ).fill( {
    "title" = "My Post",
    "body" = "Hello, world!"
} );

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

user.posts().save( post );

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.

create

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

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

user.posts().create( {
    "title" = "My Post",
    "body" = "Hello, world!"
} );

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 belongsTo side of the relationship or by deleting the belongsTo side of the relationship.

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 hasMany( "Post", "FK_userID" );

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.

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 intermediateKey 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 intermediateKey as the fourth argument.

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

Lastly, the owningKey is the primary key of the entity. Usually this is just id. You can override this by passing in the owningKey as the fifth argument.

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

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.

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.

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

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

}

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.

// 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" );
    }

}

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();

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" );

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" );
    }

}

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.

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

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

}

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

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

}

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.

permissions_users
- permissionId
- userId

As you can see, Quick uses a convention of combining the entity table names 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.

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

    function permissions() {
       return belongsToMany( "Permission", "user_permission_map" );
    }

}

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:

return belongsToMany( "Permission", "user_permission_map", "FK_UserId", "FK_PermissionID" );

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

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

    function user() {
        belongsToMany( "User", "user_permission_map", "FK_PermissionID", "FK_UserId" );
    }

}

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.

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

    function userPermissions() {
        return hasMany( "UserPermission" );
    }

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

}

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.

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

var tag = getInstance( "Tag" ).create( "miscellaneous" );

// pass an id
post.tags().attach( tag.getId() );
// or pass an entity
post.tags().attach( tag );

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.

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

var tag = getInstance( "Tag" ).create( "miscellaneous" );

// pass an id
post.tags().detach( tag.getId() );
// or pass an entity
post.tags().detach( tag );

sync

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

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

post.tags().sync( [ 2, 3, 6 ] );

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

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.get()#" 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( "user" )
    .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" );

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.

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

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.

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" );
    }

}