For a forum (divided into several “rooms”), we have a structure consisting of two tables;
- forum_thread: holding all values about each thread (id, title, forum_id, created_at, first poster, latest poster, views, etc.)
- forum_post: posts for each thread (id, thread_id, poster, message_no, message, etc.)
We want a search function which is able to search "deep" into each thread. When a client makes a search, it loops through all posts and group these, and present the search result threadwise, with the relevance score calculated from the total number of posts in the particular threads. Search result should also be able to be presented sorted by date (latest post date is available from forum_thread table).
When presented to the client, the search result is shown with title and date pulled from the thread, also showing an excerpt of 30 words from the first post found in the thread containing search term(s). The excerpt should present search terms highlighted, with a few words of lead before the search term, ie. the search example below, where search is made on "European":
...joint rescue plan by the _European_ Union and the International Monetary Fund would be able to contain the crisis. Oil hit a three and a half month low, with US light...
In the presentation of the excerpt, the coding must take into consideration that the searched word could ultimately be the first and last word in the forum post, and also that any post could ultimately be shorter than 30 words long.