This CFWheels series is heavy borrowed from Dan Wilson's "So You Want to" series about Model Glue:Unity and matches to this post

Previously in this series, we installed CFWheels, discussed some concepts in CFWheels, added our basic flow and navigation, created add and list functionality, and validation to our Contact-O-Matic Application. Next in the series, we will cover the built in ORM of CFWheels.

An Object Relation Mapper (ORM) is used to translate between our Relational Database and our Object Oriented programming. One nice thing, it is simplifies common sql statements thus saving us time and reducing sql errors. More complex SQL will still need to be coded. Don't think you won't have to learn SQL, the ORM can't do everything better but it will make the mundane tasks simplier.

CFWheel ORM

I glossed over some important convention before in our series. CFWheels expects the table name to be plural like contacts, a primary keys in the tables, and the primary key column to be name id. Our Contacts table meets all these. We can override the table name and primary key column name but CFWheels does need a primary key.

Database Code

Lets drop our old table and create it along with a types table using this code.

DROP TABLE IF EXISTS contactomatic.contacts;CREATE TABLE 'contactomatic'.'contacts' ( 'id' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 'name' VARCHAR(<span class="cc_numeric">45) NOT NULL, 'typeid' INTEGER UNSIGNED NOT NULL, PRIMARY KEY ('id') ) ENGINE = InnoDB;

DROP TABLE IF EXISTS contactomatic.tbl_typesofcontact; CREATE TABLE 'contactomatic'.'tbl_typesofcontact' ( 'typeid' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 'title_for_type_of_contact' VARCHAR(<span class="cc_numeric">45) NOT NULL, PRIMARY KEY ('typeid') ) ENGINE = InnoDB;

INSERT INTO contactomatic.tbl_typesofcontact (title_for_type_of_contact) VALUES('Friend');

INSERT INTO contactomatic.tbl_typesofcontact (title_for_type_of_contact) VALUES('Enemy');

INSERT INTO contactomatic.tbl_typesofcontact (title_for_type_of_contact) VALUES('Co-Worker');

Types Model

In our /controller/contact.cfc add this code to the new action.

<cfset types = model("type").findAll() />

While we are in this file, replace the code in the list action with this:

<cfset allContacts = model("contact").findAll(include="type") /><cfdump var="#allContacts#"><cfabort>

And now lets check our add form.

URL Rewriting On = http://localhost/contact/new?reload=true

URL Rewriting Partial = http://localhost/index.cfm/contact/new?reload=true

URL Rewriting Off = http://localhost/index.cfm?controller=contact&action=new

Table Naming

Well, you can see CFWheels assumes we have a types table since our newly created model cfc's name is type. Our table's name is actually tbl_typesofcontact, we could rename the cfc to tbl_typesofcontact.cfc, but CFWheels convention also says our table name should be plural. Lets tell (configure) CFWheels we already have a table name that doesn't fit in CFWheel's conventions.

Add this code to our type.cfc in the models folder.

<cffunction name="init"><cfset table("tbl_typesofcontact") /><cfset property(name="title", column="title_for_type_of_contact") /></cffunction>

ÿ

As you can see, we tell CFWheels our table's actual name. Also we give the long column name, title_for_type_of_contact, a shorter name, title. See how easy it is to override CFWheel's table conventions. It would have been easier if initially our table was named contacttypes and the model cfc contacttype.cfc but sometimes this won't be an option.

Reload our page and you should now see our old form but look @ our debug information

Notice we have a new query. This was created by the code, <cfset types = model("type").findAll() />, we added to our new action in /controllers/contact.cfc.

Select Helper

Lets add our select box populated from this query to our form using a CFWheels helper.

Replace in /view/new.cfm

<span class="cc_normaltag"><div>#textField(objectName="newContact", property="type", label="Type")#<span class="cc_normaltag"></div>

With

<span class="cc_normaltag"><div>#select(objectName="newContact", property="typeid", options=types, label="Type", includeBlank="")#<span class="cc_normaltag"></div>

The select helper takes our blank new object, the typeid column (property), the types object, and we add a label of Type. For more information on Helper Forms, look through the code in \wheels\view\forms.cfm.

Load the page again, and now you should see the select dop down box. Lets submit the form.

Weird, we selected a valid contact type from our new select drop down box, but our validation is catching it. This is because the value the form is submitting is actually the typeid not the title.

Well, lets change our validation and add an association to mark typeid as a foreign key.

In our model/contact.cfc change the code to match this.

<cfcomponent extends="Model" output="false"><cffunction name="init"><cfset validatesPresenceOf(property="name",message="Name is Required") /><cfset validatesPresenceOf(property="typeid",message="Type of Contact is Required") /><cfset validatesUniquenessOf(property="name", message="Name is already present") /><cfset belongsTo(name="type", foreignKey="typeid")></cffunction></cfcomponent>

We basically made sure the typeid is present instead of type and declared a datebase relationship (association). Following CFWheels convention, we could use <cfset belongTo("type")> if we followed our CFWheels naming convention for the primary key of a table but instead of id, we have typeid so we need to tell CFWheels.

Lets try to submit the form again.

We are making progress, looks like the form submitted and executed our cfdump and cfabort in /controllers/contact.cfc

Association

Here is where our association we set in /models/contact.cfc comes to play. CFWheels ORM joins the tbl_typesofcontact table with the contacts table. This happens because we added the association declaring the foriegn key in our /models/contacts.cfc and in creating the query, we told it to include type. <cfset allContacts = model("contact").findAll(include="type") />

Remove <cfdump var="#allContacts#"><cfabort> from /controllers/contact.cfc in the list action and reload our list.

URL Rewriting On = http://localhost/contact/list?reload=true

URL Rewriting Partial = http://localhost/index.cfm/contact/list?reload=true

URL Rewriting Off = http://localhost/index.cfm?controller=contact&action=list&reload=true

More on Associations

We did alot of work with the built in ORM. You did a good job. Next post, we will cover the return differences between some built in ORM calls and the logic why, how to specify the return columns, specify the where clause, and create advance queries. Hopefully after that, we will cover editing and deleting records.

So you want to create a CFWheels application? (Part 5)