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.

General Questions /

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

SIlverstripe 3 search on ms sql server


Reply


2 Posts   482 Views

Avatar
PLZI

Community Member, 1 Post

5 July 2013 at 12:03pm

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?

Avatar
Xander75

Community Member, 3 Posts

6 August 2014 at 6:56am

Hi,

I am having difficulty linking the search to a SQL Server backend, here you talk about changing the query to have the Page class. Can I ask, where did you add this? I'm new to this, but so far have only become stumped at adding the site search! I am unsure where to add the Page class and to which file to get the search to return results.

Any help would be appreciated.