Ask a Jedi: ColdFusion Autosuggest on 2 Columns

This post is more than 2 years old.

Mike asks:

I'm working on a suggestion list for a search box, is it possible to query more than one column and return that from the binding CFC?

Absolutely. Remember, ColdFusion's autosuggest control doesn't care how you create the result, it just cares that you return an array of strings. How you create the array is up to you. When I first wrote back to Mike, I recommended simply running two queries. Then I remember that a union would work fine as well. Here is the example I created.

Ok, let's start on the front end:

<cfform name="doesntmatter">

<cfinput name="chosen" autoSuggest="cfc:test.getSuggest({cfautosuggestvalue})">

</cfform>

It's truly unfortunate that ColdFusion doesn't make AJAX easier. Look at all the code above. All the messy JavaScript. I really wish ColdFusion did more to make this easier for me. It took me at least 35 seconds to write that code. Maybe even 40. I'm sure PHP does a better job here, so why can't ColdFusion? -sigh-

All kidding aside, you got to love the binding support in ColdFusion. Let's now take a look at the CFC's getSuggest method:

<cffunction name="getSuggest" access="remote"> <cfargument name="suggest" type="string" required="true"> <cfset var search = "">
&lt;cfquery name="search" datasource="cfartgallery"&gt;
select art.artname as s
from art
where art.artname like &lt;cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.suggest#%"&gt;
union
select artists.lastname as s
from artists
where artists.lastname like &lt;cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.suggest#%"&gt;
&lt;/cfquery&gt;

&lt;cfreturn listToArray(valueList(search.s))&gt;	

</cffunction>

As you can see, I search against two columns in two different tables. The result is a query with a column called S that contains the matches. Note: ColdFusion's autosuggest only allows you to do 'frontal' matches. So if you want "der" to match "Vader", you are out luck. That's why my search only uses a % at the end. I can return other matches but they won't show up.

Wait! I double checked the docs (always good to do so when you are absolutely sure you are right!) and turns out I was wrong. The cfinput tag has a "matchContains" attribute. It defaults to false. If true, then the autosuggest can match anywhere in the result.

So I changed the front end input to:

<cfinput name="chosen" autoSuggest="cfc:test.getSuggest({cfautosuggestvalue})" matchcontains="true">

and changed my cfqueryparam (both of them) to:

<cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.suggest#%">

and it worked like a charm. I'm very happy to be wrong about that. Anyway, hopefully you see that the autosuggest can be built up anyway you want. I could even mix a cfdirectory query with a database query. Whatever your needs are - just ensure you've got an array at the end and your good to go.

Edit: Ok, so apparently, matchcontains is ColdFusion 9 only. I checked the ColdFusion 8 docs and it isn't there. Unfortunately, Adobe forgot to list this in the tag history, so I mistakenly assumed it was there in 8 and I missed it. I've blogged before about the updates and how you had to dig a bit to find the changes. Apparently this is one of those things didn't get added to the history/tag changed docs. So obviously the core request here - autosuggest against N columns - is possible in ColdFusion 8. The only thing my matchcontains does is allow you to match anywhere in the result. For ColdFusion 8, simply leave that attribute off, and ensure your query uses X%, not %X%.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Robert posted on 1/31/2011 at 10:31 PM

Hey Ray,
What if I wanted to takes this a step further and auto-fill form fields based upon what the user ultimately selects in "chosen"?
Can I return an array of multiple columns from that query and somehow autofill other fields on the page?

Comment 2 by Raymond Camden posted on 2/1/2011 at 8:26 PM

Unfortunately no. The auto select in CF is really meant to return just data for the drop down. This is trivial though in jQuery's autocomplete.

Comment 3 by Robert posted on 2/1/2011 at 8:38 PM

Ok cool, thanks Ray. I'll look into the jQuery autocomplete.

Comment 4 by Michael Appenzellar posted on 2/4/2011 at 7:45 PM

One thing I am trying to figure out is, is it possible to do an autosuggest but if you type in the last say...4 digits of a value, the result will still come up? Ex: The list of database values are very similar for the first 10 characters, but only the last 4 or 5 change. So I don't want the user to have to type in the first 10 to get the autosuggest to start working...does this make since?

Comment 5 by Michael Appenzellar posted on 2/4/2011 at 7:47 PM

NEVERMIND, matchcontains="true" worked like a charm....sorry for the bad post!

Comment 6 by Raymond Camden posted on 2/4/2011 at 7:50 PM

No worries Michael - thanks for posting what you found though as I'm sure others may have the same question.

Comment 7 by puspita posted on 7/12/2011 at 1:35 PM

anybody.. can help me to send source code about that.
Because it doesn't work, and i am newbie with coldfusion. Thanks anyway :)

Comment 8 by Raymond Camden posted on 7/12/2011 at 2:27 PM

Sorry - what doesn't work?

Comment 9 by shinta posted on 7/29/2011 at 2:09 PM

I have query his,
SELECT a.Name,c.Client FROM tAnggota a,tClient c WHERE a.Name LIKE <cfqueryparam cfsqltype="cf_sql_varchar"value="%#ucase(arguments.search)%" /> AND c.Client = "#FORM.txtClient#" AND c.CodePK =a.CodeFK Order by a.Name

and '#FORM.txtClient#' made error in my file.
The error is :
Error invoking CFC/dir/autosuggest.cfc : Element TXTXCLIENT is undefined in FORM. [enabling debugging by adding 'cfdebug' to your URL parameter to seemore information]
I hope you answer my question. Thanks

Comment 10 by Raymond Camden posted on 7/29/2011 at 3:22 PM

The error is pretty clear. That key does not exist in the form scope. This isn't a form post happening here - rather CF is grabbing a form field and passing the data itself.

Comment 11 by Ray Meade posted on 3/30/2012 at 12:30 AM

Ray, is there a way to make the autosuggest feature work on a cfgridcolumn? (preferably in a flash formatted cfgrid)

Comment 12 by David Jaocbson posted on 8/28/2012 at 6:28 PM

Ray, Is it possible to return more than one field, much like a dropdown that has a display value and an actual value?

Comment 13 by Raymond Camden posted on 8/28/2012 at 7:32 PM

Not with the CF control. You can do it with others - like the jQuery UI one.

Comment 14 by ManOnTheMoon posted on 1/12/2013 at 3:28 AM

Is there a way to mimic the matchcontains="true" in CF8?

Comment 15 by Raymond Camden posted on 1/12/2013 at 3:52 AM

Not w/o modifying the code. I'd just use the autosuggest from jQuery UI.

Comment 16 by ManOnTheMoon posted on 1/12/2013 at 11:59 PM

Thank you! I'll give it a go :)

Comment 17 by ion posted on 4/10/2013 at 11:59 PM

is there a way to retrieve 2 columns, say, ID and Name, display only the Name and, onClick, set a field with that ID value?

Thanks!

Comment 18 by Raymond Camden posted on 4/11/2013 at 1:34 AM

No. I'd recommend you use something like jQuery UI for this. (In general, I no longer recommend folks use the CF AJAX UI tags!)

Comment 19 by Steve posted on 11/21/2013 at 8:59 PM

Ray,

That matchContains tidbit really helped. I am having an issue though. Is there any way to get it to return _all_ the results? My client wants to be able to have it just give her all the results if she hits the spacebar, so that she doesn't have to remember the list, or have to guess. (I know, then that's a dropdown. Mine is not to question why). I am already using the jQuery autosuggest to get data that populates another cfinput. this second cfinput is based on the first one. i.e. give me all the sub projects, based on the projects you just got. I couldn't figure out how to daisy chain the jQuery calls so the first is jQuery, the second is CF.

So summing up I guess the questions are: can cf autosuggest return all rows, OR can you pass 2 arguments to jQuery autosuggest?

Code:
<script>
<!---// this script pulls in data for the autocomplete of the project name. --->
$(function(request, response) {
var options = {
source: "getProjectWithSubView.cfc?method=get_ProjectData&returnformat=json",
dataType: "json",
minLength: 0,
data: {
Search: request.term,
maxRows: 30
},
success: function(data) {
response(data);
}
};

<!--- // this is the class for the field that activates autocomplete. --->
$("input.searchInput").live("focus.autocomplete", function() {
$(this).autocomplete(options);
});

var addInput = function() {
var inputHTML = "<input name='search' value='' class='searchInput' maxlength='20' />";
$(inputHTML).appendTo("form#myForm");
$("input.searchInput:last").focus();
};

if (!$("form#myForm").find("input.searchInput").length) {
addInput();
}

<!--- //adds the data into the field once it is clicked in the 'dropdown' --->
$("input#addButton").click(addInput);
});
</script>

a hidden field then fires off a standard SQL that populates another filed:
<cfinput name="Sub_Project_Name" id="Sub_Project_Name" type="text" size="15" maxlength="16" tooltip="Sub Project Names - Press spacebar to see full list" autosuggest="#ValueList(getSubProjectParent2.Sub_Project_Name)#" autosuggestminlength="1" matchContains="true">

Thanks!
Steve

Comment 20 by Raymond Camden posted on 11/22/2013 at 5:44 AM

Steve, this is an *incomplete* answer and I did not read your comment completely. I'm currently on the road and will not be back till after Thanksgiving. As a quick fix, you may be able to build your CFC such that if " " is passed as a search string, your query returns everything.

I'm assuming this answer probably isn't right, but I was able to get it out quickly and not make you wait a week. ;)

Comment 21 by Raymond Camden posted on 11/24/2013 at 3:33 AM

Still a half ass answer, but:

1) Please, please, please do not mix cfinput (and other CF client stuff) with jQuery (or any other JS stuff). You may not want to hear this, but you should avoid any and all usage of CF client side stuff. Yes, I blogged about it. A *lot*. And to be fair, I know some people make good use of it, but I cannot recommend enough that you do things the "right" way (and yes, that is my opinion, but also those of *many* in the CF upper echelons) and focus on just doing the JS yourself.

"So summing up I guess the questions are: can cf autosuggest return all rows, OR can you pass 2 arguments to jQuery autosuggest?"

To your first question, no, because " " won't work with matchContains unless your data actually has a space. Now, to be fair, I haven't tested it, but I'd be surprised if it worked.

To your second question: I think you mean, using jQuery Autosuggest, when I ping a server side service, can I pass multiple arguments. If so, yes. You can do whatever you want for the XHR call. Please see their docs for more info.

Comment 22 by Balu posted on 7/31/2014 at 9:28 AM

Hi Ray,

I am using the below code for autosuggest. Here the autosuggest clearly works to show the suggestion. But I want to get the value field changed as per the item I am selecting using auto suggest. Is there any way to do that?

<cfinput name="mfg" onchange="setNetworkModelList();" autosuggest="#ValueList(qGetDeviceMFGs.mfg)#" value="#qGetDeviceMFGs.mfg#" matchContains="true">

Thanks,
Balu

Comment 23 by Raymond Camden posted on 7/31/2014 at 3:53 PM

I do not understand your question.