ColdFusion on Wheels - table relationships

May 28, 2008

If you have two tables in your database that have a one to many relationship then you need to set up the relationship between the two in ColdFusion on Wheels.

For example if you have a posts table and a comments table (Note that ColdFusion on Wheels has built in assumptions and one of these is that table names are plural), the posts table can have multiple comments for one post (this is often referred to as a "one to many" relationship). The comments table will need to have foreign key called post_id which references the id primary key field in the posts table.

A simple Posts Table:

Column Data Type Usage
id integer primary key
title varchar  
published datetime  
content text  

A simple Comments Table:

Column Data Type Usage
id integer primary key
author varchar  
published datetime  
content text  
post_id integer foreign key

 

Another assumption that ColdFusion on Wheels makes is that the foreign key is named as shown above for the post_id column in the comments table.

To tell ColdFusion on Wheels about this relationship you now need to add two CFCs to your model directory.

post.cfc

<cfcomponent extends="model">
  <cffunction name="init">
    <cfset hasMany("comments") />
  </cffunction>
</cfcomponent>

comment.cfc

<cfcomponent extends="model">
  <cffunction name="init">
    <cfset belongsTo("post") />
  </cffunction>
</cfcomponent>

Note that the CFC names should be singular version of the plural table names.

The hasMany() method used in post.cfc tells ColdFusion on Wheels that the posts table has many comments.

The belongsTo() method used in comment.cfc tells ColdFusion on Wheels that the posts table has many comments.

Now that the relationships have been established you'll probably want to retrieve some data! If you want to get a particular post and all of it's comments from the database you do this

<!--- get the post for the id passed as a url parameter --->
<cfset post = model("post").findById(URL.post_id) /> 
<!--- get all comments for the post --->
<cfset comments = post.comments() />

It is also possible to get all the comments and the post in one go

<!--- get the post and all related comments for the id passed as a url parameter --->
<cfset postWithComments = model('comment').findAll(where="post_id=" & URL.post_id, 
include="post") />

If you're interested in finding out more I suggest you have a look at this video by Mike Haggerty about creating and updating database records.

I'd like to thank Chris Peters for the heads up on how to do this.

4 comments

  1. Very cool. That's always been one of the cooler features of Rails. You still have to write more complex SQL manually, but for the straightforward stuff, there's no reason it should be that difficult.

    Comment by Andy Matthews – May 28, 2008
  2. Hi Andy,

    I don't know if you've watched the video I mentioned in my post, but the insert and update features are almost magical!

    Comment by John Whish – May 29, 2008
  3. Thanks for the write-up, John. You should consider writing some docs for the project!

    Comment by Chris Peters – June 05, 2008
  4. Hi Chris,

    Feel free to use the article if you think it is any good. I don't think I know the framework well enough to write docs but I'll definitely post more articles on my blog as find out more :)

    Comment by John Whish – June 08, 2008

Leave a comment

If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)

Please note: If you haven't commented before, then your comments will be moderated before they are displayed.