CF9 computed properties

I wanted to make a computed property in CF9 just like you can on a sql table (computed column). Dan Vega had a nice how-to awhile back but it didn't show a complex example.

In my project I had a "project" class that had many ratings on it (Example: rating of 1-5, 1 being bad and 5 being great) and I wanted to have a computed property that gave me the average rating of a project. Here is what my hib file looks like for the "project" class.

<class entity-name="Project" lazy="true" name="cfc:myproject.app.model.Project" table="`Project`">
        <id name="id" type="int">
                <column length="10" name="ID" />
         <generator class="identity" />
        </id>
        <property name="name" type="string">
                <column name="Name" sql-type="varchar(max)" />
        </property>
  <property name="description" type="string">
        <column name="Description" sql-type="varchar(max)" />
        </property>
        <property type="float" 
                formula="(select (sum(r.rank)*100.0) / (count(pr.id) *100.0) from project as p inner join project_rating as pr on pr.project_id = p.id inner join rating as r on r.id = pr.rating_id where pr.project_id = id)" 
                name="rating"/>  
</class>

Some key things to point out:
1. The formula is a sql query not a hql query.
2. I wrapped my entire statement in (), don't know why but it worked.
3. You always start the sql statement with the table of the class you are working on. Reason being is when you get to the "where" clause notice this piece "pr.project_id = id". "id" is not aliased because it grabs the current id of the object that is running the formula against. This is how the query will only run for single object and not all of the other db records in the project table.
4. Alias every thing except the "where id" part.

I struggled through these above. Hope this helps someone.

CF9 hibernate file includes.

This was so cool I had to repost it. After you've defined a few hibernate.hbmxml files it gets annoying to have type the same properties on every class. My fingers were getting sore to complain a little. After a reference shown to me by Tony Nelson, Mark Mandal had a great post on "includes in the hibernate file". Check it out.

If you notice in the hibernate.hbmxml below there is a line that says "&common;".

hibernate.hbmxml

<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
                                   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"
[ <!ENTITY common SYSTEM "common.hbm.xml"> ]
>
<hibernate-mapping>
 
 <class entity-name="User" lazy="true" name="cfc:beforeandafter.app.model.User" table="`User`">
        &common;
  <property name="firstName" type="string">
       <column name="First_Name" length="200" />
  </property>
  <property name="lastName" type="string">
       <column name="Last_Name" length="200" />
  </property>
  <property name="emailAddress" type="string">
       <column name="Email_Address" length="200" />
  </property>
  <property name="password" type="string">
       <column name="Password" length="200" />
  </property>
  <bag name="projects">
   <key column="User_ID" />
   <one-to-many class="cfc:beforeandafter.app.model.Project" />
  </bag>
  <bag name="projectRatings">
   <key column="User_ID" />
   <one-to-many class="cfc:beforeandafter.app.model.ProjectRating" />
  </bag>
 </class>
</hibernate-mapping>

&common; points to file called common.hbm.xml. Within common.hbm.xml file I defined all my similiar properties and just include it on all my classes.

common.hbm.xml

<id name="id" type="int">
 <column length="10" name="ID" />
 <generator class="identity" />
</id>
<property name="isDeleted" type="boolean">
 <column name="isDeleted" />
</property>
<property name="createdBy" type="int">
 <column length="10" name="Created_By" />
</property>
<property name="createdOn" type="timestamp">
  <column name="Created_On" />
</property>
<property name="updatedBy" type="int">
  <column length="10" name="Modified_By" />
</property>
<property name="updatedOn" type="timestamp">
  <column name="Modified_On" />
</property>

Make sure your include file ends in .xml and not .hbmxml otherwise it won't work. Made my life a ton a easier. Thanks Mark and Tony for sharing this.

Adding a node to an existing xml object

So I struggled with an interesting task the other day, that should have been easy. I had an xml object with which I was to add additional node too. Sounds simple right. The part I struggled with was the ColdFusion function "xmlElemNew()". I kept wanting to put in that third optional argument. Enough blabbing, here's how I did it.

Here is my xml file or xml object.

<boarders>
 <boarder>
  <name>Joe</name>
  <board>Forum</board>
  <gender>M</gender>
 </boarder> 
</boarders>

I want to add another boarder.

Here I am reading from a file but the same thing works for an xml object.

<cfset xml = xmlParse("boarders.xml")/>

I create my new boarder node, using the variable xml as my "xml document".

<cfset boarder = XmlElemNew(xml,"boarder")/>

Next I create some extra nodes "name,gender, and board" and append it to the boarder node using arrayAppend(). Each time I make the "extra nodes" I set the value of the node by using XmlText, which is an attribute of xml object in ColdFusion. Finally we add the node the children of the boarder node created earlier.

<!---name--->
<cfset name = XmlElemNew(xml,"name")/>
<cfset name.XmlText = "Jamie"/>
<cfset arrayAppend(boarder.XmlChildren,name)/>

<!---gender--->
<cfset gender = xmlElemNew(xml,"gender")/>
<cfset gender.XmlText = "F"/>
<cfset arrayAppend(boarder.XmlChildren,gender)/>

<!---board--->
<cfset board = xmlElemNew(xml,"board")/>
<cfset board.XmlText = "Nitro"/> 
<cfset arrayAppend(boarder.XmlChildren,board)/>

Once we are ready to add the node the xml object we just arrayAppend() just like we did with the above nodes "name,gender,and board".

<cfset arrayAppend(xml.boarders.XmlChildren,boarder)>

<cfdump var="#xml#">

<cfabort/>

That's it.

To recap quick:
1. Create the node using XmlElemNew().
2. Set the node value using XmlText().(optional)
3. Append the node to the parent node using arrayAppend().

Took me forever to figure it out. Sad I know.