Jump to:

22976 Posts in 11702 Topics by 2826 members

General Questions

SilverStripe Forums » General Questions » SIlverstripe 3 search on ms sql server

General questions about getting started with SilverStripe that don't fit in any of the categories above.

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

Page: 1
Go to End
Author Topic: 224 Views
  • PLZI
    Avatar
    Community Member
    1 Post

    SIlverstripe 3 search on ms sql server Link to this post

    First of all: SQL 2008R2, fulltext enabled. Search has been enabled through _config.php.

    Problem is that searches return no results at all. Tracing the SQL shows T-SQL in form of:

    SELECT DISTINCT "File"."ID", 'File' AS "Source", Rank AS "Relevance" FROM "File" INNER JOIN CONTAINSTABLE("File", (*), 'termbeingsearched') AS "ft" ON "File"."ID"="ft"."KEY" WHERE ("File"."ShowInSearch"!=0) AND ("File"."ClassName" IN ('SiteTree', 'ErrorPage', 'RedirectorPage', 'VirtualPage', 'File')) UNION SELECT DISTINCT "SiteTree"."ID", 'SiteTree' AS "Source", Rank AS "Relevance" FROM "SiteTree" INNER JOIN CONTAINSTABLE("SiteTree", (*), 'termbeingsearched') AS "ft" ON "SiteTree"."ID"="ft"."KEY" WHERE ("SiteTree"."ShowInSearch"!=0) AND ("SiteTree"."ClassName" IN ('SiteTree', 'ErrorPage', 'RedirectorPage', 'VirtualPage', 'File')) ORDER BY "Relevance" DESC

    The query when run manually on SQL Server returns no results.

    Now, if I check the latter part of inner join, it seems that the search function is only seaching for classes in sitetree, errorpage, redirectorpage, virtualpage and file.

    However - the real content pages in database have class "Page".

    Now, if I modify the query slighty:

    SELECT DISTINCT "File"."ID", 'File' AS "Source", Rank AS "Relevance" FROM "File" INNER JOIN CONTAINSTABLE("File", (*), 'termbeingsearched') AS "ft" ON "File"."ID"="ft"."KEY" WHERE ("File"."ShowInSearch"!=0) AND ("File"."ClassName" IN ('SiteTree', 'ErrorPage', 'RedirectorPage', 'VirtualPage', 'File')) UNION SELECT DISTINCT "SiteTree"."ID", 'SiteTree' AS "Source", Rank AS "Relevance" FROM "SiteTree" INNER JOIN CONTAINSTABLE("SiteTree", (*), 'termbeingsearched') AS "ft" ON "SiteTree"."ID"="ft"."KEY" WHERE ("SiteTree"."ShowInSearch"!=0) AND ("SiteTree"."ClassName" IN ('SiteTree', 'ErrorPage', 'RedirectorPage', 'VirtualPage', 'File', 'Page')) ORDER BY "Relevance" DESC

    (added the "Page" for the IN clause)

    ...lo and behold, query returns nice set of results as expected from sitetree with relevancy and all.

    So my question is - why does the search function omit the Page class, since this seems insane - I'm not really interested in virtual pages, error pages and so on in search results, only the content?

    224 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.