Mapping And Using Databases

In the previous example we created a feed by using items supplied with query data entered by ourselves. In this chapter we will replace that code and instead create the feed items using data supplied from a live database. To do this we create a structure known as a map. In this map, the structure key names are equal to the item elements, while the structure values hold the database column names which contain the data. Once supplied, ColdBox automatically wires the database columns and the item elements together.

In this mapping example we assume you have already created the Where To Start example. We will revise that earlier example to use mapped, live data. First replace the file feeditems.cfc in the model directory with the code below. Please note this code is designed to use the cfartgallery example database which is included in the default install of ColdFusion. Users of other CFML engines will probably need to use their own database and would need to modify the query code accordingly.

<cfcomponent name="feeditems" hint="A feed items data object">

  <cfset variables.instance = structNew()/>
  <cfset variables.instance.dns = "cfartgallery"/>

  <cffunction name="init" access="public" returntype="feeditems" output="false">
    <cfreturn this>
  </cffunction>

  <---  Fetch data from a database --->
  <cffunction name="generateItems" access="public" returntype="query" output="false">
    <cfset var returnQuery = ""/>
    <cftry>
      <---  Query the cfartgallery database and save results to a variable --->
      <cfquery name="returnQuery" datasource="#variables.instance.dns#">
        SELECT address,city,state,postalcode,customerfirstname,customerlastname,orderdate,orderid,orderstatusid,total,tax
        FROM orders
        ORDER BY orderdate DESC
      </cfquery>
      <---  If the database query fails we create an empty query --->
      <cfcatch type="Database">
        <cfset returnQuery = QueryNew("orderid","VarChar")/>
      </cfcatch>
    </cftry>
    <cfreturn returnQuery/>
  </cffunction>

  <---  Map database column names to feed item elements --->
  <cffunction name="mapItems" access="public" returntype="struct" output="false">
    <cfset var returnMap = StructNew()/>
    <cfset returnMap.category_tag = "state"/>
    <cfset returnMap.description = "address"/>
    <cfset returnMap.guid_string = "orderid"/>
    <cfset returnMap.title = "customerlastname"/>
    <cfset returnMap.pubDate= "orderdate"/>
    <cfreturn returnMap/>
  </cffunction>

</cfcomponent>

Now that our method is complete we need to update our generate event in the feed.cfc file, located in the handlers directory to request and supply a map. Here the only real change is the rc.compileFeed variable where we now add the ColumnMap argument which points to the newly created mapItems() method.

Now you can generate your revised feed by running the feed.generate event. This should create a ColdBox.feedGenerator.InvalidFeedStructure, which is the technical name for a feed validation error.

To correct this problem we need to update our feeditems model to incorporate the guid_permalink element and have it contain the value false for each item.

We again update the feeditems model, first updating generateItems() with a new array containing a value of false. We merge this array into the returnQuery query for use with our feed.

  <---  Fetch data from a database --->
  <cffunction name="generateItems" access="public" returntype="query" output="false">
    <cfset var returnQuery = ""/>
    <cfset var permalinkArray = arrayNew(1)/>
    <cfset var i = 1/>
    <cftry>
      <---  Query the cfartgallery database and save results to a variable --->
      <cfquery name="returnQuery" datasource="#variables.instance.dns#">
        SELECT address,city,state,postalcode,customerfirstname,customerlastname,orderdate,orderid,orderstatusid,total,tax
        FROM orders
        ORDER BY orderdate DESC
      </cfquery>
      <---  If the database query fails we create an empty query --->
      <cfcatch type="Database">
        <cfset returnQuery = QueryNew("orderid","VarChar")/>
      </cfcatch>
    </cftry>
    <---  Create a new permalink cell, one for each query record --->
    <cfloop from="1" to="#returnQuery.RecordCount#" index="i">
        <cfset permalinkArray[i] = "false"/>
    </cfloop>
    <---  Merge the permalink cells into the query --->
    <cfset QueryAddColumn(returnQuery, 'ispermalink', permalinkArray)/>
    <---  return the updated query --->
    <cfreturn returnQuery/>
  </cffunction>

But before we can do that we must update mapItems where we add a new map pointing guid_permalink to the newly created query column ispermalink.

  <---  Map database column names to feed item elements --->
  <cffunction name="mapItems" access="public" returntype="struct" output="false">
    <cfset var returnMap = StructNew()/>
    <cfset returnMap.category_tag = "state"/>
    <cfset returnMap.description = "address"/>
    <cfset returnMap.guid_string = "orderid"/>
    <cfset returnMap.title = "customerlastname"/>
    <cfset returnMap.pubDate= "orderdate"/>
    <cfset returnMap.guid_permalink= "ispermalink"/>
    <cfreturn returnMap/>
  </cffunction>

Now our feed should be complete, trouble free and ready to be regenerated. After running the event feed.generate, run feed.rss to display the feed in your reader or browser. Hopefully the new feed should contain around 23 items.