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.

<cfquery name="qryBadGuy" datasource="#variables.DSN#" >
select bg.bgFirstName, bg.bgLastName, bg.bgID, bga.bgaAlias
from tblBadGuy bg
join tblBadGuyAlias bga
on bg.bgID = bga.bgID
where bg.bgID =
<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.bgID#">

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:

<cfset var badGuyAlias = arrayNew(1) />

<cfloop query="qryBadGuy">
    <cfset arrayAppend(badGuyAlias, qryBadGuy.bgaAlias) />
<cfset aBadGuy.setBGAlias( badGuyAlias ) />

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:

<cfset aBadGuy.setBGAlias( listToArray( valueList(qryBadGuy.bgaAlias) ) ) />

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


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:

<cfdump var="#listToArray( valueList(qryBadGuy.bgaAlias) )#" />

I get the following result:

1 Street Sweeper
2 CF Bad Ass
3 Master Gasser

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.


ColdFusion function valueList, ColdFusion 8 CFML Reference

ColdFusion function listToArray, ColdFusion 8 CFML Reference

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
Some people will frown on this as it's leveraging the "internals" of CF, but one can also do this:

myArray = qryBadGuy["bgaAlias"].toArray();

The CF query object exposes a toArray() method as public, so it's there to be used in my opinion. YMMV.

# Posted By Adam Cameron | 11/27/07 6:21 AM
Oh yeah.

The listToArray(valueList(q.col)) approach will also drop any rows in the query which have NULLs in them. So only really useful if one is CERTAIN that will not be the case (like with a PK column, or suchlike).


PS: If one accidentally clicks "cancel" on the comment submission form (like I just did, because I'm a goof), then cancels the pop-up... the message one's typed disappears. Which is annoying. :-)
# Posted By Adam Cameron | 11/27/07 6:25 AM
Adam - appreciate your comments. Thanks for the additional information.

The listToArray function has an optional parameter named includeEmptyFields that you can set to true. When that parameter is set to true, empty elements in the list are converted to empty elements in the array

However, I would think that for most use cases you don't want null (empty) array elements since those array elements would have no meaning for your bean's property. So I think the default (includeEmptyFields is false) would be what you normally want.
# Posted By Bruce | 11/27/07 6:36 AM
Ah: Good to know. That's new to CF8 (which I ain't had opportunity to touch, yet).

Good that they're getting around to fixing the list functions. [checks the docs]... OK, they've made a *start* anyhow.

Cheers for the heads-up.

# Posted By Adam Cameron | 11/27/07 2:36 PM
BlogCFC was created by Raymond Camden. This blog is running version Contact Blog Owner