A Better Way To Relate - Relationship Extended

One of the things I’m known in the Kentico community is my desire to build useful tools for developers, so they can leverage Kentico’s features to a greater degree of ease and scope.  I’ve created tools such as the CSV Import tool, allowing you to easily import files into your custom table and module classes.  I’ve created the Advanced Category Selector and Many to Many Selector in order to be able to more easily leverage Categories and Binding tables. 

I’ve also created various Portal tools such as the Bootstrap Layout tool to easily layout pages, Parameterized Query Repeaters to be able to use more secure parameterized queries to get at data, Webpart to API Converter converts to create easy APIs, and the Transformable Webpart to create simple styled webparts and widgets without touching code.

With Kentico announcing a move towards MVC, my focus is also moving more towards tools that will help you structure your data in a way that will work well with building sites using MVC technology.  My latest tool (which will be going in for approval soon) is a Relationship Extended module.  It consists of two areas of improvement which we’ll dive into!

Improving Categories

As many of you know, Kentico has a very nice Category module, and categories are a primary way of organizing your data.  However, as I’ve mentioned in past articles, one major issue I have found with Categories is they are tied to the Document, and not the Node.  On the content tree, each element is a Node, but it can have multiple documents.  A Document is a localized flavor of that node, so if you have English and Spanish on your site, you will potentially have 2 documents to the single node.

However, organization rarely ‘changes’ with language.  A News article will be a Technology-type article whether it’s in English or Spanish, or a product will be one category or another, whether it’s in German or French. So part of my module installs a new class, the "Tree Category."  This is the same as Document Category except it has a NodeID instead of a DocumentID.  To use this, I’ve also updated the Advanced Category Selector to have a default option of "Save Type" of "Node Category," so you can use the tool to automatically relate these objects.

New UI

Another thing that is included with this module is a new UI Template "Edit Categories" which is a UI Page version of the Advanced Category Selector, so you can have categories in a special UI tab instead of on the Form (which required a Field without database representation on each page type you want to use it on). You can learn how to create User Interfaces on Kentico Documentation.

New Macro methods

To allow for easier usage of your Categories if you are using the Portal Method (or even in the Document Queries), I have also included a set of new Macro Methods to generate the "WHERE" conditions.  These helper functions automatically handle any potential SQL injection issues, for Document and Node Categories they automatically convert Code Names and Guids to IDs for faster lookups, and some other nice things.  These are all in the RelHelper macro namespace.

RelHelper.GetDocumentCategoryWhere("Technologies,Programming") =>
(DocumentID in (Select DocumentID from CMS_DocumentCategory where CategoryID in (3,4)))
 
RelHelper.GetNodeCategoryWhere("Technologies,Programming") =>
(NodeID in (Select NodeID from CMS_TreeCategory where CategoryID in (3,4)))
 
RelHelper.GetBindingCategoryWhere("customtable.NodeToCategory", "NodeGUID", "NodeGUID", "CategoryGUID", "4A3AFD53-EC34-4ABE-AE52-C9D1704118DA|728710EF-0F57-4F7B-81A2-0CA4C5A9E986", RelEnums.IdentityType.Guid) =>
([NodeGUID] in (Select [NodeGUID] from customtable_NodeToCategory where [CategoryGUID] in ('728710ef-0f57-4f7b-81a2-0ca4c5a9e986','4a3afd53-ec34-4abe-ae52-c9d1704118da')))
 
RelHelper.GetBindingWhere("custom.TestNodeToCategoryNoCode", "CMS.Tree", "NodeID", "NodeID", "CategoryID", "1,5,10") =>
([NodeID] in (Select [NodeID] from custom_TestNodeToCategoryNoCode where [CategoryID] in (5,10,1)))
 
There are also other options on each that you can configure:

Condition Type: Takes a RelEnums.ConditionType value.

• Any: The default value, will find relationships where it contains any of the given values
  • RelHelper.GetNodeCategoryWhere("Technologies|Programming", RelEnums.ConditionType.Any) => (NodeID in (Select NodeID from CMS_TreeCategory where CategoryID in (3,4)))
• None: Will find relationships that do not have any of the given values
  • RelHelper.GetNodeCategoryWhere("Technologies|Programming", RelEnums.ConditionType.None) => (NodeID not in (Select NodeID from CMS_TreeCategory where CategoryID in (3,4)))
• All: Finds related items that have all of the given values.  Often requires the Table Name of the relationship (such as CMS_Document, CMS_Tree, etc).
  • RelHelper.GetNodeCategoryWhere("Technologies|Programming", RelEnums.ConditionType.All, "CMS_Node") => (Select Count(*) from CMS_TreeCategory where CMS_TreeCategory.NodeID = CMS_Node.[NodeID] and CategoryID in (3,4)) = 2

___TableName: Needed for Condition Types All in order to generate the proper SQL Where condition, this is the name of the table you are filtering (ex: CMS_Document, CMS_Tree, or whatever).

Identity Type: Takes a RelEnums.IdentityType value.  For Custom Binding tables, you can bind on IDs, CodeNames, or GUIDs.  This parameter allows the system to handle SQL injections and parsing of values.

Related Pages – Extended

In my June 2018 blog post, I discussed various ways to relate objects.  One was through Related Pages.  Kentico has a Node to Node relationship table that you can link nodes with specifically typed relationships (say a “Banner” relationship to link Banner pages to a page).  However, there are 2 main issues with Kentico’s default implementation.

Normal Page Relationships

Normal page relationships (General -> Related Pages) can be added through the tab and can be applied across page types.  However, you can’t order these relationships, meaning you can add “Banners” to a page, but you can’t sort them in different ways.

Adhoc Related Pages

You can make Adhoc Relationships by adding a field of “Page” type to a page type.  This allows you to add pages on the Form tab, and with these you can sort them.  But it’s on the specific page types that you assign it, so you can’t have these relationships across page types, and it’s also very difficult to use these relationships in the UI because the Relationship Name is a mixture of the page type’s code name and the field’s GUID.  Messy.

Both

If these limitations weren’t enough, both methods share two very major flaws:  You can’t limit the page types you can select (which almost goes against the idea of defining relationship types, why would you allow a “Folder” to be used in a “Banner” relationship?).  The other thing is you can only add pages one at a time, which makes for a very length selectin processes if there’s more than a couple items you are relating.

Out with the Old, in with the New

To remedy all these things, I’ve created two new pieces.  A Relationship Name Extended UI page and a UI Template: Edit Relationships.

The Relationship Name Extended is to allow you to create Adhoc relationships, since the default UI doesn’t allow you to create these normally, and only Adhoc relationships can be sorted.

The Edit Relationship Template then allows you to create UI elements to handle these relationships. Since a picture is worth 1000 words, and a video is worth 1000 words at however many frames per second, I’m going to give a Video tutorial on how to configure and use this tool.

Coming Soon!

Thanks for taking the time to go through this lengthy blog article and video, keep your eyes peeled for the tool, it will be available for Kentico 10, 11, and 12 (once it comes out).  It will be on the marketplace once it's all finalized and approved, and will probably be out earlier on the resources page.