CFWheels Forum and Calculated Properties
Continuing with my CFWheels powered Forum application, I had a desire to include a thread count and a post count for each forum listed on the main forum page. In my old spaghetti coding days, I would first have used a retrieveForums query. Then in my display code, while looping over the retrieveForums recordset, I would then query the threads table passing in the forumID foreign key to obtain a thread count for each forumID. In a similar fashion, I would get a post count for each forumID. The code below illustrates this scenario.
<cfquery name="getForums" datasource="myDSN">
SELECT * FROM forums
<cfquery>
<cfloop query="getForums">
<cfquery name="getThreadCount" datasource="myDSN">
SELECT COUNT(*)
FROM threads
WHERE threads.forumID = #getForums.id#
</cfquery>
</cfloop>
While this is not the CFWheels way, I found out that it is very easy to convert this code into CFWheels syntax with the same results.
Enter Calculated Properties. Searching the CFWheels blog and documentation, I was able to find this little gem which allows me to assign additional properties to a given object based on a SQL query. In this manner, property then becomes part of that object without the need to loop over a query to retrieve it as in the above code block. This property can then be called just like any other property of that object. Honestly, I’m not entirely sure I worded that correctly, but the bottom line is the result is the same as my spaghetti code example. Let’s look at the new code in my forum model.
<cfcomponent extends="Model" output="false">
<cffunction name="init">
<!--- Associations --->
<cfset hasMany("threads")/>
<!--- Properties --->
<cfset property(name="postCount", sql="(SELECT COUNT(posts.id) FROM posts INNER JOIN threads ON posts.threadid = threads.id WHERE threads.forumID = forums.id)")/>
<cfset property(name="threadCount", sql="SELECT COUNT(threads.id) FROM threads WHERE threads.forumID = forums.id")/>
<!--- Validations --->
<cfset validatesPresenceOf(property="forumName", message="Forum name is required.")/>
<cfset validatesPresenceOf(property="forumDescription", message="Description is required.")/>
</cffunction>
</cfcomponent>
And the new display code.
<tbody>
<cfoutput query="forums">
<!--- Here we are outputting the forums query. The forums query will return every forum with a post count --->
<tr height="35" <cfif currentRow MOD 2>bgcolor="##ebebeb"</cfif>>
<td><br/></td>
<td> #linkTo(text="#forumName#", controller="forums", action="viewForum", key="#id#")#<br/> #ParagraphFormat(forumDescription)# </td>
<td><br/></td>
<td>#val(threadCount)#</td>
<td align="center">#val(postCount)#</td>
</tr>
</cfoutput>
</tbody>
My new calculated properties–threadCount and postCount are now part of the forums object and can be used just like any other field/property in that object.
I hope my explanation makes sense. Calculated properties will come in handy as I move forward with this project. CFWheels makes another programming task so much simpler.
