How do I query documents that are in multiple categories with document query?

Nathan Eary asked on August 3, 2015 06:47

I need to use document queries to filter documents that are in multiple categories. Meaning, they have to be in all of the categories that I specify.

I tried using the InCategory more than once, and that doesn't work. So I tried using WhereIn("DocumentID", mySubQuery.InCategory("category name").AsSingleColumn("DocumentID")) and that gives me a SQL error because it won't select only the DocumentID column. It keeps selecting NodeClassName and another column I don't recall (I'm writing this from memory because I'm not at my computer). I also tried AsSubQuery and specifying only the DocumentID column in the Columns() method. None of those actually return just the DocumentID column in my subquery, so they throw a SQL error about needing to either use "exists" or have only one column.

What can I do to query the way I need and why don't those methods work as I expect them to?

Recent Answers


Trevor Fayas answered on August 10, 2015 21:35 (last edited on December 10, 2019 02:30)

There's two solutions, depending on how you're rendering the Documents.

1: If you are using a Repeater, you need to write in the WHERE statement to filter out any item where the DocumentID is in the Document/Category Table for those categories. Below is a custom macro i made to do that.

2: If you are using a Smart Search Index, you need to add the Category Names to the Smart Search Index manually (make sure no numbers in the Category Name so it doesn't split them upon tokenizing), and then add a filter to filter on those. You can use the built in Kentico Filters to render something like +Categories('blue', 'short')

Macro to generate WHERE Items for Categories (used in WHERE area like:

(CategoryType is null or {% Util.FilterByCategories(EventTypes, "ANY", "|", false) |(handlesqlinjection)false|(identity)GlobalAdministrator%}

)

using CMS.Base;
using CMS.DocumentEngine;
using CMS.Membership;
using System.Collections.Generic;

[SmartSearchContentLoader]
public partial class CMSModuleLoader
{
    /// <summary>
    /// Attribute class for assigning event handlers.
    /// </summary>
    private class SmartSearchContentLoaderAttribute : CMSLoaderAttribute
    {
        /// <summary>
        /// Called automatically when the application starts.
        /// </summary>
        public override void Init()
        {
            // Assigns a handler to the GetContent event for pages
            DocumentEvents.GetContent.Execute += OnGetPageContent;
        }

        private void OnGetPageContent(object sender, DocumentSearchEventArgs e)
        {
            // Gets an object representing the page that is being indexed
            TreeNode indexedPage = e.Node;

            // Checks that the page exists
            if (indexedPage != null)
            {

                // Add category names to a searchable field "Categories" for filtering / weighing.
                List<string> joinedCategories = new List<string>();
                foreach (CMS.Taxonomy.CategoryInfo cat in e.Node.Categories)
                {
                    joinedCategories.Add(cat.CategoryName);
                }

                e.SearchDocument.AddGeneralField("Categories", string.Join("|", joinedCategories.ToArray()), true, true);

                // Gets the user object of the page owner
                UserInfo pageOwner = UserInfoProvider.GetUserInfo(indexedPage.NodeOwner);

                if (pageOwner != null)
                {
                    // Adds the value of the "Description" field from the owner's user settings into the indexed content
                    // Spaces added as separators to ensure that typical search index analyzers can correctly tokenize the index content
                    //e.Content += " " + pageOwner.UserDescription + " ";
                }
            }
        }
    }
}

Adding Custom Categories to Smart Search Index:

using CMS.Base;
using CMS.DocumentEngine;
using CMS.Membership;
using System.Collections.Generic;

[SmartSearchContentLoader]
public partial class CMSModuleLoader
{
    /// <summary>
    /// Attribute class for assigning event handlers.
    /// </summary>
    private class SmartSearchContentLoaderAttribute : CMSLoaderAttribute
    {
        /// <summary>
        /// Called automatically when the application starts.
        /// </summary>
        public override void Init()
        {
            // Assigns a handler to the GetContent event for pages
            DocumentEvents.GetContent.Execute += OnGetPageContent;
        }

        private void OnGetPageContent(object sender, DocumentSearchEventArgs e)
        {
            // Gets an object representing the page that is being indexed
            TreeNode indexedPage = e.Node;

            // Checks that the page exists
            if (indexedPage != null)
            {

                // Add category names to a searchable field "Categories" for filtering / weighing.
                List<string> joinedCategories = new List<string>();
                foreach (CMS.Taxonomy.CategoryInfo cat in e.Node.Categories)
                {
                    joinedCategories.Add(cat.CategoryName);
                }

                e.SearchDocument.AddGeneralField("Categories", string.Join("|", joinedCategories.ToArray()), true, true);
            }
        }
    }
}
0 votesVote for this answer Mark as a Correct answer

Joel Dahlin answered on January 19, 2016 21:53

@Nathan, did you come up with an answer on this? I am looking to do this now.

0 votesVote for this answer Mark as a Correct answer

Joel Dahlin answered on January 22, 2016 16:23

I received this from support and it did work for me.

private String DocumentCategories()
{
    StringBuilder sb = new StringBuilder();

    var categories = CategoryInfoProvider.GetCategories("CategoryName IN ('Space', 'Test')", "CategoryID");
    var documentCategories = DocumentCategoryInfoProvider.GetDocumentCategories()
        .WhereIn("CategoryID", categories)
        .Column("DocumentID")
        .GroupBy("DocumentID")
        .Having("COUNT('DocumentID') = 2");
    //return sb.Append(documentCategories.QueryText).ToString();
    var documents = DocumentHelper.GetDocuments()
        .WhereIn("DocumentID", documentCategories);

    foreach (var post in documents)
    {
        sb.Append("<br>" + "Document Name: " + post.DocumentName);
    }

    return sb.ToString();
}
1 votesVote for this answer Mark as a Correct answer

Alonso Gonzalez answered on January 25, 2018 22:05

Hey Joel Dahlin, Do you remember when you implemented this ?

Why are doing the ".Having("COUNT('DocumentID') = 2");" part ? I need to implement the same thing and can't find the proper way to do so.

Thanks!

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.