Document table references
The following article gives a brief explanation of how the Document, Page templates, Document types, and Document type tables reference each other.
The tables we will be referencing are:
CMS_Class
CMS_Document
CMS_PageTemplate
CMS_Tree
CONTENT_MenuItem
For this example, we will be using the Corporate Site "Home Page" page template/document, and the Page menu item document type. Also, please note that each query has been limited to only return a single result in order to simplify this example. You will likely return multiple results when using these queries.
First we will start by finding the PageTemplateID for the ‘CorporateSite.HomePage’ page template:
SELECT PageTemplateID, PageTemplateCodeName FROM CMS_PageTemplate WHERE PageTemplateCodeName = 'CorporateSite.HomePage'
Once we have a given PageTemplateID, we can find all the documents which use this page template:
SELECT DocumentPageTemplateID, DocumentName, DocumentNodeID, DocumentForeignKeyValue FROM CMS_Document WHERE DocumentPageTemplateID ='28'
As you can see from the results, the CMS.PageTemplate.PageTemplateId = CMS_Document. DocumentPageTemplateID.
Expanding this a little further, we can return the values that are listed on the Form tab for the Page menu item document type. We need to query the CONTENT_MenuItem table using the CMS.Document.DocumentForiegnKey value for the given document:
SELECT * FROM CONTENT_MenuItem WHERE MenuItemID = '308'
As you can see from the results, the CMS_Document.DocumentForeignKeyValue = CONTENT_MenuItem.MenuItemID
In order to find all documents using the Page Menu Item document type, we first need to find the ClassID for the CMS.MenuItem document type in the CMS_Class table:
SELECT ClassID, ClassName from CMS_Class WHERE ClassName ='CMS.MenuItem'
Once we have the NodeClassID, we can find all the documents which use the Page Menu Item document type in the CMS_Tree table:
SELECT NodeClassID, NodeID, NodeName, NodeSiteID FROM CMS_Tree WHERE NodeClassID ='3441'
As you can see from the results the CMS_Class.ClassID = CMS_Tree.NodeClassID.
The following screenshot can be used for illustration purposes, showing all the tables used in this article.
-EH-
See also: Database referenceApplies to: Kentico CMS version 7.x