how to get the position of documents in the website content tree

Novice User asked on April 9, 2018 17:43

How to get the position of a document on a Kentico content tree inside a folder. I have got a folder in which there are documents with child pages. I want to know the position of a document for e.g if this is my folder

1. DocA
  1.1 DocAA
2. DocB
      2.1 DocBA
    2.2 DocBB
3. DocC

so How do I get the value of DocC as 6 for its position taking account of childs as well inside a node?

content tree

Correct Answer

Peter Mogilnitski answered on April 9, 2018 21:34

Try this one:

DECLARE @NodeAliasPath nvarchar(max) = N'/myfoldername';
DECLARE @DocumentCulture nvarchar(max) = N'en-US';

WITH cteTrueTree
AS ( 
SELECT NodeAliasPath, DocumentName, NodeID, NodeOrder, NodeParentID,  CAST(0 AS varbinary(max)) AS Level 
FROM View_CMS_Tree_Joined AS C WITH (NOLOCK, NOEXPAND) 
WHERE  NodeAliasPath LIKE  @NodeAliasPath AND DocumentCulture = @DocumentCulture 
UNION ALL
SELECT vw.NodeAliasPath, vw.DocumentName, vw.NodeID, vw.NodeOrder, vw.NodeParentID,  Level + CAST(vw.NodeOrder AS varbinary(max)) AS Level
FROM View_CMS_Tree_Joined vw
INNER JOIN cteTrueTree ON vw.NodeParentID = cteTrueTree.NodeID
WHERE vw.NodeAliasPath LIKE  (@NodeAliasPath + '/%')  AND DocumentCulture = @DocumentCulture  
)
SELECT *
FROM cteTrueTree
order by Level

0 votesVote for this answer Unmark Correct answer

Recent Answers

Brenden Kehren answered on April 9, 2018 18:15 (last edited on April 9, 2018 18:16)

In your query, if you perform the order by on NodeLevel, NodeOrder, NodeName then simply do a count on the pages, this should get you the position based on their order. This would all depend on where you're running the query as well. If in the API, pretty easy to loop through.

0 votesVote for this answer Mark as a Correct answer

Novice User answered on April 9, 2018 18:43

DECLARE @NodeAliasPath nvarchar(max) = N'/myfoldername/%';
DECLARE @DocumentCulture nvarchar(max) = N'en-US';

SELECT *
FROM View_CMS_Tree_Joined AS C WITH (NOLOCK, NOEXPAND) LEFT JOIN View_CMS_Tree_Joined AS P ON [C].[NodeParentID] = [P].[NodeID]
WHERE  ([C].[NodeAliasPath] LIKE @NodeAliasPath AND [C].[DocumentCulture] = @DocumentCulture)
order by p.NodeLevel,p.NodeOrder,p.NodeName

I am using this as a query but according to this all the child pages are coming in the end and that's why the position of the document is not determined properly.

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on April 9, 2018 18:47

What I'd do is add a Hieraricharl Viewer to your page, select the page types you need/want and enable SQL Debug then view the output SQL. This will give you the proper query needed to get what you're looking for.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on April 9, 2018 20:38 (last edited on April 9, 2018 20:39)

Just do ORDER BY c.NodeAliasPath insead of order by p.NodeLevel,p.NodeOrder,p.NodeName it will fix the order

1 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on April 9, 2018 20:49

Ordering by NodeAliasPath will ONLY give you an alphanbetical order by alias path not the order as it stands in the content tree. Ordering by NodeLevel, NodeOrder, NodeName will give you the results as it stands in the content tree.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on April 9, 2018 21:11

It is alphabetical hierarchy i.e. "who is parent of who" alphabetically ordered. I'll try to write a recursion sql query to get the exact order a bit later.

0 votesVote for this answer Mark as a Correct answer

Novice User answered on April 9, 2018 21:16

Brenden's statement is correct as it will only give me an alphabetically sorted list of document info, where I am trying to figure out is a list of documents with their children so that i know where they stand in a folder i.e their position.

0 votesVote for this answer Mark as a Correct answer