Relevance searches (scoring results) 1

[JohnandSwifty]

Hi,

Could someone put me on the right track when i comes to judging the relevance of records returned in a search (of an SQL DB).

I have a search where I split the typed phrase into words and then match the words to records accross several tables. I need to be able to sort those results by which ones matched the most words/ frequency etc. Straight off i could obviously loop the results over and over for each word and build a score that way, but I'm sure theres a 'good' way to do it!

Maybe this is a question for the SQL forum?...

Thanks

 

[r937]

select foo
     , case when bar like '%aaa%' then 1 else 0 end
      +case when bar like '%bbb%' then 1 else 0 end
      +case when bar like '%ccc%' then 1 else 0 end
         as relevance
  from daTable
 where bar like '%aaa%'
    or bar like '%bbb%'
    or bar like '%ccc%'   
order
    by relevance desc

got the general idea?

r937.com | rudy.ca

 

[philhege]

What flavor of SQL?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[JohnandSwifty]

Thanks Rudy - I suppose my concern with that sort of process was the extra processing involved, i was wondering if Verity is more than just file searching? Also if (to answer philhege's question) SQL server 2005 has some inbuilt indexing i could call on?

In any case the above is neither here nor there if you are telling me this is a common method for this kind of thing and the extra time is negligible compared to the other options?...

Thanks

 

[r937]

yes, Verity is much more than file searching, it requires a completely separate index

extra processing? negligible, once the rows have been pulled out via the WHERE clause

r937.com | rudy.ca

 

[JohnandSwifty]

OK thats sounds pretty good then, thank you. For arguments sake why would i opt for verity over using my own features like this? Is it more for monstrous Databases? I am currently working on a project where the system will hold around 12000 contact records and then rapidly growing numbers of communications and projects in turn. Does is sound like a job for Verity? - What is a job for Verity?

Thanks again,
John

 

[r937]

my understanding is that Verity is for all types of documents, and database-generated pages would be only one source

yes SQL2005 has built-in fulltext indexing, but that too takes time to set up

if SQL with LIKE works for you, that's the simplest search, although you have to supply some sort of relevancy calculation yourself

r937.com | rudy.ca

 

[JohnandSwifty]

OK - well the solution you supplied is working really well for me so I'll stick with it - thanks!

 

[JohnandSwifty]

Hello again Rudy,

Could you help me a little more...

Below is the outputted SQL from my query (in this example the searchPhrase contains 3 words which i loop through to generate the second, third and fourth blocks). The query doesnt throw an error, but it does still return all results that match any of the words in the phrase, i want to only return those results that match either the whole phrase, or every word in the phrase - any suggestions?

WHERE Communications.Del <> 1
AND
(
Contacts.Code LIKE (param 1)
OR Contacts.FirstName LIKE (param 2)
OR Contacts.LastName LIKE (param 3)
OR Users.Code LIKE (param 4)
OR Users.FirstName LIKE (param 5)
OR Users.LastName LIKE (param 6)
OR ClassCommunicationTypes.Code LIKE (param 7)
OR ClassCommunicationTypes.Name LIKE (param 8)
OR Communications.Code LIKE (param 9)
OR Communications.Owner LIKE (param 10)
OR Communications.Contact LIKE (param 11)
OR Communications.Method LIKE (param 12)
OR Communications.Direction LIKE (param 13)
OR Communications.Title LIKE (param 14)
OR Communications.Notes LIKE (param 15)
OR Communications.Created LIKE (param 16)
OR Communications.Status LIKE (param 17)
OR
(
Contacts.Code LIKE (param 18)
OR Contacts.FirstName LIKE (param 19)
OR Contacts.LastName LIKE (param 20)
OR Users.Code LIKE (param 21)
OR Users.FirstName LIKE (param 22)
OR Users.LastName LIKE (param 23)
OR ClassCommunicationTypes.Code LIKE (param 24)
OR ClassCommunicationTypes.Name LIKE (param 25)
OR Communications.Code LIKE (param 26)
OR Communications.Owner LIKE (param 27)
OR Communications.Contact LIKE (param 28)
OR Communications.Method LIKE (param 29)
OR Communications.Direction LIKE (param 30)
OR Communications.Title LIKE (param 31)
OR Communications.Notes LIKE (param 32)
OR Communications.Created LIKE (param 33)
OR Communications.Status LIKE (param 34)
AND
(
Contacts.Code LIKE (param 35)
OR Contacts.FirstName LIKE (param 36)
OR Contacts.LastName LIKE (param 37)
OR Users.Code LIKE (param 38)
OR Users.FirstName LIKE (param 39)
OR Users.LastName LIKE (param 40)
OR ClassCommunicationTypes.Code LIKE (param 41)
OR ClassCommunicationTypes.Name LIKE (param 42)
OR Communications.Code LIKE (param 43)
OR Communications.Owner LIKE (param 44)
OR Communications.Contact LIKE (param 45)
OR Communications.Method LIKE (param 46)
OR Communications.Direction LIKE (param 47)
OR Communications.Title LIKE (param 48)
OR Communications.Notes LIKE (param 49)
OR Communications.Created LIKE (param 50)
OR Communications.Status LIKE (param 51)
)
)
)

ORDER BY Communications.Created DESC

 

[r937]

you said 3 words, but i see 51

as for matching every word, change the ORs to ANDs

r937.com | rudy.ca

 

[JohnandSwifty]

The params are the same word - the last block is created each time the loop runs over a word in the phrase - so my coldfusion code looks like -

WHERE Communications.Del <> 1

<cfif URL.QuickFind EQ '' >
<cfelse>
AND (

Contacts.Code LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">
OR Contacts.FirstName LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">
OR Contacts.LastName LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">

OR Users.Code LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">
OR Users.FirstName LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">
OR Users.LastName LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">

OR ClassCommunicationTypes.Code LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">
OR ClassCommunicationTypes.Name LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">

OR Communications.Code LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">
OR Communications.Owner LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">
OR Communications.Contact LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">
OR Communications.Method LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">
OR Communications.Direction LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">
OR Communications.Title LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">
OR Communications.Notes LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">
OR Communications.Created LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">
OR Communications.Status LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#URL.quickFind#%">

OR (
<cfset loopNum = 0>
<cfloop list="#URL.quickFind#" delimiters=" " index="i">
<cfset loopNum = loopNum + 1>
<cfif loopNum GT 1>
AND (
<cfelse>

</cfif>
Contacts.Code LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">
OR Contacts.FirstName LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">
OR Contacts.LastName LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">

OR Users.Code LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">
OR Users.FirstName LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">
OR Users.LastName LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">

OR ClassCommunicationTypes.Code LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">
OR ClassCommunicationTypes.Name LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">

OR Communications.Code LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">
OR Communications.Owner LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">
OR Communications.Contact LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">
OR Communications.Method LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">
OR Communications.Direction LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">
OR Communications.Title LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">
OR Communications.Notes LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">
OR Communications.Created LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">
OR Communications.Status LIKE
<cfqueryparam cfsqltype="cf_sql_varchar" value="%#i#%">
<cfif loopNum GT 1>
)
<cfelse>

</cfif>
</cfloop>
)
)