Using XMLListCollection In Flex To Filter Coldfusion Queries

Another thing I have played with recently is binding Coldfusion qresultset to DataGrids. In my case, I wanted to filter the data based on input into a TextInput and also the selectedItem of a ComboBox. My natural inclination when working with query data is to want to use sql, however, that seemed like it wasn't going to fly easily in Flex. While I am sure that someone has at least considered creating a SQL parsing engine for Flash, that idea quickly fled and was replaced by a desire to use XML.

So the first thing I did was to create a function to convert my ArrayCollection. My standard caveat is that there is probably a better way to do this, but absent of having that way handily available, here's what I did:

[Bindable] private var _productsxml:XML;   

private function convertAC2XML(query:ArrayCollection):void {
     var tempXML:XML = <products/>
     for (var p:uint=0;p<query.length;p++) {
          tempXML.appendChild(addXMLProduct(query[p]));
     }    

     xlcProducts.source = _productsxml.product;        
}
    
private function addXMLProduct(product:Object):XML {
     var xmlProduct:XML = <product productid="" category="" label=""/>
     xmlProduct.@productid = Number(product.productid);
     xmlProduct.@category = product.category;
     xmlProduct.@label = product.label;
     return xmlProduct;
}    

I'll get to what xlcProducts is in a moment, but first some other observations. The primary function converts an ArrayCollection to an XML. I probably could have done this generically, but I was a bit pressed for time, so I left it tightly coupled to the data in the incoming query (Quick aside: in case you don't know, ColdFusion queries are structures of arrays that Flex converts to ArrayCollections - go figure). So, as I iterate over the ArrayCollection, I pass the Object to addXMLProduct that converts it to an XML object.

Now, getting back to xlcProducts, I set its source to the <products>s that I just created. xlcProducts is an XMLListCollection and these guys are rather useful. What I want to do is to bind my Datagrid to the XMLListCollection and then control the displayed entities by use of a filterFunction.

The filterFunction takes the XMLList that is bound to the XMLListCollection and iterates over each XML object applying a rule that determines which ones are good to view and which should be hidden. The only thing you really need to remember is that anytime you make a change to a property that will affect, or possibly affect the filtering results, you need to explitely call the refresh() method of the XMLListCollection.

So let's assume that I want to enable a user to filter the resultset down to only those categories that match a string entered into a TextInput, and we don't want to make them enter in the whole string. That might look like this:

<mx:Script>
     <![CDATA[
     
     private function productFilter(item:Object):Boolean {
          if ( !String(item.@category).match(new RegExp("^" + searchTerms.text, "i")) ){
               return false;
          }
          return true;
     }

    ]]>
</mx:Script>
<mx:XMLListCollection id="xlcProducts" filterFunction="productFilter" />
<mx:TextInput id="searchTerms" change="xlcProducts.refresh()"/>    
<mx:Label text="Product Count: {xlcProducts.length.toString()}" />
<mx:DataGrid   id="dgProducts"
               rowCount="5"
               verticalScrollPolicy="on"
               dataProvider="{xlcProducts}">
        <mx:columns>
            <mx:DataGridColumn dataField="@productid" headerText="PID" width="50"/>
            <mx:DataGridColumn dataField="@category" headerText="Category"/>
            <mx:DataGridColumn dataField="@label" headerText="Type"/>
        </mx:columns>
    </mx:DataGrid>

Remember that we have set the source of xlcProducts in the first code snippet, so the XMLListCollection is bound to the XMLList of products. And you can see that we have a function, productFilter, that takes an Object, the XML of the <product> being inspected, and returns back true or false.

In this example, the filter basically says that if the text in the TextInput named searchTerms does not correspond to a category, return false, otherwise return true. The thing you need to remember is that we set the change event handler of searchTerms to refresh the xlcProducts and thus re-running the filter.

So just a quick little update; what if we wanted to bind to a ComboBox instead? All we need to do is to change the filterFunction and add the ComboBox:

<mx:Script>
     <![CDATA[
     
     private function productFilter(item:Object):Boolean {
          if (cbCategories.selectedIndex > 0) {
               if (item.@category != cbCategories.selectedItem.category) {
                    return false;
               }                
          }
          return true;
     }

    ]]>
</mx:Script>

<mx:ComboBox id="cbCategories" dataProvider="{_categories}" labelField="category" change="xlcProducts.refresh();"  />    

I'll leave it to you to figure out how you want to populate _categories, but what is worth noting is, again, the call to refresh() as a reaction to a change event and now we are looking at our ComboBox instead of the TextInput.

The really neat thing is that it really is pretty straightforward, but you can complicate the heck out of it by using incredibly complex regular expressions if you really want to. Or you can, like in the second example, skip them entirely - so really something for everyone.

Note that my example is based on filtering an xmllistcollection using the filterfunction property and regular expressions @ Flex Example, a really handy resource for picking up concepts like this.