I firstly tried to create my query using the FullTextSqlQuery class which worked just as I wanted - this was awesome I could build my query using dropdown lists using a custom list as the datasource for properties like Role, Department etc. If you're interested in doing this see below for the code:
private const string SQLQUERYFORMATSTRING = "SELECT {0} FROM SCOPE() WHERE \"scope\"='{1}' {2} ORDER BY Rank DESC";
private const string RESULTFIELDS = "AccountName, PreferredName, JobTitle, Department, OfficeNumber, AboutMe, PictureURL, WorkEmail";
private const string SEARCHSCOPE = "People";
using (SPSite site = new SPSite(this.Page.Request.Url.ToString()))
{
// create a new FullTextSqlQuery class - use property intializers to set query
FullTextSqlQuery myQuery = new FullTextSqlQuery(site);
myQuery.QueryText = String.Format(SQLQUERYFORMATSTRING, RESULTFIELDS, SEARCHSCOPE, additionalWhereClause);
myQuery.ResultTypes = ResultType.RelevantResults;
myQuery.TrimDuplicates = true;
myQuery.EnableStemming = true;
// execute the query and load the results into a datatable
ResultTableCollection queryResults = myQuery.Execute();
ResultTable queryResultsTable = queryResults[ResultType.RelevantResults];
DataTable queryDataTable = new DataTable();
queryDataTable.Load(queryResultsTable, LoadOption.OverwriteChanges);
dgListResults.DataSource = queryDataTable;
dgListResults.DataBind();
}
Now the problem was how do I render my results? Obviously I could simply fix my Datagrid(dgListResults) to show a nicely formatted result. However I didn't want my results and the search to be on the same page.
I was told I had to use the OOB core results webpart and this was the big problem I now faced and after some digging I ran into Tom Clarkson's blog, and he describes using a custom advanced search box as a basis to pass the search query onto the core results webpart.
After further digging I found that I could basically build my fulltextsqlquery using this approach. I simply added properties as hidden fields and use the value of input fields as the criteria to search on. I could even run these hidden values on the server, as long as they had the correct IDs ie. (ASB_PS_plb_
This was very very helpful I could simply point my dropdown list give it the correct ID and fill it with values from a list in SharePoint. The code looked like this:
private void BindDropDowns()
{
SPSite site = SPContext.Current.Site;
using (SPWeb web = site.RootWeb)
{
//BindDropDownList
BindDropDownList(web, ASB_PS_pvtb_2, dsRole, "Roles");
BindDropDownList(web, ASB_PS_pvtb_3, dsDepartment, "Business Units");
}
}
private void BindDropDownList(SPWeb web, DropDownList dropDownList, SPDataSource spDataSource, string listName)
{
spDataSource.List = web.Lists[listName];
dropDownList.DataSource = spDataSource;
dropDownList.DataBind();
}
<sharepoint:spdatasource runat="server" id="dsRole"></sharepoint:spdatasource>
<sharepoint:spdatasource runat="server" id="dsDepartment"></sharepoint:spdatasource>
<tr>
<td style="width:25%">
First name / preferred name<br />
<input name="ASB_TextDT_Props" type="hidden" value="AccountName#;#FirstName#;#PreferredName#;#LastName#;#JobTitle#;#Department"/>
<input id="ASB_PS_lolb_0" type="hidden" value="And" runat="server"/>
<input id="txtFirstName" type="text" runat="server" />
</td>
<td>
Last Name<br />
<input id="ASB_PS_plb_1" type="hidden" value="LastName" runat="server"/>
<input id="ASB_PS_olb_1" type="hidden" value="Contains" runat="server"/>
<input type="text" id="ASB_PS_pvtb_1" runat="server"/>
</td>
</tr>
<tr>
<td style="width:25%">
Role <br />
<input id="ASB_PS_plb_2" type="hidden" value="JobTitle" runat="server" />
<input id="ASB_PS_olb_2" type="hidden" value="Equals" runat="server" />
<asp:DropDownList id="ASB_PS_pvtb_2" runat="server" datatextfield="Title" datavaluefield="Title" AppendDataBoundItems="true">
<asp:ListItem Value="" Text=""></asp:ListItem>
</asp:DropDownList>
</td>
<td>
Business Unit / Group Function<br />
<input id="ASB_PS_plb_3" type="hidden" value="Department" runat="server" />
<input id="ASB_PS_olb_3" type="hidden" value="Equals" runat="server" />
<asp:DropDownList id="ASB_PS_pvtb_3" runat="server" datatextfield="Title" datavaluefield="Title" AppendDataBoundItems="true">
<asp:ListItem Value="" Text=""></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td><asp:Button runat="server" text="Search" id="btnPerformSearch"></asp:Button>
</td>
</tr>
Now the only thing left to do is add a postbackUrl to the submit button:
this.btnPerformSearch.PostBackUrl = SPContext.Current.Web.Url + SEARCHRESULTSPAGE;
Just a thing to note ASB_PS_lolb_0 is a one off property value. If you look at the advanced search box it only ever uses And or Or not both, and not between properties :( So you cannot have different property operators between each property, a drawback which unfortunately I needed to try and get around.
After several misguided attempts I ran into the following hidden property: ASB_ResType_Query - which is basically the Result Type query. I looked at this using the standard OOB and this is what it sets it to either one of these values from the array:
arrResTypeQuery[1] = 'FileExtension=\u0027htm\u0027 Or FileExtension=\u0027html\u0027 Or FileExtension=\u0027asp\u0027 Or FileExtension=\u0027aspx\u0027 Or FileExtension=\u0027cgi\u0027 Or FileExtension=\u0027php\u0027';arrResTypeQuery[2] = 'FileExtension=\u0027doc\u0027 Or FileExtension=\u0027docx\u0027 Or FileExtension=\u0027dot\u0027';arrResTypeQuery[3] = 'FileExtension=\u0027xls\u0027 Or FileExtension=\u0027xlsx\u0027 Or FileExtension=\u0027xlt\u0027';arrResTypeQuery[4] = 'FileExtension=\u0027ppt\u0027 Or FileExtension=\u0027pps\u0027 Or FileExtension=\u0027pot\u0027';arrResTypeQuery[5] = 'FileExtension=\u0027pdf\u0027';
So it would seem if you used ASB_ResType_Query to pass on your query it would then append it to the query used specified with the properties. So why not just pass in the whole fulltextsqlquery onto this property as a hidden value? Only problem with that is it doesn't get appended and a fulltextsqlquery isn't run unless there is at least one property search (ASB_PS_plb_
Now knowing that I could simply pass on my fulltextsqlquery on to the ASB_ResType_Query and set the scope and do the search - we ended up just building the query using javascript and also added a property for the page to find the scopeidnumber for the "People" search scope:
protected int PeopleScopeId
{
get
{
//Set Scope Hidden value field to search on people scope
SearchContext context = SearchContext.GetContext(SPContext.Current.Site);
Scopes scopeMgr = new Scopes(context);
Scope scope = scopeMgr.GetSharedScope("People");
return scope.ID;
}
}
protected override void OnLoad(EventArgs e)
{
if (!Page.IsPostBack)
{
BindDropDowns();
}
this.btnPerformSearch.PostBackUrl = SPContext.Current.Web.Url + SEARCHRESULTSPAGE;
this.btnPerformSearch.OnClientClick = "BuildQuery()";
base.OnLoad(e);
}
<script type="text/javascript">
var firstNameQuery = "";
function SetFirstNameQuery()
{
var txtFirstName = document.getElementById("<%=txtFirstName.ClientID %>");
if(txtFirstName)
{
if(txtFirstName.value != "")
{
firstNameQuery = "FirstName LIKE '%" + txtFirstName.value + "%' OR PreferredName LIKE '%" + txtFirstName.value + "%'";
}
}
}
function BuildQuery()
{
//Create First Name/ Preferred Name Query
SetFirstNameQuery();
if(firstNameQuery != "")
{
finalQuery = firstNameQuery;
}
element = document.getElementById("ASB_ResType_Query");
if(element)
{
element.value = finalQuery;
}
//alert("Query is: " + element.value)
}
</script>
<!--Additional Result Type Query - additional search conditions --><input name="ASB_ResType_Query" id="ASB_ResType_Query" type="hidden" /><!--Search on People Scope Id ---><input name="ASB_SS_scb_0_<%=PeopleScopeId%>" type="hidden" />
Well there we have it and here is a screenshot of it all coming together bar the design:
Obviously this approach could be incorporated into a custom advanced search box as well without having to mess around with your own custom xsl and results rendering and can simply point it to the OOB search results page or just a page with the Core Results Webpart or the People Core Results webpart.
