Skip to main content
O
Outside5 Ltd
Inspiring
February 23, 2010
Question

Searching and ordering results by relevance

  • February 23, 2010
  • 1 reply
  • 1241 views

Hi all,

I am building a search function and would like some advice.

I am planning to allow people to search in a single field and am looping through each word in their search terms and returning a struct of IDs, then selecting the records relating to those ID's to output.

If the user searches for John Smith London, I want to search Forename, Surname and City.  This should return any instances of John, Smith and London from the db.  If the db contains a John Smith from London, he will be returned 3 times.  A Dave Jones from London will be returned once and so on.

I have it working up until this point.  What I want to be able to do next is to group the results and count the number of instances of each result, and order by count DESC, to list the record with the most results from the search term at the top of the results.

My code is below.  Any advice greatly appreciated.

Thanks,

Paul

<cfset results = structNew()>

<cfset x = 0>

<cfloop list="#FORM.q#" delimiters=" " index="q">
          
     <cfset x = x+1>

     <cfquery name="searchResults" datasource="#REQUEST.ds#">
          SELECT          userId
          FROM          users
          WHERE          surname LIKE <cfqueryparam value="%#q#%">
          OR          forename LIKE <cfqueryparam value="%#q#%">
          OR          town LIKE <cfqueryparam value="%#q#%">
     </cfquery>
     
     <cfoutput>
          <p>searchResults.regId = #searchResults.regId#</p>
     </cfoutput>
     
     <cfset structInsert(results, x, searchResults.regId)>
     
</cfloop>

<cfdump var="#results#">
    This topic has been closed for replies.
    1 reply
    D
    Dan_Bracuk
    Inspiring
    February 23, 2010
    You forgot to post your code but that's ok.  If you are not familiar with group by and order by clauses in queries then I have heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.
    O
    Outside5 LtdAuthor
    Inspiring
    February 23, 2010
    Thanks Dan,
    I think either you have misunderstood me, or I have misunderstood you.
    The script below is what I have so far...
    This will return 3 rows for regId 117 (matches John, Smith and Hampshire)
    This will return 2 rows for regId 113 (matches John and Hampshire)
    This will return 1 rows for regId 110, 111 and 115 (matches Hampshire)
    What I want is to output the results in the order of relevance based on the number of times a regId is picked up during the select (e.g. 117, 113, 110, 111, 115).
    <cfset resultsArray = arrayNew(1)>
    <cfset FORM.q = "john smith hampshire">
    <p><cfoutput>"#FORM.q#"</cfoutput>
    <cfloop list="#FORM.q#" delimiters=" " index="q">
     
        <cfquery name="searchResults" datasource="#REQUEST.ds#">
            SELECT regId
            FROM registrations
            WHERE surname LIKE <cfqueryparam value="%#q#%">
            OR forename LIKE <cfqueryparam value="%#q#%">
            OR county LIKE <cfqueryparam value="%#q#%">
        </cfquery>
     
        <cfloop query="searchResults"><cfoutput>
            <cfset arrayAppend(resultsArray, searchResults.regId)>
        </cfoutput></cfloop>
     
    </cfloop>
    <cfquery name="theResults" datasource="#REQUEST.ds#">
        SELECT *
        FROM registrations
        WHERE regId IN (#arrayToList(resultsArray, ',')#)
    </cfquery>
    <cfoutput query="theResults">
        <h2>#forename#, #surname#</h2>
    </cfoutput>
    D
    Dan_Bracuk
    Inspiring
    February 23, 2010
    You misunderstood me.  It's possible to get the results you want, sorted the way you want, with a single query.  I mentioned the necessary clauses in my earlier post.
    
        
                                                
    
        
    
    
    

            
    
                                        
    
            
                        
    
                                                                                                            
            
    

    

        
    
                    
    
    
    
    

    

    
    
    

                                        
    
                                                        
    
            
        
    
    

    

    

                                    
    Terms & ConditionsAccessibility statement
    
            
        

    

        
            
    
        
    
        



        
        
            


        
    

        
        
        
        
                                                        

                            
    
    
                    
    
  
    
    
    
      
        
      
    
    
    
    
      
        Using the community
      
      
        Terms of use
      
      
        Privacy
      
      
        Cookie preferences
      
      
        Do not sell or share my personal information
      
      
        ad choicesAdChoices