It's two and a half years since I first decided to give ORM^* a whirl on a small ColdFusion app. At the time I was hesitant, daunted by its apparent complexity.

^* This post refers to the Adobe ColdFusion 9.0.1 implementation.

Now all the systems I currently work on use it. I love how it encourages you to adopt a fully OO mindset when coding without having to set up all the machinery to load and save the object data and manage the dependencies. You just define your model and CF's ORM then provides you with a ready-made API for working with and persisting it. And with its intelligent caching layer, I've found it performs just as well if not better than my previous ORM-less apps.

But I wouldn't want to misrepresent my transition to ORM as entirely trouble-free. Certain detailed aspects have had me scratching my head and pleading with Google for an answer.

I don't necessarily mean bugs (although some may be considered as such), but minor, often edge-case behaviours I personally didn't expect or find intuitive and which CF's exception feedback failed to explain. In short: quirks.

Quirk number one concerns the ordering of child objects in a specific kind of relationship.

One page, many documents

Let's assume we have a Page object which can have zero or more Documents attached to it. There are a couple of rules:

1. Each document will only be attached to one page, so we have a one-to-many relationship between pages and documents.
2. Each document has a dateCreated column and documents listed on a page need to be shown in the order they were created.

Normally this would be straightforward: the documents table would have a foreign key column (pageID) to record which page it was attached to and the ORM relationship would be expressed thus:

Page.cfc

component persistent="true" table="pages"{

	property name="ID" fieldType="id" generator="native";
	
	property name="documents"
		singular="document"
		fieldType="one-to-many"
		cfc="Document"
		fkColumn="pageID"
		orderBy="dateCreated ASC";
		
	public function init(){
		return this;
	}
}

But even though each document will only be associated with one page, documents may also be attached to other types of objects, mail messages for instance. I don't want to have multiple documents tables—one for page documents, another for message documents etc.—where only the foreign key column differs.

So I'll just have one documents table, remove the pageID foreign key column, and instead record the association with a link table called pages_documents containing just two primary key columns: pageID and documentID. I can set up similarly minimal link tables for messages and other objects that have documents.

Our Page component now looks like this:

Page.cfc

component persistent="true" table="pages"{

	property name="ID" fieldType="id" generator="native";
	
	property name="documents"
		singular="document"
		fieldType="one-to-many"
		cfc="Document"
		linkTable="pages_documents"
		fkColumn="pageID"
		inverseJoinColumn="documentID"
		orderBy="dateCreated ASC";

	public function init(){
		return this;
	}
}

Out of order

However, if we try to output the documents for a particular page...

request.page	=	EntityLoadByPK( "Page",1 );
WriteDump( request.page.getDocuments() );

...we get the following exception:

could not initialize a collection: [Page.documents#1]

Although there's clearly a problem loading the page's documents we aren't told the exact cause.

It turns out it's the dateCreated column in the orderBy attribute, which CF is looking for in the link table pages_documents instead of in the documents table.

Why is it looking for it there? Because the relationship is one-to-many and it assumes I have the more straightforward 2-table setup, even though I have specified an additional link table and without the orderBy attribute the relationship works perfectly well.

The A less than ideal solution

This is easily fixed by changing the Page-Document relationship to many-to-many even though that's not what it is. Now CF/Hibernate will understand it needs to look in documents not pages_documents for the orderBy column.

property name="documents"
		singular="document"
		fieldType="many-to-many"
		cfc="Document"
		linkTable="pages_documents"
		fkColumn="pageID"
		inverseJoinColumn="documentID"
		orderBy="dateCreated ASC";

Even quirkier

Update 10 November 2013: Mike asked in the comments about filtering the documents on a "status" column in the documents table so that only those with a status of "open" would be returned.

The Adobe docs suggest this is straightforward (my emphasis):

In one-to-many and many-to-many relationships, an array or struct is retrieved. Filters can be applied to retrieve a subset of the associated objects. The filter can be specified in the where attribute, which is an SQL where clause.

However, adding a where attribute to the "documents" property in Page.cfc...

property name="documents"
		singular="document"
		fieldType="many-to-many"
		cfc="Document"
		linkTable="pages_documents"
		fkColumn="pageID"
		inverseJoinColumn="documentID"
		where="status='open'"
		orderBy="dateCreated ASC";

...results once again in:

could not initialize a collection: [Page.documents#1].

Digging into the SQL generated by Hibernate, we see that while it is now correctly applying the orderBy statement to the documents table, the new where filter is being applied to the link table:

select
    documents0_.pageID as pageID6_1_,
    documents0_.documentID as documentID1_,
    document1_.ID as ID5_0_,
    document1_.dateCreated as dateCrea2_5_0_,
    document1_.status as status5_0_
from
    pages_documents documents0_
inner join
    documents document1_
        on documents0_.documentID=document1_.ID
where
    (
        documents0_.status='open'
    )
    and documents0_.pageID=?
order by
    document1_.dateCreated asc

Faced with this level of quirkiness it seems best to eschew any further wrangling with CF's API and instead try a more direct way of controlling Hibernate's behaviour, namely the createFilter() method as described in detail by Bob Silverberg.

A better solution

We simply create a custom getter function in Page.cfc which overrides the auto-generated getDocuments() method as follows:

public array function getDocuments(){
  return OrmGetSession().createFilter( variables.documents,"where status='open' order by dateCreated").list();
}

This gives us both the filter on "status" and our desired ordering, meaning we can revert to our original and more accurately defined relationship definition.

property name="documents"
		singular="document"
		fieldType="one-to-many"
		cfc="Document"
		linkTable="pages_documents"
		fkColumn="pageID"
		inverseJoinColumn="documentID";