Category App

Sorry the formatting of my example should be vertical

Hi Paul,

I haven't tried this, but I would think you could create a query that provides the list of categories you want to display in the Smart Search Filter, including the columns required by the web part -- something like this:

SELECT
    IndexName = 'paulsindex',
    CategoryName,
    CategoryDisplayName
FROM
    CMS_Category
WHERE
    CategorySiteID IS NULL -- NULL if the category is global; SiteID if the category is a site category
AND
    CategoryParentID IS NULL -- NULL if the category is root category, or CategoryID if the parent category

After creating the query, it can be selected in the web part's "Query name" property.

Mike

Hi Can you elobate a bit more on how this sits in a smart filter web part, still a bit new at this. This was my original Query which works on a general level

SELECT 'DocumentCategoryIDs','-1', '(all)' AS CategoryDisplayName UNION SELECT '+DocumentCategoryIDs', CategoryID, CategoryDisplayName FROM CMS_Category WHERE ##WHERE## ORDER BY ##ORDERBY##

Where Condition

((CategorySiteID IS NULL) OR (CategorySiteID = {% CurrentSite.SiteID |(identity)GlobalAdministrator%}))

Hi Paul,

The first column in the query needs to be the index name. To find that, look up the index's Code Name in the Smart Search module. Without trying it myself, I think the SQL query would look more like this:

SELECT 
    'paulsindexname',
    '-1',
    '(all)' AS CategoryDisplayName
UNION SELECT
    'paulsindexname',
    CategoryID, 
    CategoryDisplayName 
FROM
    CMS_Category
WHERE ##WHERE## 
ORDER BY ##ORDERBY##

Also, I'm not sure you want to filter by CategoryUserID. How about just this for the Where Condition?

((CategorySiteID IS NULL) OR (CategorySiteID = {% CurrentSite.SiteID |(identity)GlobalAdministrator%}))

Mike

I understand what you are saying, and have been playing with some SQL. Having spoken to my project lead they have specified they want a list of specific categories and their children. Im finding it difficult to understand how i am able to display these specific categories in my filter list rather than all of them.

Actually on further reflection, i have realised that the categories i require all sit under a root category called resources. I assume this would make it easier to list knowing this root ?, if so how would i display that ?

Hi Paul,

How to filter by a parent category is in the first answer. The where condition should contain:

CategoryParentID = [the CategoryID of your Resources category]

Mike

Hi Guys, I actually managed to get my filter displaying single root categories, and only sub child categories, using the following. Seems to work great. Not sure how elegant it is, but it works.

SELECT 'DocumentCategoryIDs','-1', '(all)' AS CategoryNamePath UNION SELECT '+DocumentCategoryIDs', CategoryID, CategoryDisplayName FROM CMS_Category WHERE ##WHERE## ORDER BY ##ORDERBY## Where Clause : CategoryLevel > 1 AND CategoryParentID IN (21,31,34,38,42,50,54,57,60)

Alright, thanks fur the update.