travislowdermilk.com

This is the final post in a series about creating a robust search U.I. for SharePoint.

We have successfully captured each search query submitted through our U.I. and stored them into a normalized schema in our database.

So the final question is: “How do we use this information to provide search suggestions to our users?”.

The first step starts with our queries.  Our schema has 3 tables:

Tag Table: This captures all the unique keywords a user might type into their search query e.g. “dress”, “code”, etc.
Context Table: Captures the date, time, and the user who performed the search.
Context_Tag: Combines keywords and context.

These three tables allow us create some nifty results.

For instance:

What are the top ten phrases? (Use this to make a tag cloud):

 DECLARE @tblContextTag TABLE(Row_Num int, ContextID int, Ordinal int, Tag nvarchar(255))
 DECLARE @tblContextPhrases TABLE (Phrase nvarchar(255))
 DECLARE @Tags int
 DECLARE @Tag n_varchar(255) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]
 DECLARE @Row_Num int
 DECLARE @ContextID int
 DECLARE @Ordinal int
 DECLARE @Phrase n_varchar(255) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]</pre>
INSERT INTO @tblContextTag (Row_Num, ContextID,  Ordinal, Tag)
 (SELECT ROW_NUMBER() OVER(ORDER BY ContextID, Ordinal) AS 'Row_Num', ContextID, Ordinal, Tag
 FROM Context_Tag A INNER JOIN
 Context ON A.ContextID = Context.ID INNER JOIN
 Tag ON A.TagID = Tag.ID
 GROUP BY ContextID, Ordinal, Tag, TagID)

 SET @Tags = 0
 SET @Row_Num = 1
 SET @ContextID = (SELECT TOP 1 ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num ORDER BY ContextID)
 WHILE @Tags <= (SELECT COUNT(*) FROM Context) - 1
 BEGIN
 SET @Ordinal = 0
 SET @Phrase = ''
 WHILE @Row_Num <= (SELECT TOP 1 Row_Num FROM @tblContextTag WHERE ContextID = @ContextID ORDER BY Row_Num DESC)
 BEGIN
 SET @Tag = (SELECT TOP 1 Tag FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Phrase = @Phrase + ' ' + @Tag
 SET @Ordinal = @Ordinal + 1
 SET @Row_Num = @Row_Num + 1
 END
 INSERT INTO @tblContextPhrases (Phrase) VALUES (LTRIM(@Phrase))
 SET @ContextID = (SELECT ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Tags = @Tags + 1
 END

 SELECT TOP 10 Phrase, COUNT(Phrase) AS Weight
 FROM @tblContextPhrases
 GROUP BY Phrase
 ORDER BY COUNT(Phrase) DESC, Phrase

Or, how about:

What are the top 20 suggestions for <specified network login> based on what this user has search for in the past?  Plus I want to include what is popular among other users’ searches.  And please filter by <specified first letter of the query>.

-- Add the parameters for the stored procedure here
 @value n_varchar(1), --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]
 @networkid n_varchar(10) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 -- Insert statements for procedure here
 DECLARE @tblContextTag TABLE(Row_Num int, ContextID int, Ordinal int, Tag nvarchar(255))
 DECLARE @tblContextPhrases TABLE (Phrase nvarchar(255))
 DECLARE @tblContextTOPPhrases TABLE (Phrase nvarchar(255))
 DECLARE @Tags int
 DECLARE @Tag n_varchar(255) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]
 DECLARE @Row_Num int
 DECLARE @ContextID int
 DECLARE @Ordinal int
 DECLARE @Phrase n_varchar(255) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]

 INSERT INTO @tblContextTag (Row_Num, ContextID,  Ordinal, Tag)
 (SELECT ROW_NUMBER() OVER(ORDER BY ContextID DESC, Ordinal) AS 'Row_Num', ContextID, Ordinal, Tag
 FROM Context_Tag A INNER JOIN
 Context ON A.ContextID = Context.ID INNER JOIN
 Tag ON A.TagID = Tag.ID
 WHERE NetworkID = @networkid AND
 Date_Time >= DATEADD(mm, -6, GETDATE()) AND Date_Time <= GETDATE()
 GROUP BY ContextID, Ordinal, Tag, TagID)

 SET @Tags = 0
 SET @Row_Num = 1
 SET @ContextID = (SELECT TOP 1 ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num ORDER BY ContextID)
 WHILE @Tags <= (SELECT COUNT(*) FROM Context WHERE NetworkID = @networkid AND Date_Time >= DATEADD(mm, -6, GETDATE()) AND Date_Time <= GETDATE()) - 1
 BEGIN
 SET @Ordinal = 0
 SET @Phrase = ''
 WHILE @Row_Num <= (SELECT TOP 1 Row_Num FROM @tblContextTag WHERE ContextID = @ContextID ORDER BY Row_Num DESC)
 BEGIN
 SET @Tag = (SELECT TOP 1 Tag FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Phrase = @Phrase + ' ' + @Tag
 SET @Ordinal = @Ordinal + 1
 SET @Row_Num = @Row_Num + 1
 END
 INSERT INTO @tblContextPhrases (Phrase) VALUES (LTRIM(@Phrase))
 SET @ContextID = (SELECT ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Tags = @Tags + 1
 END
 --Now get overall popular results
 SET @Tags = 0
 SET @Row_Num = 1
 SET @ContextID = 0
 SET @Tags = 0
 DELETE @tblContextTag

 INSERT INTO @tblContextTag (Row_Num, ContextID,  Ordinal, Tag)
 (SELECT ROW_NUMBER() OVER(ORDER BY ContextID, Ordinal) AS 'Row_Num', ContextID, Ordinal, Tag
 FROM Context_Tag A INNER JOIN
 Context ON A.ContextID = Context.ID INNER JOIN
 Tag ON A.TagID = Tag.ID
 WHERE Date_Time >= DATEADD(mm, -6, GETDATE()) AND Date_Time <= GETDATE()
 GROUP BY ContextID, Ordinal, Tag, TagID)

 SET @Tags = 0
 SET @Row_Num = 1
 SET @ContextID = (SELECT TOP 1 ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num ORDER BY ContextID)
 WHILE @Tags <= (SELECT COUNT(*) FROM Context WHERE Date_Time >= DATEADD(mm, -6, GETDATE()) AND Date_Time <= GETDATE()) - 1
 BEGIN
 SET @Ordinal = 0
 SET @Phrase = ''
 WHILE @Row_Num <= (SELECT TOP 1 Row_Num FROM @tblContextTag WHERE ContextID = @ContextID ORDER BY Row_Num DESC)
 BEGIN
 SET @Tag = (SELECT TOP 1 Tag FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Phrase = @Phrase + ' ' + @Tag
 SET @Ordinal = @Ordinal + 1
 SET @Row_Num = @Row_Num + 1
 END
 INSERT INTO @tblContextTOPPhrases (Phrase) VALUES (LTRIM(@Phrase))
 SET @ContextID = (SELECT ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Tags = @Tags + 1
 END

 --Insert overall popular results
 INSERT INTO @tblContextPhrases (Phrase)
 SELECT TOP 20 Phrase FROM @tblContextTOPPhrases
 GROUP BY Phrase HAVING Count(Phrase) > 5
 ORDER BY COUNT(Phrase) DESC, Phrase

 SELECT DISTINCT Phrase AS value
 FROM @tblContextPhrases
 WHERE Phrase LIKE @value + '%'

END

The above stored procedure will give you the result set you need in order to inject meaningful suggestions into your U.I. using the jQuery autocomplete function.  Just call the stored procedure from the web service that is connected to your jquery plugin.  Here is some more info on how the jquery autocomplete code functions.  Also, if you’re wondering how you might get the user’s login from SharePoint to pass to the stored procedure, check this post out.

And the final result is a rich U.I. with personalized search suggestions. Right in SharePoint!