asked on

How to do a ranking/relevance search with both varchar(fulltext) & int fields in one query

I am trying to write a search query that will return results with a ranking/relevance value to use as the display order. For example, I have a lastname field, a firstname field, and a studentId field. I want to allow the user to type in one or more fields and display ranked results. Even if one field they type in does not match this should just make the ranking lower and not exclude the record (this works because of the union). In the code below I am able to sort by relevance, but I don't know how to add the search of my studentId (int) field into the query with a valid relevance. I know that I cannot use fulltext on an int field. Is there anyway to make this work? Any help is appreciated. Thanks!

example data:
studentId lastName firstName
123 james barb
434 roberts john

If the user searches on lastName = james, studentId = '434', firstName = 'john' the results should be

john roberts (2 fields matched)
barb james (only 1 field matched)

SELECT 		
	studentId, 
	lastName, 
	firstName, 
	MATCH(lastName) AGAINST('james' IN BOOLEAN MODE) as relevance FROM students 
WHERE MATCH(lastName) AGAINST('james' IN BOOLEAN MODE) having relevance > 0.2
		
UNION DISTINCT
		
SELECT 		
	studentId, 
	lastName, 
	firstName, 
MATCH(firstName) AGAINST('john' IN BOOLEAN MODE) as relevance FROM students 
WHERE MATCH(firstName) AGAINST('john' IN BOOLEAN MODE) having relevance > 0.2
 
ORDER BY relevance DESC

Open in new window

ASKER CERTIFIED SOLUTION

racek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

racek

maybe you can do it this way:

SELECT studentId, lastName, firstName  
FROM  yourtable  
WHERE  lastName='roberts'+ FirstName='john'+ studentId =434 > 0
ORDER BY lastName='roberts'+ FirstName='john'+ studentId =434 DESC

Open in new window

farmingtonis

ASKER

Part of the reason I was using match was to be able to do partial searches. I tried both above and the first works I think but I'm not sure how to get out the "0" matches. The 2nd should take care of that problem, but I can't get the query to work by changing the "=" to like 'roberts%'
Thanks for the help this is way better than doing a bunch of unions!

racek

and what about

SELECT          
        studentId, 
        lastName, 
        firstName, 
        MATCH(lastName) AGAINST('james' IN BOOLEAN MODE)
      + MATCH(firstName) AGAINST('barb' IN BOOLEAN MODE)
      + studentId = 434 > 0 as relevance 
 
FROM students 
ORDER BY relevance DESC

Open in new window

farmingtonis

ASKER

I ended up adding a where clause with the same If's in the select statement with a > 0 so I only get back ones that match. Thanks for the help!

How to do a ranking/relevance search with both varchar(fulltext) &amp; int fields in one query

How to do a ranking/relevance search with both varchar(fulltext) & int fields in one query