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.

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 ******/
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!

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)