Document Query InCategories * all of the selected *

Alonso Gonzalez asked on January 25, 2018 03:36

Hi there,

Im building a custom filter that lets the user filter by category, but the default "InCategories" won't work for me since it will get all pages to belong to ANY of the specified categories.

I need my filter to get me all pages that belong to ALL of the specified categories, do you guys have an idea of how can I achieve this ?

Thanks

Recent Answers

Trevor Fayas answered on January 25, 2018 04:07

Try .Where("(Select Count(*) from CMS_DocumentCategory where CMS_DocumentCategory.DocumentID = DocumentID and CategoryID in (Select CategoryID from CMS_Category where CategoryName in ('Development', 'NetworkAdministration', 'Programming'))) = 3")

messy but should work...

0 votesVote for this answer Mark as a Correct answer

Alonso Gonzalez answered on January 25, 2018 18:30

That approach give me a SQL error , so the == 3 its because we have a 3 categories selected ?

Is there a way to see the code that's used to implement the .InCategory method ?

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on January 26, 2018 18:05 (last edited on January 26, 2018 18:39)

if I get it right if a user selects 2 categories = you will show pages that at least will have these two categories? I think the document helper is flexible enough. You final SQL query should be something like:

SELECT cms_Document.DocumentID, cms_Document.DocumentName, COUNT(CategoryID)
FROM cms_Document join CMS_DocumentCategory  ON cms_Document.DocumentID = CMS_DocumentCategory.DocumentID 
WHERE CategoryID  in (123, 456)
GROUP BY cms_Document.DocumentID, cms_Document.DocumentName 
HAVING COUNT(CategoryID) = 2

All the document must have categories must have all categories from IN (...)

        var result = DocumentHelper.GetDocuments("CMS.Smartphone")
                            .InCategories("Android", "Smartphones")
                            .GroupBy("CategoryName")
                            .Having("count(*)=2");

You have to experiment a bit here with document helper.

0 votesVote for this answer Mark as a Correct answer