Recently I spent some time working on a custom search results web part and I wanted to be able to use all of the same query string parameters as the out-of-the-box core search results web part and return the same results. The solution for me was to use the SharePoint Search API FullTextSqlQuery class and build the query string out of the URL parameters normally passed to the core search results web part. Using this method allowed me to seamlessly integrate the custom web part into the site by simply dropping it onto the existing search results page. The Search API was relatively new to me, so there may be easier ways of loading these parameters into the FullTextSqlQuery object that I’m not aware of. If you’re interested in reading about the solution that worked for me though, read on.
Saving the query string parameters
The first thing I had to do was read in the query strings SharePoint uses in the URL. The three most important ones that I found were:
- (k) : the search terms
- (s) : the selected scope
- (r) : the refinement filters
Another query string worth noting is “start1” which is used for paging. You can use this parameter to set the starting point of the FullTextSqlQuery and then use the out-of-the-box results paging web part to page your custom results. My results were custom grouped which resulted in a different number of total pages reported, so unfortunately I had to develop a custom paging solution and couldn’t use this.
I saved these query parameters to variables with code like this:
if (HttpContext.Current.Request.QueryString["r"] != null)
Refiners = HttpContext.Current.Request.QueryString["r"];
if (HttpContext.Current.Request.QueryString["s"] != null)
Scope = HttpContext.Current.Request.QueryString["s"];
if (HttpContext.Current.Request.QueryString["k"] != null)
{
SearchText = HttpContext.Current.Request.QueryString["k"]);
BuildQuery();
}
Note that I don’t even proceed unless there are search terms specified. I want to make sure there is something valid there to work with. BuildQuery is the function I’ve created to put together the SQL query from these values.
Building the query
In my BuildQuery function, I started with a string that looks like this:
string query = “SELECT Title FROM SCOPE() WHERE “;
This example only returns the column “Title”, but more can be added afterwards separated by commas. Custom columns should be configured as managed properties in central administration first.
Adding the Search Terms
Next I added the search terms with code like this:
//Add search terms
SearchText = SearchText.Replace(“‘”, “\”");
string pattern = “\\s*(\”[^\"]+\”|[^ ]+)”;
MatchCollection matches = Regex.Matches(SearchText, pattern);
for (int i = 0; i < matches.Count; i++)
{
string value = matches[i].Value;
if (i != 0) query = query + ” AND “;
query = query + “CONTAINS(*, ‘” + value + “‘)”;
}
Here I’m starting by replacing any apostrophes with quotation marks because both can be used to group terms together, but the FullTextSqlQuery will only work with quotes. Then I use a regular expression pattern to pull out all of the individual search terms into a collection of matches. This pattern will match single terms or terms enclosed by quotes.
The syntax for searching any column for a value is “CONTAINS(*, ‘value’)”, so for each term I add this statement to the query with “AND” before every addition except for the first. This will bring back results that have at least one column containing each term. These rules brought back for me the closest matches to the basic out-of-the-box search results.
Adding the Scope
This one is pretty simple. As long as a scope was defined, I added it to the query like this:
//Add scope
if (Scope != string.Empty) query = query + ” AND \”scope\” = ‘” + Scope + “‘”;
Add the Refinement Terms
With the addition of the refinement panel, SharePoint allows the user to filter their results by sets of managed properties. I accounted for these in my query with code like this:
if (Refiners != string.Empty)
{
Refiners = Refiners.Replace(‘”‘, ‘\”);
pattern = “[\\w]+[<>]?=’+[\\w ]+’”;
matches = Regex.Matches(Refiners, pattern);
for (int i = 0; i < matches.Count; i++)
{
string value = matches[i].Value;
if(i!=0)
{
if (value.Split(‘=’)[0] == matches[i - 1].Value.Split(‘=’)[0])
query = query + ” OR “;
else query = query + ” AND “;
}
else query = query + ” AND “;
if (value.Contains(“>=”) || value.Contains(“<=”))
{
string date = value.Split(‘=’)[1].Replace(“‘”, ” “).Trim();
DateTime dt = Convert.ToDateTime(date);
value = matches[i].Value.Replace(date, dt.ToString(“yyyy/MM/dd”));
}
query = query + value;
}
}
There is a little more going on here because I had to account for everything SharePoint might add to the query string that wouldn’t work with the FullTextSqlQuery. First I replaced quotes with apostrophes because the syntax requires them, then I used the regular expression to pull out everything in the format matching “column=’value’”. Each time a filter is clicked in the panel, SharePoint will add a refinement term like this to the query string. The filters can be added directly to the query in this format.
The refinement panel can also group different values together into one filter however. For example, you might click a filter called “JPG” and that could include both file types ‘jpg’ and ‘jpeg’. There are a few filters defined by default this way, and in the query string it will add an entry for each possible value. So to account for this I checked the previous column name to see if it was the same, if so, then added OR to the query instead of AND. Ignoring this would yield zero results.
Another thing I had to watch out for was date filtering. By default the refinement panel can filter on date ranges, but the problem is that it formats the dates in a way that is incompatible with a SQL query. So I next searched for filter terms that contained > or < symbols and converted the dates into a compatable format (yyyy/MM/dd). The only default filters that used these symbols were dates, so I made that assumption in my code. If custom filters are used to compare ranges of values other than dates then this would need to be modified.
Completed
That’s it! I assigned this query string to the FullTextSqlQuery.QueryText value and let it run. In all of my basic tests I got the same results back as the core search results web part, even when using the refinement panel. The only difference was that the core search results will handle special characters like “!” or “[“ which will break a FullTextSqlQuery, but they could probably be escaped or encoded to prevent this. I liked this solution because I could provide someone with a predefined set of results by just giving them a customized URL to the search results page. I also didn’t have to write any custom search input fields. All of that was handled by SharePoint.
