Portal Engine Questions on portal engine and web parts.
Version 6.x > Portal Engine > Where clause for smart search filter and categories View modes: 
User avatar
Member
Member
stephen.turley-terradon - 6/1/2012 1:26:03 PM
   
Where clause for smart search filter and categories
I would like to filter my search results by category. I've added a smart search dialog that uses the following query:

SELECT 'CategoryName' , CategoryName, CategoryName FROM CMS_Category WHERE ##WHERE## ORDER BY ##ORDERBY##

Users can select the category they wish to filter by selecting the appropriate radio button.

I'm having trouble writing the correct where clause for the search results.

User avatar
Kentico Support
Kentico Support
kentico_jurajo - 6/1/2012 3:31:12 PM
   
RE:Where clause for smart search filter and categories
Hi,

You may need to create something like described here.

There are three tables - database tables.

So, the basic where condition will look like:

DocumentID IN (SELECT DocumentID FROM CMS_DocumentCategory WHERE CategoryID =<value from the filter>)

Best regards,
Juraj Ondrus

User avatar
Member
Member
stephen.turley-terradon - 6/4/2012 7:47:51 AM
   
RE:Where clause for smart search filter and categories
I'm kind of lost on how to get the value from the filter. I tried to use a macro <%CategoryID%> but I'm getting syntax errrors.

User avatar
Kentico Developer
Kentico Developer
kentico_ivanat - 6/4/2012 8:04:13 AM
   
RE:Where clause for smart search filter and categories
Hi,

searching and filtering in categories is a little more complex and unfortunately not supported in the current version by default.

There is a knowledge base article which describes how to implement search in categories.

Category search

Best regards,
Ivana Tomanickova

User avatar
Member
Member
stephen.turley-terradon - 6/4/2012 11:33:31 AM
   
RE:Where clause for smart search filter and categories
The linked article does not specify where to write this code or what namespace the required class is located. Also I'm using Kentico 6 and the class used in this example has been deprecated. Do you have anyway to implement smart search filtering within Kentico version 6?

User avatar
Kentico Support
Kentico Support
kentico_jurajo - 6/5/2012 10:55:52 AM
   
RE:Where clause for smart search filter and categories
Hi,

Have you checked also the "See also" links at the bottom? It is mentioned there where to write the code. For version 6 you can find the same documentation here.

Best regards,
Juraj Ondrus

User avatar
Certified Developer 8
Certified Developer 8
Jiveabillion - 6/7/2012 12:31:13 PM
   
RE:Where clause for smart search filter and categories
Hi Stephen. Jeff told me you needed help, so I figured this out for you.

First you need to make it so the categories are included in the content. If you have already done this, PAY CLOSE ATTENTION TO EXACTLY HOW I DID IT, most importantly the "XX" characters I add to the beginning and end of the CategoryDisplayName in the content.

Edit the file ~/App_Code/Samples/Modules/SampleClassLoaderModule.cs to include this code

public override void Init()
{

DocumentEvents.GetContent.Execute += new EventHandler<DocumentEventArgs>(GetContent_Execute);

}

void GetContent_Execute(object sender, DocumentEventArgs e)
{

// get assigned categories
System.Data.DataSet categories = CMS.SiteProvider.CategoryInfoProvider.GetDocumentCategories(e.Node.DocumentID, String.Empty, String.Empty);

if (!CMS.GlobalHelper.DataHelper.DataSourceIsEmpty(categories))
{
// loop through all categories and add them to the content variable
foreach (System.Data.DataRow category in categories.Tables[0].Rows)
{
//Add XX to the beginning and end of the CategoryDisplayName so that our filter can pin point these category names while ignoring the rest of the content
e.Content = e.Content + " XX" + CMS.GlobalHelper.ValidationHelper.GetString(category["CategoryDisplayName"], "") + "XX";

}
}



}


Next Go to your smart search index that you will be using in your search results web part in Site Manager>Administration>Smart Search. Find your index and rebuild it. This time it will include the categories.

After you have rebuilt your index, create a query for your smart search filter to use.
Go to Site Manager>Development>Document Types>Root (CMS.Root) and select the querys tab.

Add a new query with this text

SELECT '_content', 'XX'+CategoryDisplayName+'XX', CategoryDisplayName FROM CMS_Category WHERE ##WHERE## ORDER BY ##ORDERBY##



Notice that the second column it will return has the 'XX's added before and after the CategoryDisplayName. Also notice the first column value of '_content' this tells the filter that it will be searching the content field of the search index items.

Now go to your search page. Follow some of the instructions on This Page to get the right web parts on your page. Instead of using the query WHERE condition they mention on that page, use this "CategorySiteID = {%CurrentSite.SiteID%}" and use "CategoryDisplayName" for the ORDER BY. Also, be sure to select the new query that you added earlier instead of the one they mention and make the Filter Mode a dropdownlist instead of a checkbox.

Once you have done all of that, you should be able to search in the dialog and it will only show results that have the selected category assigned to them.

I have this working fine on my local dev site, so let me know if you need any more help.

User avatar
Member
Member
stephen.turley-terradon - 6/13/2012 7:32:15 AM
   
RE:Where clause for smart search filter and categories
Thanks, I'm going to try to get this thing working right now.

User avatar
Member
Member
stephen.turley-terradon - 6/13/2012 8:52:28 AM
   
RE:Where clause for smart search filter and categories
I've followed the instructions and the filter is no longer generating the check boxes to select a category.

User avatar
Certified Developer 8
Certified Developer 8
Jiveabillion - 6/13/2012 9:05:07 AM
   
RE:Where clause for smart search filter and categories
It should be a dropdown list. Using a checkbox list would add a whole mess of complications.

Make sure the query you used is exactly the way I posted.

On my phone right now, but I will call you and help you later today of you still need it

User avatar
Member
Member
stephen.turley-terradon - 6/13/2012 10:01:50 AM
   
RE:Where clause for smart search filter and categories
Sure that would be great if you could call this afternoon. My extension is 201. Thanks

User avatar
Certified Developer 8
Certified Developer 8
Jiveabillion - 6/13/2012 10:28:37 AM
   
RE:Where clause for smart search filter and categories
After thinking about it, I don't think there would be issues with using a checkbox list. I forgot that we weren't the ones handling the actual filtering of the search results. The smart search should be able to handle filtering by multiple categories.

I tried to call you, but Trina said that you were out of the office.

I have a meeting at noon, so I won't be able to call again until after that. In the mean time, you can email me when you are available at ndeary@gmail.com.

Export your smart search filter settings and send them to me in an email.

User avatar
Certified Developer 8
Certified Developer 8
Jiveabillion - 6/14/2012 4:35:26 PM
   
RE:Where clause for smart search filter and categories
For anybody following this thread, we were able to get it working.

The Categories weren't showing up for him because they were all global categories and the where clause was filtering them down to categories assigned to the current site (there were none).

He then just had to rebuild the index again and everything worked fine.

User avatar
Certified Developer 8
Certified Developer 8
Jiveabillion - 9/15/2012 12:49:44 PM
   
RE:Where clause for smart search filter and categories
For anyone who might be wondering why I added the 'XX' before and after the category names:

If I didn't add the 'XX' before and after the category names, then the search results for , as an example, category named "Photos", would return all documents that not only have the category assignment of "Photos", but also ones that have the word "Photos" in their content (even ones that aren't assigned the category "Photos").

By modifying the category name, it allows for much greater precision when being used to filter the search results.

User avatar
Member
Member
rlull - 9/12/2013 1:17:37 PM
   
RE:Where clause for smart search filter and categories
I know this is an old post, but is this still applicable in v7?