Dumb ColdFusion ORM Mistakes Vol. 2 - computed properties
Posted on Apr 14, 2010
ColdFusion ORM has a powerful feature that allows you to include properties in your object that contain computed data using a formula. For example, the application I am currently working on has Poll objects that contain Answers (as in the response options) and responses (i.e. the user submitted vote). In this scenario, you may want to allow that when you retrieve a Poll object it has knowledge of how many votes have been cast for that poll. In addition, you would likely want each Answer to have knowledge of how many times it has been voted for. In doing so it would be very easy to render the poll results. In this scenario, your Poll would have a computed property for your Poll called totalVotesCast and your Answer object may similarly contain a computed property for responseCount (the names aren't important here...so don't focus on it). This is actually very easy to do using ORM computed properties, with one gotcha that caught me.
Computed properties are easy to define as a cfproperty in your object as they simply require a name attribute and a formula. Now, knowing that ColdFusion ORM uses Hibernate under the covers and looking at the very simplistic provided examples and instructions, you might assume, as I did, that the formula attribute takes HQL (i.e. the Hibernate Query Language). This would mean that you could use object names and properties within the formula as well as "assumed" joins (since Hibernate already knows about your table relationships). This assumption, however, would be totally wrong. As it is, the formula attribute uses straight SQL. You can see this in action if you have the logSQL property turned on under ORMsettings in your Application.cfc. In doing so, you can watch the query that comes through via the Console (you can do this via ColdFusion Builder if you started your ColdFusion server via Builder's server tab). The contents of your formula attribute are essentially pasted into your query as a subselect.
Take my Poll and Answer scenario. Let's say your Poll had a property defined to get the total vote count like so:
property name="totalVotesCast" formula="select count(*) from PollResponse pr join PollAnswer pa on pr.pollChoiceID = pa.PollChoiceID where pa.pollID = pollID";
The resulting query might look something like this:
select
poll0_.POLLID as POLLID363_,
poll0_.TITLE as TITLE363_,
poll0_.QUESTION as QUESTION363_,
(select
count(*)
from
PollResponse pa
join
PollAnswer pa
on pr.pollChoiceID = pa.PollChoiceID
where
pa.pollID = poll0_.pollID) as formula34_
from
poll poll0_
Notice that the subquery for the column formula34_ is essentially a direct copy of the formula attribute in my cfproperty with only the local pollID replaced in the join. As Bob Silverberg noted via Twitter in response to me, this also means that the property does not update until it is reloaded (so, in this scenario, even if a vote was submitted during the course of the request, your Poll objects totalVotesCast property would not reflect the new tally.
Comments
There are currently no comments for this entry...be the first!