Using The ValueList and ListToArray Functions To Convert Values Stored In A Query Column To An Array

I recently was fortunate to study an advanced ColdFusion developer's code. I noticed his use of the valueList and listToArray functions to convert the values stored in a query column to an array. The array then was used to provide the bean with a value for one its properties that was storing multiple values.

Here is an example of a use case where you could apply this technique. Let's say we have a BadGuy CFC and a BadGuyDAO CFC. The BadGuy is modeling a criminal and we must track the following properties:

1. First Name
2. Last Name
3. Alias

An examination of existing records shows that most bad guys have multiple aliases, so our data type for the Alias property is an array.

There are two tables used to store the above information, with the following schema

tblBadGuy - bgID (int) (PK), bgFirstName ( varchar(100) ), bgLastName ( varchar(100) )

tblBadGuyAlias - bgaID (int) (PK), bgID (int) (FK), bgAlias ( varchar(100) )

In the BadGuyDAO's read method, I can use the following query to pull out a specific record from tblBadGuy and all the associated aliases from tblBadGuyAlias.

To provide our BadGuy bean with values for its properties we can call its set methods and pass each set method the appropriate query column value. For example:

Before I can call the aBadGuy.setBGAlias function, I need to create an array that stores all of the values from the bgaAlias query column. Before I learned the technique of using functions valueList and listToArray, this is what I would typically do:

The above code uses a for loop to iterate over the query, pull out the value of the query column (qryBadGuy.bgaAlias) one at a time, and append the value to a local array variable (badGuyAlias). When the for loop is finished, I then call the set function of the BadGuy bean to set the bgAlias property.

Since this code worked I never thought of doing it differently. However, after studying a more advanced ColdFusion developer's code, I learned a new technique that saves me writing 4 lines of code and of course is much cooler. Here's an example:

Let's break down the above line of code into smaller parts and discuss what each part is doing. First look at:

valueList(qryBadGuy.bgaAlias)

I pass the bgaAlias query column to the valueList function. See the references below for the full details about the valueList function. The function takes 1 or 2 arguments. The first argument is a query column and the optional second argument is a delimiter. The default delimiter is a comma. Just running the above line of code returns this result:

Street Sweeper,CF Bad Ass,Master Gasser

The above result is then passed to the listToArray function. The references below has a link to the documentation for this function. The listToArray function copies the list items into an array. So when I call the listToArray function, it takes the list returned by the valueList function call and returns an array with an element for each list item. So just running this line of code:

I get the following result:

So now I have an array that I then just pass to aBadGuy.setBGAlias() and BAM! one line of code replaces 5 lines! After studying this developer's excellent code, I kind of feel like the Detroit Lions after they played the Green Bay Packers: whipped and tired, but just a little smarter. Next time, I'll play (code) better.

References

ColdFusion function valueList, ColdFusion 8 CFML Reference

ColdFusion function listToArray, ColdFusion 8 CFML Reference