5 Replies Latest reply on Mar 12, 2008 12:38 PM by Pete L.

    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?

        • 1. Re: Help with CF8 autosuggest wildcards
          Level 7
          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
          collegeName = <cfqueryparam value="#arguments.suggestvalue#"
          OR collegeName LIKE <cfqueryparam value="#arguments.suggestvalue#%"
          OR collegeName LIKE <cfqueryparam value="%#arguments.suggestvalue#"

          Azadi Saryev
          • 2. Re: Help with CF8 autosuggest wildcards
            Pete L. Level 1
            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.
            • 3. Re: Help with CF8 autosuggest wildcards
              Pete L. Level 1
              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...
              • 4. Re: Help with CF8 autosuggest wildcards
                Level 7
                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...


                Azadi Saryev
                • 5. Re: Help with CF8 autosuggest wildcards
                  Pete L. Level 1
                  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".