A reader asked me a simple Ajax-y type question this morning and I thought I'd turn it into a simple blog post. We've all done, or at least seen, related selects before. That widget where you select something in one drop down and it drives the data in the second drop down. What he was asking is how you would support being able to select multiple items in the first drop down. Here is an example of that. Before I go on, let me say that while the back end of this is built in ColdFusion, it absolutely does not matter what back end you use. Oh, and please do not ask me to build you a PHP version! ;)
Let's begin by building a simple version of related selects. Here's the HTML:
<html>
<head>
<title>My Page</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
</head>
<body>
<form>
<select id="state">
<option value="0">-- Select a State --</option>
<option value="1">California</option>
<option value="2">Louisiana</option>
<option value="3">Texas</option>
</select>
<select id="city">
<option>-- Select a City --</option>
</select>
</form>
<script src="app.js"></script>
</body>
</html>
Nothing too terribly interesting here, but make note of the two drop downs. The first is for states and the second is for cities. This one will be driven by JavaScript. I was lazy and only typed in three states. Now let's look at the JavaScript.
$(document).ready(function() {
$stateDropdown = $("#state");
$cityDropdown = $("#city");
$stateDropdown.on("change", function(e) {
var state = $(this).val();
//always clear
$cityDropdown.empty();
//reinject the default
$cityDropdown.append("<option>-- Select a City --</option>");
//if blank, do nothing
if(state === 0) return;
$.getJSON("service.cfc?method=getCities", {state:state}).done(function(res) {
for(var i=0, len=res.length; i<len; i++) {
$cityDropdown.append("<option>"+res[i].CITY+"</option>");
}
});
});
});
Again, nothing too complex here if you've built one of these things before. Notice a change - get the value - and update the related select. Finally, let's look at the server side component. Again, this is ColdFusion, but you could, I hope, see how this would be done in your language of choice. Like Node. Or Node. But, yeah, whatever you prefer:
component {
//nice defaults
url.returnformat="json";
url.queryformat="struct";
//include a file to create a fake query we can select against
include "fake.cfm";
remote query function getCities(required numeric state) {
return queryExecute("select city from cities where state = :state", {state={value:arguments.state, cfsqltype:'cf_sql_integer'}}, {dbtype:'query'});
}
}
All this component does is define one method, getCities, that runs a query against a query (a ColdFusion feature that lets you treat an existing query like a database table). The data is simply hard-coded in an external file.
<cfscript>
variables.cities = queryNew("state,city", "integer,varchar", [
{state:1, city:"San Francisco"},
{state:1, city:"San Mateo"},
{state:1, city:"Mountain View"},
{state:2, city:"Lafayette"},
{state:2, city:"Lake Charles"},
{state:2, city:"Catahoula"},
{state:3, city:"Houston"},
{state:3, city:"Dallas"},
{state:3, city:"Fort Worth"}]);
</cfscript>
Ok, so that's it. You could make this a bit more intelligent perhaps with some caching on the client-side, but that's really it. So how do we update this for multiple items?
Well, first, obviously, we add "multiple" to the first drop down. I won't reshare the entire HTML file as it is almost the exact same. The JavaScript is a little bit different though.
$(document).ready(function() {
$stateDropdown = $("#state");
$cityDropdown = $("#city");
$stateDropdown.on("change", function(e) {
var state = $(this).val();
//state is an array, convert it to a list
state = state.join(",");
//always clear
$cityDropdown.empty();
//reinject the default
$cityDropdown.append("<option>-- Select a City --</option>");
//if blank, do nothing
if(state === 0) return;
$.getJSON("service.cfc?method=getCities", {state:state}).done(function(res) {
for(var i=0, len=res.length; i<len; i++) {
$cityDropdown.append("<option>"+res[i].CITY+"</option>");
}
});
});
});
So - the only real difference here is handling the fact that jQuery will return the multiple items as an array. We can easily convert that to a list of values using join. This turns the array into a simple string we can pass to our service. Finally, let's look at the change in the server-side code.
component {
//nice defaults
url.returnformat="json";
url.queryformat="struct";
//include a file to create a fake query we can select against
include "fake.cfm";
remote query function getCities(required string state) {
return queryExecute("select city from cities where state in (:state)", {state={value:arguments.state, cfsqltype:'cf_sql_integer', list:true}}, {dbtype:'query'});
}
}
So - a few small changes. First, our method signature went from requiring a number to a string. This lets us accept a value like 1, or 5,6. Next, the query was updated to do a "state in ()" type search. Again, this will work with one value or multiple. And... that's it. I've included a zip of both versions. The ColdFusion code requires the latest version if you want to run it as is.
Archived Comments
Thanks, Raymond! One question: what do I need to change if I'm running CF9 or CF10?
queryExecute is CF11. To use it in 10 or 9, you would need the query CFC we shipped with those versions.
Ah, I see you're using the new url.queryformat="struct"; that you mentioned earlier.
But my takeaway from that earlier blog post was that the older url.queryformat="column" was leaner.
Can you write a version of this in Railo? (Kind of being funny, but kind of being serious too).
I've found that I can't use res[i].CITY any longer but instead must refer to DATA[0].
I might have missed something in converting my ColdFusion personal project to Railo.
"But my takeaway from that earlier blog post was that the older url.queryformat="column" was leaner." It can be, yes, but the question is, does it matter, and would you rather have something easier to use on the client side. In most cases, I'm going to prefer the easier to use format.
"Can you write a version of this in Railo? (Kind of being funny, but kind of being serious too)."
Nope. :)
"I've found that I can't use res[i].CITY any longer but instead must refer to DATA[0]."
If you like the nicer array format, you can do it in the CFC in a utility method.
Thanks for the help, Raymond. I was able to translate the queryExecute statement to one using the query CFC. It generates a correct JSON result (can see it in in the HTML tab of the XHR tab in Firebug) as I choose the different states. But, for some reason it's not updating the City dropdown. It seems that the result is not getting passed to the res function in the javascript. I know it gets into that function (tested with a simple alert), but nothing seems to be in the array. Is there something about how ajax works in CF11 that has changed, also? Or, any other hints?
Guy
Yes - CF11 introduced a new way to return JSON data of queries - as an array of structs. You can't do that in CF10 unless you write code to convert the query into an array of structs and THEN return it to the caller.
Thanks. Your solution is very elegant, which is what appealed to me in the first place. But for my situation (ISP on an older version of CF), I think this is getting to the point where I wait for the upgrade. Just not as straight forward in my environment. Maybe when I have more time. Thanks for yours.