Jump to:

3460 Posts in 1064 Topics by 739 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Complex fulltext search

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 2041 Views
  • Fred C.
    Avatar
    Community Member
    18 Posts

    Complex fulltext search Link to this post

    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

  • Ingo
    Avatar
    Forum Moderator
    801 Posts

    Re: Complex fulltext search Link to this post

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

  • Fred C.
    Avatar
    Community Member
    18 Posts

    Re: Complex fulltext search Link to this post

    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.

    2041 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.