かけがえのない愛

Once I was working with a custom search webpart issue in which search was implemented by using FullTextSqlQuery method, but it was not returning any results whenever we use contains predicate to filter the date time type columns.

There was a custom managed property of type Date Time to filter in the custom webpart. Since it was not returning any results in the custom webpart, we added that managed property in OOB advance search web part to include the custom managed property in the pick property section. After that, selected the date time managed property and executed the search by giving the same value that we given in the custom search webpart and it returned the correct results. We also checked the ULS logs to check the query used to execute the search with the date time type managed property in the filtering condition.

We saw that there is a difference in the construction of query between the custom webpart and the query executed in OOB (out-of-the-box).

SharePoint has only 3 filtering conditions with any managed property of type Date and Time. So, we must need to construct a query in a way that SharePoint can internally understand. If you pass ‘07/08/2009’ as your input date value SharePoint will convert that input date value in the format of ‘2009/07/08 18:30:00’ internally. You can see the query in the ULS logs once you execute a search in UI.

I have captured the SQL syntaxes for all the three filtering conditions. (eg: 07/08/2009 as our input date value)

Equal

Full Text Query: SELECT WorkId, Rank, Title, Author, Size, Path, Description, Write, SiteName, CollapsingStatus, HitHighlightedSummary, HitHighlightedProperties, ContentClass, IsDocument, PictureThumbnailURL  from scope() where ("scope" = 'All Sites') And ((Created >= '2009/07/07 18:30:00' and Created < '2009/07/08 18:30:00'))

Earlier than

Full Text Query: SELECT WorkId, Rank, Title, Author, Size, Path, Description, Write, SiteName, CollapsingStatus, HitHighlightedSummary, HitHighlightedProperties, ContentClass, IsDocument, PictureThumbnailURL  from scope() where ("scope" = 'All Sites') And (Created < '2009/07/08 18:30:00')

Later than

Full Text Query: SELECT WorkId, Rank, Title, Author, Size, Path, Description, Write, SiteName, CollapsingStatus, HitHighlightedSummary, HitHighlightedProperties, ContentClass, IsDocument, PictureThumbnailURL  from scope() where ("scope" = 'All Sites') And (Created > '2009/07/08 18:30:00')

You can refer the above syntaxes for building your SQL query for filtering date type managed properties.

A while back, I posted a how to on using the KeywordQuery class and it seemed to get pretty good response, so I figured I would post a follow up on how to use the FullTextSqlQuery class today.  It's actually pretty similar to using the KeywordQuery class but of course the syntax is different.  I am not going to give you a full run down of all the syntax, but I will provide enough to get you going. 

There are a number of reason to use the FullTextSqlQuery class.  Using a SQL Syntax query unlocks the full power of Enterprise Search that really isn't there out of the box.  With SQL Syntax, you can do wildcard searches, and make use of the powerful CONTAINS and FREETEXT predicates.  With the CONTAINS predicate, you can use the FORMSOF term to do inflectional (i.e.: go matches going, gone, went, etc.) or thesaurus searches.  Again, check the SDK as there is plenty of documentation about how to use all sorts of predicates.  Before, we look at all the code lets look at a couple of things on the query.

SELECT Title, Path, Color, Size, Quantity, Rank, Description, Size FROM SCOPE() WHERE "scope" = 'My Scope' AND CONTAINS(Description, 'ant*')

In a lot of ways this looks like a T-SQL query.  Enterprise Search queries however always query from SCOPE().  To specify the actual scope (or scopes) you want to search on you use the where clause.  I have always found the syntax weird here because the word scope has to be in quotes and what you are comparing it to always has to be in single quotes.  In this case I am using the CONTAINS predicate to do a wildcard search.  It should return anything that has a word starting with ant in the description column (i.e.: ant, ants, anthony, antler, etc.).

Here is the complete code example.