Experimenting with Multi-related Selects using Spry

Posted at February 22, 2007

I recently found myself in need of a multi-related select solution and decided to give Adobe’s new Spry framework a shot.

The first thing I noticed was that, still being in development, there isn’t much Spry documentation. In lieu of docs Adobe provides several samples which, though useful, did not provide the solution I was looking for. My goal is to have two select boxes, each populated by separate but related datasets. In the example I use below, my datasets are brands and subbrands. The subbrands dataset contains a brandID column which is related to the brandID column in the brands dataset. Read on to see how I did it and feel free to leave a comment telling me how I should have done it ;) .

First, I set up my two datasets in a component named datasets.cfc.:

<cfcomponent>
   <cffunction name="getBrands" output="no" access="public" returntype="query">
		<cfset tbl = queryNew("brandID,brand")>
		<cfset queryAddRow(tbl)>
		<cfset querySetCell(tbl, "brandID", 1)>
		<cfset querySetCell(tbl, "brand", "GI Joe")>
		<cfset queryAddRow(tbl)>
		<cfset querySetCell(tbl, "brandID", 2)>
		<cfset querySetCell(tbl, "brand", "Star Wars")>
		<cfset queryAddRow(tbl)>
		<cfset querySetCell(tbl, "brandID", 3)>
		<cfset querySetCell(tbl, "brand", "Transformers")>

      <cfreturn tbl />

   </cffunction>

     <cffunction name="getSubbrands" output="no" access="public" returntype="query">
	 	<cfargument name="brandID" type="numeric" required="yes">
		<cfset tbl = queryNew("brandID,subbrand")>
		<cfset queryAddRow(tbl)>
		<cfset querySetCell(tbl, "brandID", 1)>
		<cfset querySetCell(tbl, "subbrand", "Army Rangers")>
		<cfset queryAddRow(tbl)>
		<cfset querySetCell(tbl, "brandID", 1)>
		<cfset querySetCell(tbl, "subbrand", "Navy Seals")>
		<cfset queryAddRow(tbl)>
		<cfset querySetCell(tbl, "brandID", 1)>
		<cfset querySetCell(tbl, "subbrand", "Sigma 6")>
		<cfset queryAddRow(tbl)>
		<cfset querySetCell(tbl, "brandID", 2)>
		<cfset querySetCell(tbl, "subbrand", "Clone Wars")>
		<cfset queryAddRow(tbl)>
		<cfset querySetCell(tbl, "brandID", 2)>
		<cfset querySetCell(tbl, "subbrand", "Jedi Force")>
		<cfset queryAddRow(tbl)>
		<cfset querySetCell(tbl, "brandID", 2)>
		<cfset querySetCell(tbl, "subbrand", "Original Trilogy")>
		<cfset queryAddRow(tbl)>
		<cfset querySetCell(tbl, "brandID", 3)>
		<cfset querySetCell(tbl, "subbrand", "Beast Wars")>
		<cfset queryAddRow(tbl)>
		<cfset querySetCell(tbl, "brandID", 3)>
		<cfset querySetCell(tbl, "subbrand", "Cybertron")>
		<cfset queryAddRow(tbl)>
		<cfset querySetCell(tbl, "brandID", 3)>
		<cfset querySetCell(tbl, "subbrand", "Decepticon")>

		<cfquery name="ds" datasource="query">
			select *
			from tbl
			where brandID = #arguments.brandID#
		</cfquery>

      <cfreturn ds />

   </cffunction>

</cfcomponent>

Next, I created two ColdFusion pages, brands.cfm and subbrands.cfm, that output XML. These will be used as the sources for my selects. The brands.cfm page will output all brands. The subbrands.cfm will expect a URL variable named brandID and will output only the subbrands with that brand ID.

A small diversion: One reason I used external pages to output my XML was that it allowed me to easily insert a new row at the beginning of the dataset. I wanted an additional option with a value of 0 which gets submitted if no brand is selected, In the select box this value is represented by the text: ‘Please select a brand’.

Here are the pages that output XML:

brands.cfm

<cfsetting enablecfoutputonly="no">
<cfset objDatasets = createObject("component","cfc.datasets") />
<cfset qGetBrands = objDatasets.getBrands() />
<cfsavecontent variable="thisXML">
<cfoutput>
<brands>
	<brand>
		<brandID>0</brandID>
		<brandNAME>Select a brand</brandNAME>
	</brand>
	<cfloop query="qGetBrands">
	<brand>
		<brandID>#xmlFormat(brandID)#</brandID>
		<brandNAME>#xmlFormat(brand)#</brandNAME>
	</brand>
	</cfloop>
</brands>
</cfoutput>
</cfsavecontent>

<cfcontent type="text/xml" reset="true">
<cfoutput>#thisXML#</cfoutput>
<cfabort>

subbrands.cfm

<cfsetting enablecfoutputonly="no">
<cfparam name="url.brandID" default=0>
<cfset objDatasets = createObject("component","datasets") />
<cfset qGetSubbrands = objDatasets.getSubbrands(url.brandID) />

<cfsavecontent variable="thisXML">
<cfoutput>
<subbrands>
	<cfif qGetSubbrands.recordcount eq 0>
		<subbrand>
			<subbrand_id>0</subbrand_id>
			<subbrand_name>No subbrands available</subbrand_name>
		</subbrand>
	<cfelseif qGetSubbrands.recordcount gt 1>
		<subbrand>
			<subbrand_id>0</subbrand_id>
			<subbrand_name>Select a subbrand</subbrand_name>
		</subbrand>
	</cfif>
	<cfloop query="qGetSubbrands">
	<subbrand>
		<subbrand_id>#xmlFormat(subbrand_id)#</subbrand_id>
		<subbrand_name>#xmlFormat(subbrand_name)#</subbrand_name>
	</subbrand>
	</cfloop>
</subbrands>
</cfoutput>

</cfsavecontent>

<cfcontent type="text/xml" reset="true">
<cfoutput>#thisXML#</cfoutput>
<cfabort>

Now the meat of the main page which holds the selects. The first thing I do is load the Spry js files:

<script language="JavaScript" type="text/javascript" xsrc="js/spry/xpath.js"></script>
<script language="JavaScript" type="text/javascript" xsrc="js/spry/SpryData.js"></script>

Next, I define my XML datasets by pointing the js variables to the appropriate XML-generating pages. Notice that my dsSubbrands dataset passes the current value of brandID to the subbrands page. The second parameter in the method calls (“brands/brand” and “subbrands/subbrand”) refer to the XML node names leading to the records.

<script type="text/javascript">
var dsBrands = new Spry.Data.XMLDataSet("brands.cfm","brands/brand");
var dsSubbrands = new Spry.Data.XMLDataSet("subbrands.cfm?brandID={dsBrands::brandID}","subbrands/subbrand");
</script>

And finally, I place the selects by providing repeatingchildren with my dataset names and using the brace format to provide the column names within the source xml ({brandID}, {brandNAME} and {subbrandNAME}):

<form name="frmSpry" action="#CGI.SCRIPT_NAME#">
<cfoutput>
<table border="0">
	<tr>
		<td>Brand:</td>
		<td>
<SELECT spry:repeatchildren="dsBrands" name="brandID"
onchange="document.forms[0].subbrandselect.disabled = true;
dsBrands.setCurrentRowNumber(this.selectedIndex);">
	<option spry:if="{ds_RowNumber} == {ds_CurrentRowNumber}" value="{brandID}" selected="selected">{brandNAME}</option>
	<option spry:if="{ds_RowNumber} != {ds_CurrentRowNumber}" value="{brandID}">{brandNAME}</option>
</SELECT>

		</td>
	</tr>
	<tr>
		<td>Subbrand:</td>
		<td>
<SELECT spry:repeatchildren="dsSubbrands" name="subbrandselect" >
	<option spry:if="{ds_RowNumber} == {ds_CurrentRowNumber}" value="{subbrandNAME}" selected="selected">{subbrandNAME}</option>
	<option spry:if="{ds_RowNumber} != {ds_CurrentRowNumber}" value="{subbrandNAME}">{subbrandNAME}</option>
</SELECT>
		</td>
	</tr>
</table>
</cfoutput>
</form>

…and Spry takes care of the rest.

As I said before, this is a workable solution but I’m certain there are quicker and more efficient ways to get the same results with Spry without the XML hoops that I jumped through here. You can see a working copy here.

In researching, I attempted using Ray Camden’s toXML component as well as the WDDX functionality provided by ColdFusion but I just kept running problems passing the resulting datasets to the Spry methods.

Let me know if anyone out there has done anything similar using Spry. I’m interested in seeing the different possible solutions because it seems to me there are many.

-rG

XML, ajax, coldFusion, spry

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.

Comments

9 Responses to “Experimenting with Multi-related Selects using Spry”

Leave Comment

(required)

(required)