• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

ColdFusion AutoSuggest Question

Hi,
I am doing an Ajax autosuggest (see code below).   I only return 5 choices at a time.

My question is should the SQL (using SQL Server 2005)  use

Select Distinct top(5) OrgName  versus
Select Distinct  OrgName

There is an order by so SQL Server has to sort them all but if I only need 5 returned, it should be faster?  But I'm not sure how this works in ColdFusion?
FORM FIELD:
<cfinput type="text" name="orgName" size=30  maxlength="100"
                  autosuggest="cfc:org-methods.orgName_autosuggest({cfautosuggestvalue})" 
                  autosuggestMinLength="2"
                  maxResultsDisplayed = "5"/>  



CFC:
<cffunction name="orgName_autosuggest"  access="remote" returnType="array">
      <cfargument name="search" type="any" required="false" default="">

<cfset var OrgNameSearch="">
<cfset var OrgResult=ArrayNew(1)>

<cfquery name="OrgNameSearch" datasource="#application.socialDSN#">
       Select Distinct OrgName 
         from TestOrganization 
         WHERE Upper(OrgName) Like Upper('#Arguments.search#%')  
         ORDER BY OrgName ASC 
      </cfquery>

<cfloop  query="OrgNameSearch">  
     <cfset arrayappend(OrgResult, OrgName)> 
</cfloop>
    
<cfreturn OrgResult>

</cffunction>

Open in new window

Thanks,
hefterr



 
0
hefterr
Asked:
hefterr
  • 4
  • 3
1 Solution
 
_agx_Commented:
I wouldn't, and here's why.  

Autosuggest only fires when it can't find a match for what you typed within the data it has.  If you only return 5 items at a time, there's no guarantee they'll be the right ones. Say there are 10 matching records for what the user typed. And the record they want is #7. If you only returned the TOP 5, the user might not find the one they want, which would be confusing. Not to mention frustrating if they knew the record really did exist.
0
 
hefterrAuthor Commented:
Hi agx_,
OK.  Thanks..  That was the kind of answer I was looking for as I did not know when the Autosuggest would fire up the remote CFC.  I thought it was on every keystroke.  In that case my question would have more relevance.

hefterr
0
 
hefterrAuthor Commented:
Thanks again!!
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
_agx_Commented:
    >   I thought it was on every keystroke.

I used to think that too :) Enable ajax debugging in the CF Admin (and add ?cfdebug to your page) and you can see what's it's doing.

When you start typing, the javascript control sends an initial request to .cfc for data matching what you typed.  It then caches the data on the client side and filters it as you type. It only sends a new request to the server when it can't find a match in it's cache.

Example:
1)  You type  "Jo"
2)  The autosuggest has no data. So it sends a request to the server for records starting with "Jo". It caches the returned data and displays the first X records that match (ie maxResultsDisplayed):
     
         - Joanne
         - Job
         - Johan
         - John
         - ....

3) Then you type another character "h". The autosuggest filters the data it already has, and displays the next 5 items matching "Joh"

         - Johan
         - John
         - ....

(... Lather-rinse-repeat ;-)
0
 
_agx_Commented:
Gah... I left off #4

4) You type another character "x". Autosuggest can't find a match in it's cache. So it sends off a new request to the server. Then displays the next 5 items matching "Johx" , if there are any.

0
 
hefterrAuthor Commented:
Is that approach specific to the ColdFusion implementation or is that a standard JS/Jquery appraoch?  Just curious.

0
 
_agx_Commented:
Not sure. But it seems like it would be standard to js/jquery. Otherwise, autosuggest would be terribly inefficient.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now