ColdFusion ORM calculated values

09/07/2010 | ORM, ColdFusion | 17 Comments

A calculated value is a read only property that is calculated using sql expressions. In this example we are building a blog application that has posts and comments. First we have a post entity. I have stripped these down for this example so we can look at the root of the issue. component persistent="true" extends="BaseEntity" { property name="title"; property name="body" ormtype="text"; property name="alias"; property name="allowComments" type="boolean"; property name="featured" type="boolean"; property name="released" type="boolean"; property name="views" ormtype="integer"; property name="comments" fieldtype="one-to-many" cfc="Comment" fkcolumn="post_id" singularname="comment" inverse="true"; } And then we have a comment entity. component persistent="true" extends="BaseEntity" { property name="name"; property name="email"; property name="url"; property name="body"; property name="post" fieldtype="many-to-one" cfc="Post" fkcolumn="post_id"; }

So far this is pretty basic stuff, nothing really special going on here. Now we need to grab a record from our database and display it, easy enough. <cfset post = entityLoadByPK("Post",1)> There is a major problem here with our display though. How would we get a count of the number of comments for this post? You could count the array of comments. <cfset commentCount = arrayLen(post.getComments())> While this would work your going to have to do this 2x on the post display page now. One time for the comment count and then a 2nd time to display the comments. In a post where there is only 1 or 2 comments this will not be a big issue but you can imagine the trouble this would cause with a large number of comments. A better solution to this problem is to use a calculated value. We can actually create this commentCount as a property of our post entity. component persistent="true" extends="BaseEntity" { property name="title"; property name="body" ormtype="text"; property name="alias"; property name="allowComments" type="boolean"; property name="featured" type="boolean"; property name="released" type="boolean"; property name="views" ormtype="integer"; property name="commentCount" formula="select count(c.id) from Comment c where c.post_id = id"; property name="comments" fieldtype="one-to-many" cfc="Comment" fkcolumn="post_id" singularname="comment" inverse="true"; } All we are doing here using SQL to the count. The id of the Post Entity is id (comes from the base Entity) so we can do a lookup in the comment table based on post_id being equal to id. Now if we want to output the count all we have to do is. <cfset commentCount = post.getCommentCount()>

This entry was posted on September 7, 2010 at 9:13 AM and has received 3731 views. Comments 17 | Print this entry.

TweetBacks

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)

[Add Comment] [Subscribe to Comments]

#1 by Raymond Camden on 9/7/10 - 8:59 AM

While I agree that getCommentCount() is less typing than arrayLen(post.getComments()), you do know that in Hibernate, it will not be much of a performance gain if your relationship is extra lazy? arrayLen() is very light weight in those cases, and I believe acts the exact same as the SQL you had to hand code.
#2 by Dan Vega on 9/7/10 - 9:07 AM

I would agree with you and thats where fine tuning your apps really comes into play. By default though the relationship is Lazy loaded which means when you call getComments() the entire collection is fully loaded. I guess I never thought of comparing the SQL in the different scenarios, I will have to try that when I get a chance.
#3 by Dan Vega on 9/7/10 - 9:09 AM

I guess once its loaded though its loaded so my argument of hitting it 2x doesn't really hold up. I guess you could really see the performance on a listing of posts. If you just want to show the number of comments for a post, you really wouldn't to be loaded the collection of comments for each iteration of posts.
#4 by Raymond Camden on 9/7/10 - 9:14 AM

Agreed - which is why extra lazy is so cool. arrayLen() won't load anything - it will just count.
#5 by Rick O on 9/7/10 - 9:23 AM

Why not just denormalize a bit and have an actual Count property that is updated as comments are added? If done right, with an idempotent query, it should Just Work.
#6 by Terrence Ryan on 9/7/10 - 9:49 AM

I ran into a slight issue using calculated values with Apptacular. What you add here is SQL and not HQL, so its either database specific, or you have to write very generalized SQL. So I ended up using HQL to create these type of functions.

Not a deal breaker for everyone, but be aware
#7 by Raymond Camden on 9/7/10 - 9:54 AM

In IM I asked Dan to test a select rand() to see if N calls in one request returns the same value. I bet it doesn't - which makes this even less performant than arrayLen() on an extra lazy.
#8 by Tony Garcia on 9/7/10 - 10:07 AM

Cool stuff, Dan! Thanks for posting this. Even if there isn't a performance difference over arraylen(), from a coding point of view I think I prefer the nice encapsulation that the calculated property provides.
#9 by Dan Vega on 9/7/10 - 10:10 AM

property name="random" formula="select rand()";

This will give you a random number each time.
#10 by Chris Blackwell on 9/7/10 - 10:11 AM

One problem with using SQL to provide a computed property is that it will not get updated when you add an extra comment to the post. It will always return the same value until the entity is reloaded. That could be confusing, so i roll my own getWhateverCount() using arrayLen().

There is also a gotcha with this method too. A newly created instance of a persistent cfc does not contain an empty array (or struct) for its realtionship properties, they are actually undefined. So you shouldn't rely on getCooments() returning an array and should test with isNull().

My base persistent cfc contains a utility method for this purpose

<code>
numeric function getCommentCount() {
   return countObjectsInRelationship("Comments");
}

private numeric function countObjectsInRelationship(required string key) {
   if(structKeyExists(variables, key) AND !isNull(variables[key])) {
      if(isArray(variables[key]))
         return arrayLen(variables[key]);
      else if(isStruct(variables[key]))
         return structCount(variables[key]);
   }
   return 0;
}
</code>
#11 by Raymond Camden on 9/7/10 - 10:12 AM

Thanks for testing that Dan. If you overwrite the getCommentCount with your own getter, can you cache the value of variables.commentcount? Does it exists in the variables scope?
#12 by Raymond Camden on 9/7/10 - 10:16 AM

Chris, doesn't Dan's comment about the rand returning new values each time mean that you are wrong about the value not being updated? Since it is "real" SQL and HQL is not involved, it should always be right, correct? Unless you have modified the object in the current Hibernate session and it hasn't been persisted yet.
#13 by Dan Vega on 9/7/10 - 10:18 AM

Right - I am going to go ahead and disagree with you as well Chris. In my blog application if I add a new comment to the post I am returned to the post and the new number is correct. This is because we are using sql to retrieve the count and not working with a cached hibernate object.
#14 by Chris Blackwell on 9/7/10 - 10:20 AM

Sorry ray, i wasn't very clear. Using SQL will reflect the state of the object in the database, not the currently loaded object in the hibernate session. So if you're object is modified and you ask for a count before persisting it you won't get the right answer.

at least, that was my understanding of how it worked....
#15 by Dan Vega on 9/7/10 - 10:24 AM

Chris - You are correct but I think in the case of a calculated value you are almost always working with persisted data.

comment count
category count
posts by category count
first order date
last order date
days since last order
#16 by Raymond Camden on 9/7/10 - 10:26 AM

Makes perfect sense Chris - and it's a good point.
#17 by Mike Bosch - Software Engineer on 1/18/11 - 3:29 AM

Good point by Chris.