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

1
Comments
3
Votes
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: http://www.kenticoangler.com/Fish.aspx

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.

ka_web.jpg

 

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

FishingLocation => FishingLocationFish <= Fish
many-to-many-example-h.jpg

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

doctypes-(1).gif

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]

flocation_doctype.gif

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

 arky-selectfish.gif

 

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.

flocation_stored.gif

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 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Split]
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)
) RETURNS @t TABLE
(
  val nvarchar(max)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'' + replace(@delimited,@delimiter,'') + ''

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

  RETURN
END
GO

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:

SELECT ##TOPN## ##COLUMNS##
FROM KenticoAngler_Fish 
WHERE KenticoAngler_Fish.FishId IN (
SELECT  distinct s.* 
FROM KenticoAngler_FishingLocation CROSS APPLY dbo.split([KenticoAngler_FishingLocation].FishAvailable,'|') s 
WHERE  ##WHERE##)
ORDER BY ##ORDERBY##

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;
                if(HttpContext.Current.Request.QueryString["li"]==null)
                    result = "";
                else
                    result = "KenticoAngler_FishingLocation.FishingLocationId IN(" + HttpContext.Current.Request.QueryString["li"] + ")";
                break;
        }
        

        return result;
    }

I can now filter our "Fish" documents by our "Fishing Location" document Id's!
Fish-one-selected.jpg

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
  
Comments
alundgren
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
http://www.theatomgroup.com
9/9/2012 9:48:43 PM