Re: [PHP] 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] scoring/sorting db search results based on score
I don't know how postgres server would optimize the query, but in teory there should be only a slight overhead as the (I)LIKE results should be cached. How much takes to order by depends on the number of rows returned. Dave [Hawk-Systems] wrote: Appreciate the responses... We are talking a db of under 10k records where the search would take place on. what are the ramifications of this as a load on the postgres server? Wouldn't want to have a great search query that grinds the server to a halt everytime someone searches regardless of the accuracy of the end result :) Thanks Dave -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] scoring/sorting db search results based on score
looking for code snippets or links to examples of the following; - Have a database with multiple fields that will be searched against (happens to be PostgreSQL in this instance, but we can migrate any MySQL based examples/code) - We wish to score search results - ie: a match in keywords is worth 5 points, title worth 3, and description worth 1, perhaps even so far as multiple matches producing multiples of the point value(though that can be a later consideration) - Once we get the results, we would want to display in the order of the scoring, most points first etc... Obviously there are convoluted ways to accomplish, but I am looking to maximize the database performance, limit the number of recursive searches, and use the database/PHP each handle their portion of the search/score/ranking based on their strengths and use of system resources. appreciate any feedback Dave -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] scoring/sorting db search results based on score
Try SELECT , (((keywords LIKE '%$search%') * 5) + ((title LIKE '%$search%') * 3) + (description LIKE '%$search%')) score FROM . ORDER BY score DESC Dave [Hawk-Systems] wrote: looking for code snippets or links to examples of the following; - Have a database with multiple fields that will be searched against (happens to be PostgreSQL in this instance, but we can migrate any MySQL based examples/code) - We wish to score search results - ie: a match in keywords is worth 5 points, title worth 3, and description worth 1, perhaps even so far as multiple matches producing multiples of the point value(though that can be a later consideration) - Once we get the results, we would want to display in the order of the scoring, most points first etc... Obviously there are convoluted ways to accomplish, but I am looking to maximize the database performance, limit the number of recursive searches, and use the database/PHP each handle their portion of the search/score/ranking based on their strengths and use of system resources. appreciate any feedback Dave -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] scoring/sorting db search results based on score
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 Dave [Hawk-Systems] wrote: looking for code snippets or links to examples of the following; - Have a database with multiple fields that will be searched against (happens to be PostgreSQL in this instance, but we can migrate any MySQL based examples/code) - We wish to score search results - ie: a match in keywords is worth 5 points, title worth 3, and description worth 1, perhaps even so far as multiple matches producing multiples of the point value(though that can be a later consideration) - Once we get the results, we would want to display in the order of the scoring, most points first etc... Obviously there are convoluted ways to accomplish, but I am looking to maximize the database performance, limit the number of recursive searches, and use the database/PHP each handle their portion of the search/score/ranking based on their strengths and use of system resources. appreciate any feedback Dave -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Thanks, Dean E. Weimer http://www.dwiemer.org/ [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] scoring/sorting db search results based on score
Appreciate the responses... 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 We are talking a db of under 10k records where the search would take place on. what are the ramifications of this as a load on the postgres server? Wouldn't want to have a great search query that grinds the server to a halt everytime someone searches regardless of the accuracy of the end result :) Thanks Dave -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
