Rolling my own SQL solution

Recently I was working on a medical website and was tasked with building a doctor directory page that had to pull data from three related tables.  For the viewers at home, here is a simplified view of my tables.

Doctors, Practices, and Locations oh my

The first version of the page was easy to whip up because we were only showing primary location address which lives in the practices table.  ( NOTE : The diagram above was dumbed down to support this post ).  When the request came in to show all locations for each doctor’s practice, that’s when I had to put on my wizard hat and get tricky.

After some thinking I came up with two options for adding all locations for each doctor row :

1. Build a second locations query for each doctor in the first query, then use code to merge the data together
2. Update the first query to include all necessary data, then use code to merge the data into a loopable structure

Being a SQL junky, I decided to take the second route so I’m still only hitting the database once.  In the first version of this page, we always had one row per doctor and didn’t have to do any post processing of the data prior to sending to the page.  However, when the locations table gets added to our sql query, all of the doctor data gets duplicated for each location they are associated with, making the dataset look like this.

When joining the third table, the first two get duplicated

Now that we’ve got all the data, you’ll notice that the doctor information gets duplicated.  This is where I built a cffunction to convert this query into a structure of individual doctors, each having an array of locations.  I used the doctor’s full name and id as the structure keys.  Great, now I can just loop through my new structure of doctors, then loop through each doctor.location array to draw the updated html table.  That is the goal here, but we’re not ready just yet.

If you’ve ever tried to loop over a Structure in Coldfusion, you’ve probably noticed controlling the order of your data coming back can be tricky.  Here’s an example of what I’m talking about when looping through a structure.

Looping through a coldfusion structure can be a challenge

To get around this random order of looping through a structure, I did two steps prior to building the updated html table.

	// UPDATE : extract key list ( DR full names ) so
	// you can sort the physicians my last name
	dr_list = StructKeyList( providers_struct );
	dr_list = ListSort( dr_list, "textnocase" );

This gives me an alphabetized list of all doctors inside my structure, which I convert to an array then loop through.  I know have a sorted loop that can pick all necessary doctor data out of my structure on command.  This solution took a little bit of time to figure, but wasn’t too bad.  After thinking about all the work needed to make this update, I wondered if using Coldfusion9’s Hibernate ORM would have made this update any easier.

Porting SQL to ORM

The first step in setting up  Coldfusion9’s ORM for this code is to enable the ORM in the application.

At the top of my Application.cfc I added the following lines.

	<cfscript>
		// enable ORM
		this.ormenabled = true;

		// my datasource name, setup in the CFAdmin
		this.datasource = "mysql-test";

		// the folder in my app where the cfc mapping files are location
		this.ormsettings.cfclocation = "mappings";

		// this wasn't setup automatically for me
		this.ormsettings.dialect = "MySQL";
	</cfscript>

If you haven’t setup ORM in your Coldfusion9 application before, be sure to bookmark this livedoc page on configuring ORM.  It will give you all the lowdown on all the fancy ORM bells and whistles.

After ORM is enabled, it was time to create my Hibernate mapping files for each of the database tables involved in this code.  For convenience sake, I went the CFC route, and only mapped the columns required for this example.  There are a number of ways to define ORM mappings, refer to this livedocs page to find out more. Here are my three hibernate mapping cfcs.

doctor.cfc

<cfcomponent persistent="true" entityname="Doctor" table="providers">
    <cfproperty name="id" column="provider_id" generator="increment">

    <!--- one to many relationship Doctor.practice = Practice --->
    <cfproperty name="practice" fieldtype="one-to-one" cfc="PRACTICE" fkcolumn="practice_id">

    <cfproperty name="PracticeId" column="practice_id" insert="false" update="false">

    <cfproperty name="FirstName" column="first_name">
    <cfproperty name="LastName" column="last_name">
    <cfproperty name="status" column="status">
    <cfproperty name="is_midlevel" column="is_midlevel">
</cfcomponent>

practice.cfc

<cfcomponent persistent="true" entityname="Practice" table="practices">
	<!--- tie this column back to Doctor.practice_id --->
	<cfproperty name="id" column="practice_id" fieldtype="id" generator="foreign" params="{property='Doctor'}">

	<!--- Relate locations to this practice via Locations.practice_id and Locations.active = 1 --->
        <cfproperty name="locations" type="array" fieldtype="one-to-many" cfc="Location" fkcolumn="practice_id" params="{property='Location'}" where="active = 1">

    <cfproperty name="name" column="practice_name">
    <cfproperty name="active" column="active">
</cfcomponent>

location.cfc

<cfcomponent persistent="true" entityname="Location" table="locations">
	<cfproperty name="id" column="location_id" fieldtype="id">

	<cfproperty name="practice_id" column="practice_id">

    <cfproperty name="address1" column="address1">
    <cfproperty name="address2" column="address2">
    <cfproperty name="city" column="city">
    <cfproperty name="state" column="state">
    <cfproperty name="zipcode" column="zipcode">
    <cfproperty name="phone" column="phone">
    <cfproperty name="active" column="active">
</cfcomponent>

At this point we have three ORM entities that are mapped to the three database tables. If you look closely, you’ll notice that relationships have also been defined for doctors to practice, and practice to locations.  Setting up the relationships at the mapping level takes care of physically joining the three tables together via sql.  Also, while it’s pretty straight forward reading a blog with working code, I’m not going to pretend I got these relationships setup first try.  It’s simple relating two tables together, but finding the right combination for doctror > practice > locations was tricky.  I did a lot of this to figure this part out.

<cfscript>
// load doctors
dr_list = ORMExecuteQuery( "FROM Doctor WHERE status = 'Active' AND provider_id = 1" );
// load a practice
// practice = ORMExecuteQuery( "FROM Practice WHERE practice_id = 1" );
// load a location
// location = ORMExecuteQuery( "FROM Location" );
</cfscript>

<cfdump var="#dr_list#" />
<cfabort />

You can find out more about defining ORM relationships on this livedocs page.

With the hard stuff out of the way, we’re down to two steps.  First it’s time to port my cfquery that grabs the data, and the cffunction that converts the query into our loopable doctor structure.  Are you ready for this?

dr_list = ORMExecuteQuery( "from Doctor WHERE status = 'Active' AND is_midlevel = 0 ORDER BY last_name, first_name" );

To give you the full idea of how much code was shrunk down to a single line, here are the two cffunctions used to query and massage the data into a useable structure.

	<!--- Retrieve listing of all active providers in the app  --->
    <cffunction name="getAllActiveProviders" access="public" returntype="struct">

		<cfquery name="providers_qry" DATASOURCE="#request.dsn#" USERNAME="#request.dbuser#" PASSWORD="#request.dbpswd#">
            SELECT	DISTINCT

					p.provider_id,
            		CONCAT( p.first_name, ' ', p.last_name ) AS 'full_name',
                    p.first_name,
                    p.last_name,

    	            p2.practice_name,

					l.address1,
                    l.address2,
                    l.city,
                    l.state,
                    l.zipcode,
                    l.phone

            FROM	providers p, practices p2

            LEFT OUTER JOIN locations l
            ON p2.practice_id = l.practice_id AND l.active = 1

            WHERE	p.status = 'Active'
            AND		p.practice_id = p2.practice_id
            AND		is_midlevel = 0

            ORDER BY	p.last_name, p.first_name
        </cfquery>

		<!--- return a translated structure --->
		<cfreturn providersQryToStruct( providers_qry ) />

    </cffunction>

	<!--- Translates the providers query into a Struct that has a row per doctor, each containing an Array for location(s) --->
	<cffunction name="providersQryToStruct" access="public" returntype="struct">

    	<cfargument name="providers_qry" type="query" required="yes">

		<cfscript>
		this_dr = "";
		last_dr = "";
		// array of provider arrays
		providers_struct = StructNew();

		// loop through qry and flatten into single row with Location = Array
		// The key to this qry is provider_name+provider_id
		for( xx = 1; xx <= providers_qry.RecordCount; xx++ )
		{
			// set key to this row [ LAST_NAME+FIRST_NAME+PROVIDER_ID ]
			this_dr = providers_qry.LAST_NAME[ xx ] & providers_qry.FIRST_NAME[ xx ] & providers_qry.PROVIDER_ID[ xx ];

			// clean up DR name
			this_dr = Replace( this_dr, " ", "" );
			this_dr = Replace( this_dr, ",", "" );
			this_dr = Replace( this_dr, ".", "" );

			// got same doctor?
			if( this_dr != last_dr )
			{
				// append to array
				provider = StructNew();
				// FILL UP PROVIDER
				provider['full_name'] = providers_qry["full_name"][ xx ];
				provider['practice_name'] = providers_qry['practice_name'][ xx ];

				// location array
				provider['location'] = ArrayNew(1);

				// add first location
				loc = StructNew();
				loc['address1']	 = providers_qry['address1'][ xx ];
				loc['address2']	 = providers_qry['address2'][ xx ];
				loc['city']		 = providers_qry['city'][ xx ];
				loc['state']	 = providers_qry['state'][ xx ];
				loc['zipcode']	 = providers_qry['zipcode'][ xx ];
				loc['phone']	 = providers_qry['phone'][ xx ];

				// add location struct to array
				ArrayAppend( provider['location'], loc );

				// Add to master struct
				providers_struct[ this_dr ] = provider;
			}
			else
			{
				// add first location
				loc = StructNew();
				loc['address1']	 = providers_qry['address1'][ xx ];
				loc['address2']	 = providers_qry['address2'][ xx ];
				loc['city']		 = providers_qry['city'][ xx ];
				loc['state']	 = providers_qry['state'][ xx ];
				loc['zipcode']	 = providers_qry['zipcode'][ xx ];
				loc['phone']	 = providers_qry['phone'][ xx ];

				// Append to this DR's location array
				ArrayAppend( providers_struct[ this_dr ]['location'], loc );
			}

			// store this_dr in last_dr for next iteration
			last_dr = this_dr;
		}

		return providers_struct;
        </cfscript>

    </cffunction>

After going through the brain fry of figuring out ORM relationships, replacing over one hundred lines of code with only a single line really made my day. Now it was time for the final step, porting the cfml that draws the html table into using the ORM collection instead.
The porting of the cfml that draws the doctor table was a piece of cake. The updates were so easy it was almost not worth showing, but I’m including just to complete the picture.

The original sql loop code

    <!--- use dr_list as sorted list. use each DR key to pick out data from providers_struct --->
    <cfloop list="#dr_list#" index="xx">
    <tr>

        <td>#providers_struct[ xx ]['full_name']#</td>

        <td>#providers_struct[ xx ]['practice_name']#</td>

        <td>
        <!--- loop through array of location structures --->
        <cfloop from="1" to="#ArrayLen( providers_struct[ xx ]['location'] )#" index="yy">
            <address>
            <cfset loc = #providers_struct[ xx ]['location'][ yy ]#>
            <li>#loc.address1#, #loc.address2#
            #loc.city#, #loc.state# #loc.zipcode#
            <br />
            <b>#loc.phone#</b>
            </li>
            </address>
        </cfloop>
        </td>

    </tr>
    </cfloop>

The new ORM loop code

	<!--- loop through parent query --->
    <cfloop array="#dr_list#" index="xx">
    <tr>

    	<!--- retrieve properties using ORM's getProperty() syntax --->
        <td>#xx.getFirstName()# #xx.getLastName()#</td>

        <td>#xx.getPractice().getName()#</td>

        <td>
		<!--- loop through locations collection Doctor > Practice > Location(s)  --->
        <cfloop array="#xx.getPractice().getLocations()#" index="loc">

            <address>
                <li>#loc.getAddress1()#, #loc.getAddress2()#
                #loc.getCity()#, #loc.getState()# #loc.getZipcode()#
                <br />
                <b>#loc.getPhone()#</b>
                </li>
            </address>

        </cfloop>
        </td>

    </tr>
    </cfloop>

A few things I’d like to point out in the new ORM loop code

1. ORMExecuteQuery returned a data collection that I can loop through without post-processing
2. When retrieving object properties, use the getPropertyName() syntax.  EX : doctor.getFirstName() instead of doctor.FirstName
3. Practice.locations was declared as an Array collection, but you could use Structure as well.
4. To get the ORMExecuteQuery record count, use ArrayLen.

Did ORM make this easier?

I’d say yes it did.  Mapping my tables via code, including the relationships, really saved time writing SQL.  Funny thing, while I was porting this over, I actually found a few data and code bugs in the home grown solution that I didn’t catch before.  Once the dust had settled I still had one difference in the home grown versus ORM code, and that turned out to be duplicated location rows.

The other thing that I noticed thanks to our good friend cfdump, is all the extra functionality that comes along with the objects returned from ORM.  On top of having a populated data object, you also have magical getters, setters, and other useful methods to work with your data objects.  To see what I mean, fire an ORMExecuteQuery and run your variable through CFDUMP.

To sum up, I’m really starting to buy into using ORMs.  Since I know and love SQL, I’ve been anti ORM for too long.  Having said that, I’m not fully advocating going ORM for everything.  Pretty much every project I work on these days is powered by some datasource, but I don’t really need all the ORM baggage for simple sites.  Now for large applications, or any site large enough to need at least two people, I’d give ORM a chance.  I still have lots of ins and outs to learn about using Hibernate, but it is definitely simple enough to get jamming right away.