scoring select results
От | Dave [Hawk-Systems] |
---|---|
Тема | scoring select results |
Дата | |
Msg-id | DBEIKNMKGOBGNDHAAKGNAEGACNAC.dave@hawk-systems.com обсуждение исходный текст |
Список | pgsql-general |
have a table title, description, keywords which I am searching (from PHP) using a keyword What I want to do is sort the results based on the number of hits nd scoring based on where the hit is. For example, a hit in keywords is worth 5, title is worth 3, description is worth 1.\ I currently have the following working select; SELECT *, ( (CASE WHEN (keywords ~* '.*keywordSearch.*') THEN 5 ELSE 0 END) + (CASE WHEN (title ~* '.*keywordSearch.*') THEN 3 ELSE 0 END) + (CASE WHEN (description ~* '.*keywordSearch.*') THEN 1 ELSE 0 END) ) AS score FROM catalog_table WHERE keywords ~* '.*keywordSearch.*' or title ~* '.*keywordSearch.*' or description ~* '.*keywordSearch.*' AND status='D' ORDER BY score DESC which works great, but the maximum hit is 5 even if a particular item has multiple hits in several different fields (ie: hit in keyword, title, and description results in score of 5 instead of score of 8) 1) Any idea on how to rework the query to total the score for all field hits (as in teh score of 8 we should se from above)? 2) What about multiple hits, for example, two keyword hits, two title hits, and two description hits totaling a score of 16? Thanks Dave
В списке pgsql-general по дате отправления: