Limit result count of subelements when using hierarchical viewer

Stefan Sturm asked on January 12, 2018 14:01

Hello,

In general there is the "Select top N pages:" field to set a limit for all types and levels. But we need a possibility to limit the count determined by page type or the nesting level.

Any suggestions?

Kind reguards Stefan

Correct Answer

Trevor Fayas answered on January 12, 2018 14:15

Possible, but not easy. What you need to do is instead of hierarchy viewer for pages, user the one that let's you define a query (I think it's called universal viewer with custom query or just universal viewer).

Next you need to yourself generate the query that combines the cms_tree, cms_document, and any related class tables together, ordered by node level and then node order.

Once you have this, the next trick is to use SQL partitions/windowing over the class name and node parent, to select the total count and current index (using count () and row () over (your partition statement))

This way say you have a blog month and 5 blog posts, for the blog posts you will have a count of 5 and row index of 1, 2, 3, 4, 5.

At this point now you can do a where condition similar to "classname ='my.class' and rowindex<=3" in order to limit the number returned.

---EDIT--- Now that i'm at my desktop, here's an example SQL query to use. In my example i had a Blog, 2 blog months with about 4 blog posts in each, this query limited the blog posts to 3 returned.

select
-- Useful to have the COUNT here so you can also detect in your transformation if it's the last or the first of it's siblings.
COUNT(*) over (partition by NodeParentID, ClassName, DocumentCulture) as NewItemCount,
-- Replace this with the ##COLUMNS## macro
*
 from (
Select
-- This is the Item Position Function that returns what position of a certain page type under a single node it is.
ROW_NUMBER() over (partition by CMS_Tree.NodeParentID, CMS_Class.ClassName, CMS_Document.DocumentCulture ORDER BY NodeOrder) as ItemPosition,
-- Select the rest.  Note that this will NOT WORK if you have two fields of the same field name in your Page Type Classes, so may need to manually type out the select statement.
 *
 from CMS_Tree
 -- Join the 2 primary tables, Class and Document Name, the Viewer needs these so it knows which document and which transformation based on ClassName to apply
left join CMS_Class on CMS_Tree.NodeClassID = CMS_Class.ClassID
left join CMS_Document on CMS_Tree.NodeID = CMS_Document.DocumentNodeID
-- Join on each Page Type you are going to be selecting from
left join CONTENT_Blog on  (CMS_Document.DocumentForeignKeyValue = CONTENT_Blog.BlogID and ClassName = 'CMS.Blog')
left join CONTENT_BlogMonth on (CMS_Document.DocumentForeignKeyValue = CONTENT_BlogMonth.BlogMonthID and ClassName = 'CMS.BlogMonth')
left join CONTENT_BlogPost on (CMS_Document.DocumentForeignKeyValue = CONTENT_BlogPost.BlogPostID and ClassName = 'CMS.BlogPost')
where ClassName in ('CMS.Blog', 'CMS.BlogPost', 'CMS.BlogMonth')
) as Combined
where 
-- This is your limiting portion that you'll probably want to add in to the WHERE, but this the document is a certain class, then limit it by the item position
(Classname = 'CMS.BlogPost' and ItemPosition <= 3 OR Classname <> 'CMS.BlogPost')
-- Order needed for Hierarchy Viewers
order by  NodeLevel,NodeOrder

Once in Kentico, should look like this

select ##TOPN## COUNT(*) over (partition by NodeParentID, ClassName, DocumentCulture) as NewItemCount, ##COLUMNS##
 from (
Select
ROW_NUMBER() over (partition by CMS_Tree.NodeParentID, CMS_Class.ClassName, CMS_Document.DocumentCulture ORDER BY NodeOrder) as ItemPosition, *
 from CMS_Tree
 -- Join the 2 primary tables, Class and Document Name, the Viewer needs these so it knows which document and which transformation based on ClassName to apply
left join CMS_Class on CMS_Tree.NodeClassID = CMS_Class.ClassID
left join CMS_Document on CMS_Tree.NodeID = CMS_Document.DocumentNodeID
-- Join on each Page Type you are going to be selecting from
left join CONTENT_Blog on  (CMS_Document.DocumentForeignKeyValue = CONTENT_Blog.BlogID and ClassName = 'CMS.Blog')
left join CONTENT_BlogMonth on (CMS_Document.DocumentForeignKeyValue = CONTENT_BlogMonth.BlogMonthID and ClassName = 'CMS.BlogMonth')
left join CONTENT_BlogPost on (CMS_Document.DocumentForeignKeyValue = CONTENT_BlogPost.BlogPostID and ClassName = 'CMS.BlogPost')
where ClassName in ('CMS.Blog', 'CMS.BlogPost', 'CMS.BlogMonth')
) as Combined
where ##WHERE##
ORDER BY ##ORDERBY##

With a where condition of your (Classname = 'CMS.BlogPost' and ItemPosition <= 3 OR Classname <> 'CMS.BlogPost') and Order by of NodeLevel, NodeOrder

using the Universal viewer with custom query web part.

If this helped, please mark as answered, took a bit to formulate this for ya!

2 votesVote for this answer Unmark Correct answer

Recent Answers


Stefan Sturm answered on February 27, 2018 13:40

Thank you for the great support!

0 votesVote for this answer Mark as a Correct answer

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