Many-to-Many relationships in the Kentico CMS Content Tree

Login to vote

For a few of my Kentico implementations, I've encountered the need for a Many-to-Many relationship for documents that exist in the content tree. Documents within the content tree are stored in a hierarchy and have a parent-child relationship.  There are a few different ways to achieve a many-to-many relationship while storing documents in the tree, but this tip will just describe a quick work-around using a bit of sql and Kentico's query repeater.  I'll first discuss an example of where the issue emerges and then the work-around.

To see a working version, go to:

The implemation example:

I have an implementation of the Kentico CMS as a solution for a Fly Fishing website. For the website, I have two documents types: Fish and Fishing Location. One of the features I will need to implement is to list the types of "Fish" by the "Fishing Location" and the fishing location filter needs to allow for mult-select. At this point, I realize that I need a many to many relationship.



If I was building this from scratch, I would probably have three tables with in a normalized relationship such as:

FishingLocation => FishingLocationFish <= Fish

When I create the document types in Kentico, notice that it creates a table for each on.


So, to create the many-to-many relationship, I'm going to add a multi-select field called "FishAvailable" for the FishingLocation document type. The query for our multiple choice will be:


SELECT [FishID],[FishName]
FROM [KenticoAngler_Fish]


Now I can go update the Fishing Locations by selecting which fish are available:



As you can see below, the values for this multi-select field are pipe delimited. In most cases, this will work fine with most the functionality I create, but not for the multi-select fishing location filter I need to build.


To get around this, I'll create a sql function to split the values of FishAvailable column, so we can create queries as though it were a separate table. The following function will do the trick!


/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 05/19/2011 14:49:24 ******/


  @delimited nvarchar(max),
  @delimiter nvarchar(100)
  val nvarchar(max)
  declare @xml xml
  set @xml = N'' + replace(@delimited,@delimiter,'') + ''

  insert into @t(val)
    r.value('.','varchar(5)') as item
  from @xml.nodes('//root/r') as records(r)


Using this function I'm going to create a custom query for the "Fish" document type in the CMSsitemanager>Development>Document Types, so that we may reference it in the query repeater.  The query will look like this:

FROM KenticoAngler_Fish 
WHERE KenticoAngler_Fish.FishId IN (
SELECT  distinct s.* 
FROM KenticoAngler_FishingLocation CROSS APPLY dbo.split([KenticoAngler_FishingLocation].FishAvailable,'|') s 

Next, I'll create a custom user control to act as the Fishing Location filter.  The user control will have an on_click() event that posts the selected Fishing Location Ids in the url.

Finally, we'll create a query repeater webpart that references the custom query we created for our "Fish" document type.  For the WHERE clause of the repeater, I implemented a custom macro: {#fishlist#}.  In /App_Code/Global/CMS/CMSCustom.cs, the ResolveCustomMacro method will look like this:


///      /// Custom macro handler     /// 
    /// Sender (active macro resolver)
    /// Expression to resolve
    /// Returns true if the macro matches (was resolved)
    public static string ResolveCustomMacro(MacroResolver sender, string expression, out bool match)
        match = false;
        string result = expression;

        // Add your custom macro evaluation
        switch (expression.ToLower())
            case "fishlist":
                match = true;
                    result = "";
                    result = "KenticoAngler_FishingLocation.FishingLocationId IN(" + HttpContext.Current.Request.QueryString["li"] + ")";

        return result;

I can now filter our "Fish" documents by our "Fishing Location" document Id's!

Using the same SQL Split function, I can aslo filter "Fishing Locations" by "Fish" document Id's, by creating a query such as:


SELECT KenticoAngler_FishingLocation.* 
FROM KenticoAngler_FishingLocation CROSS APPLY dbo.split([KenticoAngler_FishingLocation].FishAvailable,'|') s 
WHERE s.val = 4

(s.val is set to the Fish Id.)

For multiple, add "DISTINCT" and change the where clause:


SELECT DISTINCT KenticoAngler_FishingLocation.* 
FROM KenticoAngler_FishingLocation CROSS APPLY dbo.split([KenticoAngler_FishingLocation].FishAvailable,'|') s 
WHERE  s.val IN(1,2)




Posted by Jason Ellison on 5/19/2011 11:34:32 AM
This is a very interesting approach. As a rule, I don't use transformations, as they store C# outside of source control. That being said, the last bit of this isn't relevant for me, but I like the SQL approach to binding the check box list. I may do this in the future and just break the delimited string up in C# and execute a second query. I generally abstract the Kentico interactions into my BLL, so the knowledge of how they're linked doesn't bubble up to my repeaters.

The way I usually handle many-to-many relationships is to link the relevant nodes as children. That way, you can easily just execute a node query off of the parent node alias path.

Thanks for the great post!

Andrew Lundgren
9/9/2012 9:48:43 PM