How a Page is connected to Page Type in database

Ashutosh Pandey asked on April 5, 2020 20:17

I'm trying to understand the relation between tables for a page. What I understand is this:

CMS_Tree contains one row per page. CMS_Document contains multiple rows per page based on culture. DocumentNodeID is the foreign key connected to NodeID of CMS_Tree.

We can create PageTypes to store form data for a page. What I'm not able to figure out is how record in page type table is connected to CMS_Tree.

I'm mapping each page with a category. For this, I added category_id field in page type of the page. Now, I want to load all pages related to a particular category.

Please help, thanks

Correct Answer

Brenden Kehren answered on April 5, 2020 23:18

Here is what you're looking for.

CMS_Tree is the table which holds the content tree (node) structure. The fields in this table remain as a constant so when you fill out webparts or document queries, you can use those constant values no matter what language is being used. For instance, NodeAliasPath remains the same value no matter what language is entered in the CMS_Document table.
CMS_Document is the table where all the "pages" for a tree node are stored. CMS_Document is related to the CMS_Tree table via the DocumentNodeID field. When joining to the page type table(s), you will use the DocumentForeignKeyValue, which will be the PK value in the page type table you are joining to.
CMS_View_Tree_Joined is the view which joins both the CMS_Tree and CMS_Document table together. This is very helpful to know and use. Keep in mind you'll need to join other tables to it if you wish to get more coupled data.

If you want to use Categories for page types, I suggest following the documentation and using the out of the box configuration. Keep in mind, the table CMS_DocumentCategory only has the DocumentID and CategoryID fields in it. Remember cultures are only stored with the document. So a page in every language can have their own categories assigned to them.

So your query would look something like:

SELECT v.DocumentName, v.DocumentID, v.NodeAliasPath, c.CategoryDisplayName
FROM View_CMS_Tree_Joined AS v
LEFT OUTER JOIN CMS_DocumentCategory AS dc ON v.DocumentID = dc.DocumentID
INNER JOIN CMS_Category c on dc.CategoryID = c.CategoryID
WHERE v.ClassName = 'cms.menuitem'
AND v.DocumentCulture = 'en-us'
AND c.CategoryName = 'CategoryCodename'

Or if you're using a DocumentQuery you can use something like:

var docs = DocumentHelper.GetDocuments("cms.menuitem").WhereEquals("DocumentCulture", "en-us").OnCurrentSite().InCategories("CategoryCodeName");
0 votesVote for this answer Unmark Correct answer

Recent Answers

Ashutosh Pandey answered on April 6, 2020 05:53

Thanks Brenden for the quick reply, just a question

Is it best practice to select from View_CMS_Tree_Joined ? It is a very big view and can impact the performance of the site.

0 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on April 6, 2020 08:11

Just to add to what Brenden wrote - you can find more information about the DB tables and also which one holds versioned and culture related data in the Pages database structure documentation. For the View_CMS_Tree_Joined view - which version of Kentico are you using? In newer versions it was optimized. It also depends what you want to do - could you please share more details on your goal?

0 votesVote for this answer Mark as a Correct answer

Ashutosh Pandey answered on April 6, 2020 09:39

Hi Juraj, we are using V10 of Kentico. As suggested by Brenden DocumentForeignKeyValue solved the issue for me. I got Kentico code from a previous company who as using View_CMS_Tree_Joined heavily. They used it in from clause of query. I used it in left join and saw performance improvements in the website.

1 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on April 6, 2020 18:19


You can use that view however you wish, I'd highly suggest specifying the columns you need vs. bringing back all 170+ columns of data for every row.

0 votesVote for this answer Mark as a Correct answer

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