Working with entity-object relationships

Now we will look at relational connections via our ORM-mapped objects. We need to do this to create a new record in the ART table. The art record has two relationships. It has an artist and a media type. We will find that working with relationships can also be simpler than writing out query relationships. It also has the advantage of being portable from one platform to the next. The field detail added in previously was done to make the ORM relations easier to follow as we read the code. It was just a little extra detail to give more meaning to the relational properties. Add the following code just above the closing<cfcomponent> tag. We will create the two ORM relations and then go back and create the related ORM object CFCs:

<!--- Relations --->
<cfproperty name="ARTISTS" singularname="ARTIST" fieldtype="many-to-one" cfc="ARTIST" fkcolumn="ARTISTID"/>
<cfproperty name="MEDIA" singularname="MEDIA" fieldtype="many-to-one" cfc="MEDIA" fkcolumn="MEDIAID"/>

We can see that fieldtype is different here. We also see the type many-to-one. The first item "many" refers to this ORM object. The second item "one" refers to the related object. So the relation states that there can be many objects related to one of the objects listed in the CFC attribute. We also see that there is a foreign key column attribute, fkcolumn, containing the field holding the relational value. We could get an error running the code, so for best practice we will tell the entity not to directly update the artist or media ID fields. The following is the correct way to do this via ORM:

<cfproperty name="ARTISTID" ormtype="int" insert="false" update="false" />
<cfproperty name="MEDIAID" ormtype="int" insert="false" update="false" />

We can see both of these properties with insert and update attributes set to false. These are the fields listed in the fkcolumn attribute of both the relations. Now we have a richer entity ready to do more for us. We will need to create the two ORM objects before testing it out. We will create an ARTISTS and MEDIA entity object.

Here is the code for the ARTISTS table:

<cfcomponent persistent="true" table="ARTISTS">
<!---- properties ---->
<cfproperty name="ARTISTID" ormtype="int" fieldtype="id" />
<cfproperty name="FIRSTNAME" ormtype="string" />
<cfproperty name="LASTNAME" ormtype="string" />
<cfproperty name="ADDRESS" ormtype="string" />
<cfproperty name="CITY" ormtype="string" />
<cfproperty name="STATE" ormtype="string" />
<cfproperty name="POSTALCODE" ormtype="string" />
<cfproperty name="EMAIL" ormtype="string" />
<cfproperty name="PHONE" ormtype="string" />
<cfproperty name="FAX" ormtype="string" />
<cfproperty name="THEPASSWORD" ormtype="string" />
</cfcomponent>

Here is the code for the MEDIA table:

<cfcomponent persistent="true" table="MEDIA">
<!---- properties ---->
<cfproperty name="MEDIAID" ormtype="int" fieldtype="id" />
<cfproperty name="MEDIATYPE" ormtype="string" />
</cfcomponent>

Now if we put a line at the bottom of our last page and dump the entity named piece, the following record would appear on the screen. We notice that in the dump ColdFusion shows us the values directly. We should use the setter and getter methods to interact with these values in our code. If you expand the methods, you will find a setter and getter for each field in the entity. In our updated object, we will also see a couple of new methods such as hasArtists(), hasMedia(), getArtists(), setArtists(), getMedia(), and setMedia(). The has function of course returns a Boolean result. The new relational getters and setters return an array of entities in the same return type as the results of our entityLoad() method. Obviously, the return collection is narrowed, but the results come back in the same type as an array of entities.

Now we have achieved looking up a record for an art item, and the related artist and media type. We need to understand how to work with this entity object through code. Let's reverse the relationship in the media class before we write the entity code. Here is the correct way to relate the media back to the art works. Add the following code to the end of the MEDIA.cfc object class. We will do one thing different than before. We will set the field type as one-to-many because the relation based on this entity is the reverse of the previous examples. We always name this using the reference of the current object.

<cfproperty name="works" cfc="ART" fkcolumn="MEDIAID" fieldtype="one-to-many" >

If we immediately ran the previous piece of code, we would get too much information. There is a simple way to prevent getting too much information in ColdFusion. We add one attribute to our dump tags. In this case, two levels of information and/or rows of array data are sufficient. So just write top="2" inside the CFDump tag and we would get the following result for the media section of the data dump:

There are actually more than two rows but this attribute helps keep the amount of information under control. If this were large enterprise data, the reasons for doing this would become obvious. So we should remember to use the top attribute which of course could be set to 10, 20, or any number you prefer. What is neat is how it links back to the art-related records. This means the entity objects are very smart with little coding. Just a few lines of mapping description tags, and our data tools become very powerful.

So how do we create the code needed to output this related information? We will not only output the related information, but we will make sure to pull a single record returned without the array of entities in the same example:

<cfscript>
art = entityLoad("art",3,true);
artist = art.getArtist();
media = art.getMedia();
</cfscript>
<cfdump var="#media#" top="2">

This code block will give us the same basic results as the nested media entity previously shown, except of course it will not be nested. We see that just like the methods added for the attributes, we now have methods added for the entity items. So in the place where we would code getArtID() to return the artID field from the art table record, we are now going to code getMedia() to return the entity item. Looking at the code you will notice that we show how to return both the artist and the media. You should also note that we coded our entity load in a different manner this time. We passed in the table, primary ID key, and the value of true. This is used when we want it to return the actual single entity without the array structure. We will only show the dump for the media in this example. Also remember there are more than two records for art works. We limited that with the top attribute in CFDump. The last thing we should notice is the works rather than the ART property/entity type. This is because we gave the relationship in the MEDIA.cfc an attribute name of works. This shows we have naming flexibility that can make our code more abstract as desired.

Many-to-many relationships

The next thing we will look at is setting up many-to-many type relations. These relationships are where one table is connected to another table by a link table. The link table contains a link field for both the source and the target tables. We will set the link from the orders table to the items table. This will create the ability to look at an order and see the items that are on an order using ORM to make things easier to code. Traditionally we would write a query in the CFQuery tag that did a double-level join. Here we will use a property tag with the fieldtype attribute set to many-to-many:

<cfproperty name="item" fieldtype="many-to-many" cfc="ART" fkcolumn="ORDERID" linkTable="ORDERITEMS" inverseJoinColumn="ARTID">

The code shown should be added to the ORDERS.cfc table. When setting the details for the mapping, it is always done from the point of view of the current entity class. We can use an alias just as we did previously in this chapter. Here we set the alias name to item to represent an order item. The fkcolumn is the column that exists in the current table and the link table. The inverseJoinColumn is the field that exists both in the linkTable and the target entity table. That is enough to get the connections working. ORM is very powerful and adaptable, but we will go with this simplified ORM class for learning because we just need to see the basics of how it works:

<cfscript>
order = entityLoad("orders",2,true);
</cfscript>
<cfdump var="#order#" top="2">

This is the dump that our code outputs:

Here is the property we would place in ART.cfc to build reverse functionality. There are many other attributes that can be added. We can control the order, cascading relationship functionality. A collection can be either a structure or an array. As we can see in the screenshot of the dump, entities use array-based collections by default:

<cfproperty name="order" fieldtype="many-to-many" cfc="ORDERS" fkcolumn="ARTID" linkTable="ORDERITEMS" inverseJoinColumn="ORDERID">

Working with relational data

Now we should look at using this relational data with forms and learn how to store information. If this mapping technology seems slightly difficult on the front side, then hang on just one moment and you will be surprised to see how much simpler the ORM makes the logic and view parts of your application. The ORM concepts are where your "model" is built. Model is a programming term for the things that you interact with in a program. The technology in this chapter is what we would consider a persistent model. The information is persisted via a database in this use case. We will create the beginnings of a cart page, so we can add an item to the cart. Create an add item page first. Note that this is just for the code example. The way you build a cart should depend on your needs:

<cfscript>
if(structKeyExists(session,"cartID")){ cart = entityLoadbyPK("ORDERS",session.cartID);
} else {
cart = entityNew("ORDERS");
cart.setOrderStatus(entityLoad("ORDERSTATUS", {STATUS="pending"},true));
cart.setTax(0);
cart.setTotal(0);
cart.setCustomerFirstName("CF9DT");
cart.setCustomerLastName("Student");
cart.setCustomerLastName("123 Anywhere St");
cart.setCity("no");
cart.setState("ST");
cart.setPostalCode("0000");
entitySave(cart);
session.cartID = cart.getORDERID();
}
if(structKeyExists(url,"remove")){ cart.removeItem(entityLoad("ART",url.remove,true));
}
if(structKeyExists(url,"buy")){ art = entityLoadByPK("ART",URL.buy);
if(!cart.hasItem(art)){
cart.addItem(art);
}
}
cart.setOrderDate(now());
entitySave(cart);
</cfscript>
<cfinclude template="navigation.cfm"/>
<cfoutput>
<h1>Cart</h1>
<table border="1">
<tr>
<td>Item</td>
<td>Price</td>
<td>Remove</td>
</tr><cfif isArray(cart.getItem()) AND arrayLen(cart.getItem())> <cfloop array="#cart.getItem()#" index="i">
<tr>
<td>#i.getArtName()#</td>
<td>#dollarFormat(i.getPrice())#</td>
<td><a href="cart.cfm?remove=#i.getArtID()#">Remove</a></td>
</tr></cfloop><cfelse>
<tr>
<td colspan="4">No Items In Cart</td>
</tr></cfif>
</table>
</cfoutput>

Okay, let us walk through that code. One of the first and most important things to note at this time is that we do not persist an entity object in a persistent ColdFusion scope—application or session scope, basically. We are going to follow the rule of checking our session for the ID of our cart stored in the session scope. This will allow us to do the same thing without nasty surprises of data abnormalities.

We would also be using forms and more in a full application; however, here we provided just enough detail to show how we would create and manage a shopping cart. This is to illustrate a real-life example of how data could be maintained in tables where there is a many-to-many relationship. We also have logic that allows us to both add and remove items from the cart. For each call to the server in the shopping cart, we update the date and save the cart.

The last thing for our page is to generate the view. We include a file for navigating through the page. The code for this file is as follows:

<div>
<span><a href="art_items.cfm">Items for Sale</a></span>
&nbsp;
<span><a href="cart.cfm">Cart</a></span>
</div>

We then create a table for our tabular cart data. Again we could dress up a real-world application in a much nicer way, but we want to focus on code here. There are times when the entity will return null, which ColdFusion sees as an empty string. This is why we wrapped our loop logic with a conditional cfif tag set. We also included an arrayLen() check in the previous Cart display code. If the array is empty or non-existing, it allows us to place alternate information on the screen telling the user that there are no items in the cart at that point.

We also need an art_items.cfm page to show what items are still available for sale at this time:

<cfscript>
art = entityLoad("art",{ISSOLD=false},"ARTNAME");
</cfscript>
<cfinclude template="navigation.cfm"/>
<cfoutput>
<ul><cfloop array="#art#" index="i"> <li> <a href="item.cfm?item=#i.getARTID()#">Preview</a> #i.getARTNAME()# @ #dollarFormat(i.getPRICE())#</li></cfloop>
</ul>
</cfoutput>

Here we see a special way of using our entityLoad() method. The second argument is a filter. If we don't name the field represented by the filter, it assumes the primary key field. Here we told it to only return items that have not been sold. Next, we see that the art array stores its collection item in the index variable for each iterative loop. We can then use that iterative loop to pull data as we would with the alias being used for the individual art item in this case. Here is the output:

Next we need to create our item.cfm page. We are using the entityLoadByPK() method this time. It works the same as if we used entityLoad("ART",URL.item,true). You can use either one as you choose. We include the navigation, as in our cart page. Then we put out our information to the screen. We also include a conditional link of course, to deal with someone who may have bookmarked the page on an item that may have been sold before they return:

<cfscript>
art = entityLoadByPK("ART",URL.item);
</cfscript>
<cfinclude template="navigation.cfm"/>
<cfoutput>
<h1>Item Preview Page</h1>
<h2>#art.getArtName()#</h2>
<div>
by #art.getArtist().getFirstName()# #art.getArtist().getLastName()#
</div>
<div>
Media: #art.getMedia().getMediaType()#
</div>
<div>
Price: #dollarFormat(art.getPrice())#
</div>
<div>
<cfif art.getIsSold()><strong>Already Sold</strong>
<cfelse> <a href="cart.cfm?buy=#art.getArtID()#">Buy #art.getArtName()# </a>
</cfif>
</div>
</cfoutput>

Here is what the screen would look like if we choose The Lake as our selection:

This shows how amazingly simple code can be, using the new ColdFusion 9 ORM. Now if we click on Buy The Lake, we will get the following view:

Our sample is now complete. If we return to Items for Sale, the items will be shown as before. If we try to buy the lake again before it is marked, purchasing it will not add it in the cart twice. Of course there is more logic that would be needed in a real-world application because we certainly would not want two people trying to buy a piece of art that sells for $150,000 at the same time. That would be a huge public relations mess. If we add other pieces, they will be shown on the screen. If we click on the Remove link by any of the pieces, they will be properly removed from the cart and from the database. Welcome to the beginning of a very simple shopping experience.