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.
.gif.aspx)
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)