Displaying data hierarchically
A step-by-step guide to using hierarchical transformations in Kentico for displaying any data you wish… without fear.
How does it work?
When developing a site in Kentico, there is often a need to display hierarchical data on the page. Even when you would like to display nested lists of documents, document attachments or custom table data, surprisingly you do not have to develop your own custom web part. There is a standard web part included as default in your Kentico installation that can manage all of this and much more. It is called the Universal viewer with custom query.
With the Universal viewer with custom query, you can pull any information stored in the database by assembling your custom SQL query. To successfully pass the data to the transformation, the result of the query has to be a single table containing unique ID, level (in the hierarchy) and parent ID (which would be NULL for level 0 rows).
You can merge different tables (like document tables, custom tables, etc.) into one using the UNION operator in the SQL query. Tables merge-able by UNION must contain the same number of columns with the same data type and order.
Get the table names
So when considering using the Universal viewer with custom query for displaying your data hierarchically on the page, the first thing to do is find the right tables in the Kentico database where the data is stored. The tables must contain all the information you would like to display on the page as well as the reference to the parent (parent ID) for the items in the nested levels of the hierarchy.
For documents (pages) you can use the View_CMS_Tree_Joined table which contains general Node, Document and SKU columns. The attachments can be found in the CMS_Attachment table or in View_CMS_Tree_Joined_Attachments (if you also want the properties of the parent document). The custom tables are named with a customtable_ prefix (e.g. customtable_mytable). When getting data from joined views tables, don’t forget to select only the columns you need.
The tables in the Kentico database are named quite comprehensibly so you can easily determine its purpose from its name. If you’re not sure how to find the table you need, use the Filter functionality in the Microsoft SQL Server Management Studio to search the tables according to felicitous keywords.
Do not forget to search in the Views section, which contains already-joined tables – this can save you work in getting data originally stored in multiple tables.
The SQL Server Management Studio is also very useful for testing and eliminating errors in your custom SQL query – I will mention the assembling of the query later in this article.
Practical example
Let’s assume you would like to display all documents from the /Products section with the product document type and also all images stored as attachments in these documents. To make it interesting, we might also want to display product reviews and, using message boards, fetch the data from the View_Boards_BoardMessage_Joined table.
Assembling the query
Place the Universal viewer with the custom query web part on your page. To complete the Query name property, create a new custom query by clicking the New button. The query will look like this:
SELECT DocumentID as ItemID, DocumentName as Name, SKUShortDescription as Text, null as ParentID, 0 as Level, NodeAliasPath as URL, null as Date, null as AttachmentGUID from View_CMS_Tree_Joined
WHERE NodeAliasPath LIKE '/Products/%' AND ClassName = 'CMS.Product'
UNION
SELECT MessageID, MessageUserName, MessageText, BoardDocumentID, 1, null, MessageInserted, null from View_Boards_BoardMessage_Joined
UNION
SELECT AttachmentID as ID, AttachmentName, AttachmentTitle, AttachmentDocumentID, 1, null, null, AttachmentGUID from CMS_Attachment
Using this query, we will be able to access the values of the columns in the transformations via:
<%# Eval("ColumnName") %>
Note that we determine the column names of the result table only in the first select command. The values from other tables will inherit the names from the first one. In our case, the column names of the result will be: ItemID, Name, Text, ParentID, Level, URL, Date and AttachmentGUID.
When assembling your own custom query based on your particular needs, it’s good practice to test the functionality and the results of the individual selects, as well as the whole query, in the Microsoft SQL Server Management Studio connected to the SQL server your site is using. It’s faster and more efficient than fine-tuning the query up in the Kentico administration UI.
Tinkering the transformation
Here comes the challenge of creating a hierarchical transformation that is composed of standard transformations used for certain levels of the hierarchy. In the Web part properties window, click the New button to create a new hierarchical transformation.
In the New transformation dialog window, select the document type where the transformation should be stored. (It doesn’t matter, since this has no influence on the functionality of the transformation.) Name it and click Save.
Now you will see the Transformation properties window where you can add the transformations of various types to the hierarchical transformation. According to your intended HTML layout, you will probably use just a few of them.
Let’s assume we would like to get the HTML like this:
<div class="product-list">
<div class="product">
<div class="product__name">
<a href="http://product abcd url">Product ABCD name</a>
</div>
<div class="product__details">
<div class="product__attachment">
<a href="http://product atachment url">Product attachment name</a>
</div>
<div class="product__attachment">
<a href="http://another product atachment url">Another product attachment name</a>
</div>
<div class="product__comment">
Alice (18/7/2014): "I like this product."
</div>
<div class="product__comment">
Bob (19/7/2014): "I am not sure about this product."
</div>
</div>
</div>
<div class="product">
...
</div>
...
</div>
Since we would like to have two different markups for attachments and comments (and they’re both on the same level in the hierarchy), we need to use the transformation for them that contains the condition determining which code to generate. When using the standard Universal viewer (without custom query) for hierarchical display of documents, there is an option to specify the document types for which the transformation should be used. Unfortunately this is not supported by design when using custom query.
Use this procedure to add the transformation to the hierarchical transformation:
-
In the Transformation properties windows, click the “Add transformation” button.
-
Select Transformation type, Level, and uncheck the Apply sublevels options.
-
Click the “New” button on the line called Transformation name.
-
Choose a proper, unambiguous name and complete the Transformation name property field (white spaces are not allowed).
-
Complete the code of the transformation.
-
Click Save.
-
Click Cancel.
-
Click Save again.
-
Click the “Transformations” tab to get back to the list of transformations in the current hierarchical transformation. You can now add another transformation by returning to the Step 1.
Now add these transformations:
|
Transformation type: |
Header transformation |
|
Level: |
0 |
|
Apply to sublevels: |
uncheck |
|
Transformation code: |
<div class="product-list">
|
|
Transformation type: |
Footer transformation |
|
Level: |
0 |
|
Apply to sublevels: |
uncheck |
|
Transformation code: |
</div>
|
|
Transformation type: |
Item transformation |
|
Level: |
0 |
|
Apply to sublevels: |
uncheck |
|
Transformation code: |
<div class="product">
<div class="product__name">
<a href="<%# Eval("URL") %>"><%# Eval("Name") %></a>
</div>
|
|
Transformation type: |
Separator transformation |
|
Level: |
0 |
|
Apply to sublevels: |
uncheck |
|
Transformation code: |
</div>
|
|
Transformation type: |
Header transformation |
|
Level: |
1 |
|
Apply to sublevels: |
uncheck |
|
Transformation code: |
<div class="product__details">
|
|
Transformation type: |
Footer transformation |
|
Level: |
1 |
|
Apply to sublevels: |
uncheck |
|
Transformation code: |
</div>
|
|
Transformation type: |
Item transformation |
|
Level: |
1 |
|
Apply to sublevels: |
uncheck |
|
Transformation code: |
<div class="<%# IfCompare(Eval("Date"), "", "product__comment", "product__attachment") %>">
<%# IfCompare(Eval("Date"), "", Eval("Name") + "(" + Eval("Date") + "):\"" + Eval("Text") + "\"", "<a href=\"~/cms/getattachment/"" + Eval("AttachmentGUID") + "\">" + Eval("Name") + "</a>") %>
</div>
In the last transformation, we use the value of the Date column to determine whether the current row represents comment or attachment data. In the case of an attachment, the Date column is empty.
After adding all nested transformations, close the Transformation properties window and fill in the rest of the required Web part properties in the Hierarchical properties section (ID column, ParentID Column, Level column) according to the column names in the result of your custom SQL query. Don’t forget to check the Load hierarchical data property, which is unchecked by default.
And that’s it! Your data should be displayed exactly as you wish. Please do not hesitate to ask about any related topics in the discussion under this article. I will be happy for any questions or feedback.
Some interesting facts:
-
Items with a missing parent (parent ID points to item out of the selection) will not be displayed.
-
In our example we use a combination of flat and tree-like sub-levels whose position relate to the parent item. In most cases, this could be managed more easily by using the Hierarchical display mode property.