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.

Usage

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" accessors="true" {

    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 hasOne method specifying your parent table's custom key.

return hasOne( "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" accessors="true" {

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

}

withDefault

HasOne relationships can be configured to return a default entity if no entity is found. This is done by calling withDefault on the relationship object.

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

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

}

Called this way will return a new unloaded entity with no data. You can also specify any default attributes data by passing in a struct of data to withDefault.

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

    function profile() {
       return hasOne( "UserProfile" ).withDefault( {
           "showHints": true
       } );
    }

}

Signature

Returns a HasOne relationship between this entity and the entity defined by relationName.

Visualizer

Usage

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 hasMany method specifying your parent table's custom key.

The inverse of hasMany is .

Inserting & Updating

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

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.

Signature

Returns a HasMany relationship between this entity and the entity defined by relationName.

Visualizer

Usage

A belongsTo relationship is a many-to-one relationship. For instance, a Post may belong to a 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:

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 belongsTo is or .

Updating

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

Removing

To remove a belongsTo relationship, use the dissociate method.

Relationship Setter

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

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

withDefault

BelongsTo relationships can be configured to return a default entity if no entity is found. This is done by calling withDefault on the relationship object.

Called this way will return a new unloaded entity with no data. You can also specify any default attributes data by passing in a struct of data to withDefault.

Signature

Returns a BelongsTo relationship between this entity and the entity defined by relationName.

Visualizer

Usage

A hasOneThrough relationship is either a many-to-one or a one-to-one relationship. It is used when you want to access a related entity through one or more intermediate entities. For instance, a Team may have one latest Post through a User.

The only value needed for hasOneThrough is an array of relationship function names to walk through to get to the related entity. The first relationship function name in the array must exist on the current entity. Each subsequent function name must exist on the related entity of the previous relationship result. For our previous example, members must be a relationship function on Team. posts must then be a relationship function on the related entity resulting from calling Team.members(). This returns a hasMany relationship where the related entity is User. So, User must have a posts relationship function. That is the end of the relationship function names array, so the related entity resulting from calling User.posts() is our final entity which is Post.

This approach can scale to as many related entities as you need. For instance, let's expand the previous example to include an Office that houses many Teams.

withDefault

HasOneThrough relationships can be configured to return a default entity if no entity is found. This is done by calling withDefault on the relationship object.

Called this way will return a new unloaded entity with no data. You can also specify any default attributes data by passing in a struct of data to withDefault.

Signature

Usage

A belongsToThrough relationship is the one side of a many-to-one relationship with an intermediate entity. It is used when you want to access a related, owning entity through one or more intermediate entities. For instance, a Post may belong to a Team via a User.

The only value needed for belongsToThrough is an array of relationship function names to walk through to get to the related entity. The first relationship function name in the array must exist on the current entity. Each subsequent function name must exist on the related entity of the previous relationship result. For our previous example, author must be a relationship function on Post. team must then be a relationship function on the related entity resulting from calling Post.author(). This returns a belongsTo relationship where the related entity is User. So, User must have a team relationship function. That is the end of the relationship function names array, so the related entity resulting from calling User.team() is our final entity which is Team.

This approach can scale to as many related entities as you need. For instance, let's expand the previous example to include an Office that houses many Teams.

withDefault

HasOneThrough relationships can be configured to return a default entity if no entity is found. This is done by calling withDefault on the relationship object.

Called this way will return a new unloaded entity with no data. You can also specify any default attributes data by passing in a struct of data to withDefault.

Signature

Usage

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.

Signature

Returns a BelongsToMany relationship between this entity and the entity defined by relationName.

Visualizer

Usage

A hasManyThrough relationship is either a one-to-many or a many-to-many relationship. It is used when you want to access related entities through one or more intermediate entities. 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 .

The only value needed for hasManyThrough is an array of relationship function names to walk through to get to the related entity. The first relationship function name in the array must exist on the current entity. Each subsequent function name must exist on the related entity of the previous relationship result. For our previous example, userPermissions must be a relationship function on User. permission must then be a relationship function on the related entity resulting from calling User.userPermissions(). This returns a hasMany relationship where the related entity is UserPermission. So, UserPermission must have a permission relationship function. That is the end of the relationship function names array, so the related entity resulting from calling UserPermission.permission() is our final entity which is Permission.

Let's take a look at another example. HasManyThrough relationships can go up and down the relationship chain. Here's an example of finding a User's teammates

The inverse of hasManyThrough is either a belongsToThrough or a hasManyThrough relationship.

This approach can scale to as many related entities as you need. For instance, let's expand the previous example to include an Office that houses many Teams.

This next example can get a little gnarly - you can include other hasManyThrough relationships in a hasManyThrough relationship function names array. You can rewrite the officemates relationship like so:

As you can see, this is a very powerful relationship type that can save you many unnecessary queries to get the data you need.

Signature

Usage

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.

Signature

Returns a polymorphicBelongsTo relationship between this entity and the entity defined by relationName.

Visualizer

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.

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

Usage

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" accessors="true" {

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

}

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

    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" accessors="true" {

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

}

Signature

Returns a polymorphicHasMany relationship between this entity and the entity defined by relationName.

Visualizer

One common type of subselect field is the count of related entites. For instance, you may want to load a Post or a list of Posts with the count of Comments on each Post. You can reuse your existing relationship definitions and add this count using the withCount method.

withCount

Adds a count of related entities as a subselect property. Relationships can be constrained at runtime by passing a struct where the key is the relationship name and the value is a function to constrain the query.

By default, you will access the returned count using the relationship name appended with Count, i.e. comments will be available under commentsCount.

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

You can alias the count attribute using the AS syntax as follows:

var post = getInstance( "Post" )
	.withCount( "comments AS myCommentsCount" )
	.findOrFail( 1 );
	
post.getMyCommentsCount();

This is especially useful as you can dynamically constrain counts at runtime using the same struct syntax as eager loading with the with function.

var post = getInstance( "Post" )
	.withCount( [
	    "comments AS allCommentsCount",
	    { "comments AS pendingCommentsCount": function( q ) {
	        q.where( "approved", 0 );
	    } },
	    { "comments AS approvedCommentsCount": function( q ) {
	        q.where( "approved", 1 );
	    } }
	] )
	.findOrFail( 1 );

post.getAllCommentsCount();	
post.getPendingCommentsCount();
post.getApprovedCommentsCount();

To order by a relationship field, you will use a dot-delimited syntax.

getInstance( "Post" ).orderBy( "author.name" );

The last item in the dot-delimited string should be an attribute on the related entity.

Nested relationships are also supported. Continue to chain relationships in your dot-delimited string until arriving at the desired entity. Remember to end with the attribute to order by.

getInstance( "Post" ).orderBy( "author.team.name" );

If you desire to be explicit, you can use the orderByRelated method, which is what is being called under the hood.

getInstance( "Post" ).orderByRelated( "author.team", "name" );
// or
getInstance( "Post" ).orderByRelated( [ "author", "team" ], "name" );

You might prefer the explicitness of this method, but it cannot handle normal orders like orderBy. Use whichever method you prefer.

When querying an entity, you may want to restrict the query based on the existence or absence of a related entity. You can do that using the following four methods:

has

Checks for the existence of a relationship when executing the query.

By default, a has constraint will only return entities that have one or more of the related entity.

getInstance( "User" ).has( "posts" ).get();

An optional operator and count can be added to the call.

getInstance( "User" ).has( "posts", ">", 2 ).get();

Nested relationships can be checked by passing a dot-delimited string of relationships.

getInstance( "User" ).has( "posts.comments" ).get();

doesntHave

Checks for the absence of a relationship when executing the query.

By default, a doesntHave constraint will only return entities that have zero of the related entity.

getInstance( "User" ).doesntHave( "posts" ).get();

An optional operator and count can be added to the call.

getInstance( "User" ).doesntHave( "posts", "<=", 1 ).get();

Nested relationships can be checked by passing a dot-delimited string of relationships.

getInstance( "User" ).doesntHave( "posts.comments" ).get();

whereHas

When you need to have more control over the relationship constraint, you can use whereHas. This method operates similarly to has but also accepts a callback to configure the relationship constraint.

The whereHas callback is passed a builder instance configured according to the relationship. You may call any entity or query builder methods on it as usual.

getInstance( "User" )
    .whereHas( "posts", function( q ) {
	      q.where( "body", "like", "%different%" );
    } )
		.get();

When you specify a nested relationship, the builder instance is configured for the last relationship specified.

getInstance( "User" )
    .whereHas( "posts.comments", function( q ) {
	      q.where( "body", "like", "%great%" );
	  } )
	  .get();

An optional operator and count can be added to the call, as well.

getInstance( "User" )
    .whereHas( "posts.comments", function( q ) {
	      q.where( "body", "like", "%great%" );
	  }, ">", 2 )
	  .get();

whereDoesntHave

The whereDoesntHave callback is passed a builder instance configured according to the relationship. You may call any entity or query builder methods on it as usual.

getInstance( "User" )
    .whereDoesntHave( "posts", function( q ) {
	      q.where( "body", "like", "%different%" );
    } )
		.get();

When you specify a nested relationship, the builder instance is configured for the last relationship specified.

getInstance( "User" )
    .whereDoesntHave( "posts.comments", function( q ) {
	      q.where( "body", "like", "%great%" );
	  } )
	  .get();

An optional operator and count can be added to the call, as well.

getInstance( "User" )
    .whereDoesntHave( "posts.comments", function( q ) {
	      q.where( "body", "like", "%great%" );
	  }, ">", 2 )
	  .get();