Thursday, July 18, 2013

Limiting search results by exact time in SharePoint 2013–and how to do FQL

Often you might find the need to limit search results between two dates, or even more exact, between two exact time intervals. The issue is that property queries written in KQL will disregard the time portion of your query, limiting to full day results only (See this old post for an explanation).

FAST Search for SharePoint allowed queries with the time portion in Sharepoint 2010 by using FQL, and fortunately you can do this in 2013 and Office 365 as well.

Doing FQL in 2013 in theory involves setting a parameter either on your REST queries or on the KeywordQuery object telling it your query is written in FQL and not KQL. The reality is a bit more complex involving a custom result source, and to tell you the truth. I have yet to be able to get FQL to work using REST this way.

How then? you might ask. Use use the RefinementFilters property instead! This property actually uses FQL which is what we need and is our “Get out of jail free card”. And all though KQL has more of the FQL operators in 2013, FQL still has some tricks up it's sleeve. Worth taking a look at for sure.

To limit on dates you will use the FQL range operator. The default behavior is to use “greater than” and “equal and less than” if not specified in the query.

The granularity of date/time queries are down to 7 decimals, as long as the crawler has been able to set this level of granularity during indexing.
Below are some sample REST queries limiting the results based on time:
// Return all results greater than or equal to last modified time of 2013-07-17T12:35:57
/_api/search/query?querytext='*'&refinementfilters='write:range(2013-07-17T12:35:57.0000000Z,max,from="ge")'

// Return all results greater than last modified time of 2013-07-17T12:35:57
/_api/search/query?querytext='*'&refinementfilters='write:range(2013-07-17T12:35:57.0000000Z,max,from="gt")'

// Return all results less than or equal to last modified time of 2013-07-17T12:35:57
/_api/search/query?querytext='*'&refinementfilters='write:range(min,2013-07-17T12:35:57.0000000Z,to="le")'

// Return all results less than last modified time of 2013-07-17T12:35:57
/_api/search/query?querytext='*'&refinementfilters='write:range(min,2013-07-17T12:35:57.0000000Z,to="lt")'

// Return all results between and including 2013-07-17T12:35:57.0000000Z and 2013-07-17T13:35:57.0000000Z
/_api/search/query?querytext='*'&refinementfilters='write:range(2013-07-17T12:35:57.0000000Z,2013-07-17T13:35:57.0000000Z, from="ge",to="le")'


It’s also possible to filter on other properties like Author, with starting-with, and ends-with:

/_api/search/query?querytext='*'&refinementfilters='author:starts-with("Mikael")
/_api/search/query?querytext='*'&refinementfilters='author:ends-with("Svenson")

If you want to boost your query using FQL, you may write something like this:

// Items with the term "test" will get 100 extra boost points
// Items with the terms "test" and "developer" will get 200 extra boost points
/_api/search/query?querytext='*'&refinementfilters='xrank(xrank("*","test",cb=100),"developers",cb=100)'

Pay attention to the use of “*” in the matching part. This means it will evaluate all results based on the initial query value, and boost accordingly. If you enter a term instead of “*” in the refinementfilter, you will also limit the actual results.

Want more FQL samples? Take a look at my book. Working with FAST Search Server 2010 for SharePoint.

14 comments:

  1. Hi,
    We have a requirement to enable search with each word in the phrase/query. e.g. if user search with awesome environment, use should get results with all possible 3 combination i.e. awesome environment, awesome, environment
    currently its giving results of only "awesome environment"

    ReplyDelete
    Replies
    1. Unfortunately there is no setting on the search result web part to switch from ALL of the terms to ANY. One solution is to add ANY() around the search terms, or put OR between them.

      ANY(awesome environment)
      awesome OR environment

      If dealing with the default search page in SP2013 you can do this by overriding what happens on the search click event, or override the code being executed. But that's another post :)

      -m

      Delete
  2. Can You please tell me how can I make time filtering work in a Content Search Webpart?

    ReplyDelete
  3. Have you been able to use query variables like {User} as a token to filter items like 'Authored' for a document or 'AssignedTo' for a task?

    ReplyDelete
    Replies
    1. Hi,
      I'm using

      ModifiedBy:{User.Name}

      which works, but as it used the display name you can get too many results if two people are named exactly the same.

      If you can get the login as a field on your items you can use

      {User.Login}

      which is unique.

      Hope this helps,
      Mikael

      Delete
  4. Is there a way to specify multiple refinement filters? I've tried the following (and a few variations) but it throws an error:

    &refinementfilters='(RefinableDate01:range(min,2013-10-31T00:00:00.0000000Z,to="le"), RefinableDate00:range(2013-10-31T00:00:00.0000000Z,max,from="ge"))'

    ReplyDelete
    Replies
    1. Hi,
      I executed the following without any errors:

      &refiners='write:range(2013-07-17T12:35:57.0000000Z,max,from="ge"),Created:range(min,2013-07-17T12:35:57.0000000Z,to="le")'

      Delete
  5. Hi Mikael,

    Thanks for the article. Any idea what is the use of querytemplate in the search rest api in SP 2013. I am unable to find any article for the same in the net.

    ReplyDelete
    Replies
    1. You can do something like: http://server/_api/search/query?querytext='sharepoint'&querytemplate='{searchterms} author:johndoe'

      Reference: http://msdn.microsoft.com/en-us/library/office/jj163876(v=office.15).aspx

      Basically it's a query transformation. I haven't tried it much but I believe you can use some of the query variables available in this template.

      Delete
  6. Is it possible to use FQL refiners in a Content Search Web Part? We have a number of CSWP's that show list items on our front page and are having difficulties getting them to expire at the correct time due to KQL disregarding the time portion of the DateTime field. It seems to me that using FQL refiners would be the best way around this but I can't get them to work in a CSWP. Any help would be greatly appreciated.

    ReplyDelete
    Replies
    1. You should be able to set them as refinement filters in the .webpart definition inside the JSON property I believe.

      Delete
  7. Hi Mikael,

    Thanks for the great article! Do you know of any way that I can use to apply a date filter to an OOB search result web part without passing it via the URL? An FQL result source came to mind but those seem to require enableFQL and thus can only be called via code. Any ideas?

    ReplyDelete
    Replies
    1. Custom control template or script on the page which sets the FQL date filter will work.

      Delete