Configuring ColdFusion ORM

The first thing we need to do in order to use CF-ORM is to place the following code in the Application.cfc template.

  • The first line of code below directs ColdFusion to reload CF-ORM every time the page is refreshed.
    • While initially setting up CF-ORM, you will want to include this argument in your code
    • Once everything is ready for production, you should remove this line of code as consumes more resources
  • The ormenabled setting enables CF-ORM.
  • The dbcreate = "update" argument is used to have CF-ORM create the database tables and relationships for you.
  • The cfclocation argument is optional and is used when you keep all of your persistent cfc's in a particular location. Setting the cfclocation argument should also marginally improve performance as without it ColdFusion will search the entire folder structure to find any persistent cfc's.
<cfset ORMReload()="">
<cfset this.ormenabled="true">
<cfset this.datasource="GregorysBlog">
<!-- Allow ColdFusion to update and create the tables when they do not already exist. --->
<cfset this.ormSettings.dbcreate="update">
<cfset this.ormSettings.cfclocation="expandPath("/common/cfc/db/model/")">

Creating the tables and the relationships using persistent CFC's

The code below is a persistent CFC that will be used to create the table and its relationships. Like other CFC's, this CFC has the initial component declaration with the addition of the persistent argument which is set to true, and the properties map the column names. We'll go over the relationships in-depth later in the article.

  • Examining the PostId
    • The PostId is our primary key, it is annotated with the fieldtype="id"
    • generator determines how to increment the primary key
    • See Map the properties for more information
  • The ormtype is the datatype. These are generic values since CF-ORM is database agnostic. If you want finer control, you can substitute the ormtype with sqltype.
  • length specifies the column length. The default length for string datatypes is 255 characters.
  • I will not cover the details of the other properties here, refer to Map the properties for more information.
<cfcomponent displayName="Post" persistent="true" table="Post" output="no" hint="ORM logic for the new Post table">

<cfproperty name="PostId" fieldtype="id" generator="native" setter="false">
<!-- Many posts for one blog. --->
<cfproperty name="BlogRef" ormtype="int" fieldtype="many-to-one" cfc="Blog" fkcolumn="BlogRef" cascade="all">
<cfproperty name="UserRef" ormtype="int" fieldtype="many-to-one" cfc="Users" fkcolumn="UserRef" cascade="all">
<!-- The ThemeRef is optional. I am not going to make a relationship here as it will make a required constraint. --->
<cfproperty name="ThemeRef" ormtype="int"> 
<cfproperty name="PostUuid" ormtype="string" length="35" default=""> 
<cfproperty name="PostAlias" ormtype="string" length="100" default=""> 
<cfproperty name="Title" ormtype="string" length="125" default=""> 
<cfproperty name="Headline" ormtype="string" length="110" default=""> 
<cfproperty name="Body" ormtype="string" sqltype="varchar(max)" default=""> 
<cfproperty name="MoreBody" ormtype="string" sqltype="varchar(max)" default=""> 
<cfproperty name="AllowComment" ormtype="boolean" default="true"> 
<cfproperty name="NumViews" ormtype="int" default=""> 
<cfproperty name="Mailed" ormtype="boolean" default="false"> 
<cfproperty name="Released" ormtype="boolean" default="false"> 
<cfproperty name="Date" ormtype="timestamp"> 
</cfcomponent>

The cfproperty name argument

The cfproperty name argument can either be the name of the physical column in the database, the name of a CFC, or a reference to a new object that will not be placed into the database. We'll cover this important aspect later in the post when we discuss the fkcolumn argument.

The field type argument maps the relationships

CF-ORM has the following relationships: one-to-one: one-to-many: many-to-one: and many-to-many.

The order of the relationship keywords

When you map a relationship, the keyword (one or many) to the left is applied to the cfc that you're working on- and the keyword to the right is applied to the table that you're mapping to. For example, in the Post.cfc, the many-to-one relationship created below signifies that there are many posts (the CFC that is being worked on) for one blog (the table that I am making a reference to).

<cfproperty name="BlogRef" ormtype="int" fieldtype="many-to-one" cfc="Blog" fkcolumn="BlogRef" cascade="all">

Understanding the one and the many

The one keyword signifies that this must be unique. When a one-to-many relationship is found, CF-ORM will create a unique key in the database for the table that is being mapped by the CFC. So, in the case of the UserRef column (which maps to a user), there can be many users for one post. If the mapping for UserRef was one-to-one, a duplicate error will be raised when you try to insert the same user. Since the same author can make many posts, the many-to-one relationship must be used here. If you are receiving a duplicate error from the database when inserting new records, be sure to check your mapping a change any erroneous one-to-one mapping relationships.

one-to-one relationship

A one-to-one relationship is often applied to two tables when there is a set of optional data that is not required. For example, the PostRef, found in the code below, is a one-to-one relationship. I have a Post table that may have an optional image or video. The image or video is not required to be in the post table as there are posts that do not have any images or video. In order to consolidate the post table, I wanted to store images or video into a generic Mediatable, which is a different type of a 'thing'. Having the one-to-one relationship here helps me reduce the length of the column in the Post table, and allows me to organize the concept of two different 'things', i.e. a post and its associated media. Additionally, this relationship is quite useful for dropdowns; I can query the entire media table to make a dropdown list to allow the user to change the image or video. Often, I am surprised that a lot of folks have a negative impression of the one-to-one relationship. This relationship is often misunderstood. I find the one to one relationship to be quite useful. Since this is not relevant to this article, I'll reserve further elaboration for another article.

The many-to-one relationship

The MimeTypeRef in the code below has a many-to-one relationship. This could be a little confusing, you might rationally conclude that one image or video (the CFC that we are working on) would have one mime-type, but remember that whenever the one keyword is found, it signifies that it must be unique record. If we did put a one-to-one relationship here, we would receive a duplicate error message whenever we tried to put in the same mime type for a new record. With the many-to-one mapping, we can have many videos and images for one mime type.

<cfcomponent displayName="Media" persistent="true" table="Media" output="no" hint="ORM logic for the new Media table, can be an image or a video.">
	
 <cfproperty name="MediaId" fieldtype="id" generator="native" setter="false">
<!-- There can be many images and videos for a post --->
<cfproperty name="PostRef" ormtype="int" fieldtype="one-to-one" cfc="Post" fkcolumn="PostRef" cascade="all" missingrowignored="true">
<!-- Many images can have one mime type (if you have many-to-one you'll recive a 'Cannot insert duplicate key in object 'dbo.Media'. The duplicate key value is (11).' error ')--->
<cfproperty name="MimeTypeRef" ormtype="int" fieldtype="many-to-one" cfc="MimeType" fkcolumn="MimeTypeRef" cascade="all" missingrowignored="true">
<cfproperty name="FeaturedMedia" ormtype="boolean" default="false" hint="Is this an image or video that should be at the top of a blog post?"> 
<cfproperty name="MediaPath" ormtype="string" length="255" default=""> 
<cfproperty name="MediaUrl" ormtype="string" length="255" default=""> 
<cfproperty name="MediaTitle" ormtype="string" length="255" default="" hint="Also used for the alt tag."> 
<cfproperty name="MediaWidth" ormtype="string" length="25" default=""> 
<cfproperty name="MediaHeight" ormtype="string" length="25" default=""> 
<cfproperty name="MediaSize" ormtype="string" length="25" default=""> 
<cfproperty name="MediaVideoDuration" ormtype="string" default="" length="25" hint="Used for video types"> 
<cfproperty name="MediaVideoCoverUrl" ormtype="string" default="" length="255" hint="The image URL to cover the video. Used for video types"> 
<cfproperty name="MediaVideoSubTitleUrl" ormtype="string" default="" length="255" hint="The URL to the subtitle file. Used for video types"> 
<cfproperty name="Date" ormtype="timestamp"> 
</cfcomponent>

The one-to-many relationship

Simply put, the one-to-many relationship is the inverse of the many-to-one relationship. That is, there is one thing in the CFC that we are working on, to many things that we are mapping to. For example, in a blog, one Author can have many Posts. I don't often use this relationship as I tend to make the relationships from the other side (many-to-one).

The many-to-many relationship

A many-to-manyrelationship couldbe used to map a blog categoryto a blog post like so:

<cfcomponent displayName="Category" persistent="true" table="Category" output="no" hint="ORM logic for the new Category table">
<cfproperty name="CategoryId" fieldtype="id" generator="native" setter="false">
<!-- Many categories for one blog --->
<cfproperty name="PostRef" ormtype="int" fieldtype="many-to-many" cfc="Post" fkcolumn="PostRef" cascade="all">
<cfproperty name="CategoryUuid" ormtype="string" length="75" default="">
<cfproperty name="CategoryAlias" ormtype="string" length="75" default=""> 
<cfproperty name="Category" ormtype="string" length="125" default=""> 
<cfproperty name="Date" ormtype="timestamp"> 
</cfcomponent>

However, I replaced this many-to-many relationship with several many-to-one relationships placed into a junction table. A Junction table is also commonly defined as a:

  • cross-reference table
  • bridge table
  • join table
  • map table
  • intersection table
  • link table

While not technically correct, I personally refer to this as a lookup table as it closely matches the lookup table definition used in computer science (a table that contains a simple array). Whatever you may call it, a many-to-many, or a set of many-to-one relationships used in a junction table are bi-directional. Instead of using a many-to-many relationship, I used a many-to-one relationship to map a Post with a Category with a PostCategoryLookup junction table like so:

<cfcomponent displayName="PostCategoryLookup" persistent="true" table="PostCategoryLookup" output="no" hint="ORM logic for the new PostCategoryLookup table">
<cfproperty name="PostCategoryLookupId" fieldtype="id" generator="native" setter="false">
<!-- There can be many posts and categories --->
<cfproperty name="PostRef" ormtype="int" fieldtype="many-to-one" cfc="Post" fkcolumn="PostRef" singularname="Post" lazy="false" cascade="all">
<cfproperty name="CategoryRef" ormtype="int" fieldtype="many-to-one" cfc="Category" fkcolumn="CategoryRef" cascade="all">
<cfproperty name="Date" ormtype="timestamp"> 
</cfcomponent>

Mapping with the cfc and fkcolumn arguments

The cfc and fkcolumn argument determines the table and column that you're mapping to. The cfc argument is simple and needs no further explanation. It is the table that you want to create a foreign key too. The fkcolumn is not so simple, rather I find it to be an unnecessarily complex beast.

The fkcolumn argument

The fkcolumn argument takes on different characteristics depending on how its name was set.

If the cfproperty name is an ORM object reference

If we used the name as an object reference, such as Posts, we must use a name that is not already in use in the database, or the name of an existing CFC. A lot of documentation on the web uses the plural name of the table, such as the name that I used- i.e. Posts. However, John Whish, the author of the excellent book ColdFusion ORM, suggests using 'fk_TableName' to signify that the reference is a foreign key. If he applied his naming convention with my RelatedPost table, for example, he would use fk_RelatedPost. No matter what approach you use, what's important to recognize is that this 'Posts' or 'fk_RelatedPost' name is just a reference, and it won't actually exist in the database. If the name is a reference, the fkcolumn argument should be set to the name of the primary key of the cfc that you're mapping to. Here the cfc value is Post and the fkcolumn is PostId. Even though the cfproperty name is Posts, the column that will be created into the RelatedPost table will be the value of the fkcolumn, i.e. BlogId. However, Posts will be the column when I dump out the CFC object. Posts as an object reference in the persistent CFC:

<cfcomponent displayName="RelatedPost" persistent="true" table="RelatedPost" output="no" hint="ORM logic for the new RelatedPost table. This is used to indicate the related posts within the forum.">	
<cfproperty name="RelatedPostId" fieldtype="id" generator="native" setter="false">
<!-- There are many related posts for one post --->
<cfproperty name="Posts" ormtype="int" fieldtype="many-to-one" cfc="Post" fkcolumn="PostId" singularname="Post" cascade="all" lazy="false">
<!-- There is one related post that we are pointing to --->
<cfproperty name="Date" ormtype="timestamp">		
</cfcomponent>

PostId is stored as a column in the database:

Posts is the item in the CFC database object:

This works fine when working with a single foreign key reference to the Post.PostId column, but what if we wanted more than one reference to Post.PostId? The RelatedPost table maps a relationship to a single post to all other posts that relate to it. At the bottom of many of my own posts, you will see a Related Resources label that has links to other posts. The RelatedPost database table was designed to handle this. So here, we need two references to the Post table's primary key- PostId

However, when we use more than one PostIdin the fkcolumn, yet use two objects as the cfproperty name (Posts and RelatedPosts), we will get an error.

<cfcomponent displayName="RelatedPost" persistent="true" table="RelatedPost" output="no" hint="ORM logic for the new RelatedPost table. This is used to indicate the related posts within the forum.">
<cfproperty name="RelatedPostId" fieldtype="id" generator="native" setter="false">
<!-- There are many related posts for one post --->
<cfproperty name="Posts" ormtype="int" fieldtype="many-to-one" cfc="Post" fkcolumn="PostId" singularname="Post" cascade="all" lazy="false">
<!-- There is one related post that we are pointing to --->
<cfproperty name="RelatedPosts" ormtype="int" fieldtype="one-to-one" cfc="Post" fkcolumn="PostId" singularname="Post" cascade="all" lazy="false">
<cfproperty name="Date" ormtype="timestamp"> 
</cfcomponent>

This is the error message raised when there is more than one reference to the Post.PostId column in the database: Repeated column in mapping for entity: RelatedPost column: PostId (should be mapped with insert="false" update="false")This error signifies that this column needs to be a read-only post. However, as with many other ORM-related errors, this is incorrect. The problem here is that CF-ORM is trying to create two BlogId references into the RelatedPost table. There is several ways to fix this, but the best way that I found is to use the fkcolumn to point the foreign key back to itself.

Using the fkcolumn to point back to its self

Another way to use the cfproperty name and the fkcolumn is to create a new column into the database using the name attribute, and then use the same column name in fkcolumn to point back to itself. Let's look at the code:

<cfcomponent displayName="RelatedPost" persistent="true" table="RelatedPost" output="no" hint="ORM logic for the new RelatedPost table. This is used to indicate the related posts within the forum.">
<cfproperty name="RelatedPostId" fieldtype="id" generator="native" setter="false">
<!-- There are many related posts for one post --->
<cfproperty name="PostRef" ormtype="int" fieldtype="many-to-one" cfc="Post" fkcolumn="PostRef" cascade="all" lazy="false">
<!-- There is one related post that we are pointing to --->
<cfproperty name="RelatedPostRef" ormtype="int" fieldtype="one-to-one" cfc="Post" fkcolumn="RelatedPostRef" cascade="all" lazy="false">  
<cfproperty name="Date" ormtype="timestamp"> 
</cfcomponent>

Instead of creating an object reference using the name attribute, we are using PostRef and RelatedPostRef to create two new database columns. The cfc that we are pointing to remains the same, we want to use the primary key found in the Post table, and we are also using the same that we used in the fkcolumn to point back to itself. This creates two new columns, the PostRef and the RelatedPostRef into the database, and creates the relationship to the PostId using these new columns. Viola! the error is gone, and now the database model is an exact replica of the ORM object model! The PostRef relationship in the database

The RelatedPostRef relationship in the database

And the ORM Object that is now identical to the database model

I must admit that I am not a CF-ORM expert and stumbled upon this approach while discovering different ways to solve some issues. However, it is now my preferred approach. It is my personal preference to use TableName Ref for all of my foreign keys, and use TableName Id for my primary keys. I personally think that it is easier to read, and it allows me to quickly identify what I am working with. I have been using the same name database naming conventions for many many years. I also like the fact that both my object and database models are now uniform.

Final Relationship Notes

You will notice that anytime we create a new relationship with CF-ORM, the columns that hold the relationships will be placed at the end of the table. The order of the columns indicated with the persistent CFC will not be enforced. However, you can change the order of the columns after the tables are created, and CF-ORM won't modify the new order. There is a lot of flexibility with CF-ORM. However, I personally think that the implementation is confusing and would prefer that CF-ORM be more opinionated. One of the reasons why I don't like PHP is that there are far too many ways in PHP to perform a task. This results in added complexity. There is a lot of room for improvement with CF-ORM. I wish that there was an argument in allowed in the cfproperty to be used as a foreign key, such as a foreignKey="true", or something like fkeycolumn="this" or "self".