Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Data Model Questions /

Complex fulltext search


Reply


3 Posts   2061 Views

Avatar
Fred C.

Community Member, 18 Posts

15 May 2009 at 9:26am

Edited: 16/05/2009 8:43am

I'm wondering if anyone has done any work extending fulltext search on a SS site. In exploring search contexts, I found that FulltextFilter.php (in sapphire/search/filters) has an out-and-out MySQL syntax error (a missing parenthesis in the MATCH function), so I'm suspecting that this is code that positively no one is exercising or testing. Not only that, but it would inject MATCH into the WHERE clause, which is not what I want.

My particular app would benefit from full-text searches across multiple objects that are related by has_one, and I'm starting to think that I need a denormalized table containing all the text that's to be searchable yielding a fulltext relevancy score. Something like this example:

SELECT ID, SourcePageID, MATCH (Title,Content,ContributedContent,WikiContent) AGAINST ('$search_terms') AS Relevance
FROM MyFullTextTable
HAVING Relevance > 0.5
ORDER BY Relevance DESC

Has anyone barked up this or a similar tree? Would love to hear about it!

### Update 15 May 2009 (California Time)

Seems I had some misconceptions about MATCH usage -- it is intended to be used in the WHERE clause. Here's a nice intro by Joe Stump: http://www.onlamp.com/pub/a/onlamp/2003/06/26/fulltext.html

Avatar
Ingo

Forum Moderator, 801 Posts

25 May 2009 at 9:29am

Thanks for pointing out the bug with FulltextFilter, I've fixed it with http://open.silverstripe.com/changeset/77672.

Avatar
Fred C.

Community Member, 18 Posts

27 May 2009 at 5:09am

Glad to help!

In the meantime, we've changed our approach and are integrating sphinxsearch (http://www.sphinxsearch.com/) rather than rely on MySQL's fulltext indexing.