Help with CF8 autosuggest wildcards

[Pete_L_]

I have a cfinput type="text" using the autosuggest attribute, which reads a CFC and returns a string of values from a query. It all works very nicely except that I'd like to make the query a true wildcard search:

SELECT DISTINCT collegeName FROM college WHERE collegeName LIKE <cfqueryparam value="%#arguments.suggestvalue#%" cfsqltype="cf_sql_varchar">

For whatever reason, the % at the end of the value works fine (if I type Ohio, I get Ohio State, Ohio University, etc.) but if I type something that's in the middle of a value, it doesn't work. It's like the first % is being ignored. I know this isn't typical behavior for a type-ahead, but I need it to work in this one case.

Anybody have suggestions?

[Newsgroup_User]

not sure why it does not work - i have not used or tested having to
wildcard characters in same value.

i am pretty sure separating your WHERE clause into 2 (or 3) separate
ones with OR operators will do the trick, though:

SELECT DISTINCT collegeName
FROM college
WHERE
collegeName = <cfqueryparam value="#arguments.suggestvalue#"
cfsqltype="cf_sql_varchar">
OR collegeName LIKE <cfqueryparam value="#arguments.suggestvalue#%"
cfsqltype="cf_sql_varchar">
OR collegeName LIKE <cfqueryparam value="%#arguments.suggestvalue#"
cfsqltype="cf_sql_varchar">

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Pete_L_]

I just tried that and while I agree it should work, it doesn't. I'm starting to think this is a setting in the autosuggest feature built in to CF. Not show how they are doing that though. If I switch the CFC to JUST the '%value' clause, I get no results no matter what I type, so it seems like they are preventing that.

I am returning the values as a string of values (1,2,3,4) instead of an array, so I wonder if that makes any difference.

[Pete_L_]

I think I've pretty much confirmed this is something in the javascript CF8 uses to build the autosuggest values. I can call my CFC with the WHERE clause like '%value%' and just dump the return array and I see the expected results. But calling the same value in the cfinput autosuggest field I only get the "starts with" values. So, perhaps I'm out of luck here. I'm not sure it's worth implementing another autosuggest function from another AJAX library. Unless someone knows how to modify the core files...

[Newsgroup_User]

if you run the query outside of the autosuggest, does it return all the
expected results? i mean, it is just a query - it should return what it
finds, autosuggest or not...

do you have any values with 'ohio' in the middle?

if you omit the <cfqueryparam> and just use LIKE
'%#arguments.suggestvalue#' in your query - does it change the results?

also, just found a suggestion on livedocs:

... LIKE '%' + <cfquerypama without any % in value> + '%'

see if above works...

hth

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Pete_L_]

I think the CFC and query are performing correctly -- If I just load it and dump the result, I get what I expect. Using the same CFC with the same values in the typeahead only gives me "starting with" matches. It's like the javascript that is called to generate the dropdown is also filtering based on the "starts with" value.

For example, if I pass "Notre" to the CFC, I get an array back with "Notre Dame College, Notre Dame Seminary, College of Notre Dame, University of Notre Dame". But when the same value ("Notre") is passed and used in the autosuggest, I only see "Notre Dame College, Notre Dame Seminary".