Re: scoring/sorting db search results based on score

I use mysql, so this was for mysql - LIKE is case insesitive and returns 0 for false and 1 for true. Dean E. Weimer wrote:

Try SELECT ...., (((keywords LIKE '%$search%') * 5) + ((title LIKE '%$search%') * 3) + (description LIKE '%$search%')) score FROM ..... ORDER BY score DESC

PostgreSQL cannot type cast the Boolean type so you have to use a case statement, also changing like to ilike will get results regardless of case. SELECT ...., ((CASE WHEN (keywords ILIKE '%$search%') THEN 5 ELSE 0 END) + (CASE WHEN (title ILIKE '%$search%') THEN 3 ELSE 0 END) + (CASE WHEN (description ILIKE '%$search%') THEN 1 ELSE 0 END)) AS score FROM .... ORDER BY score DESC