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.

About these ads