Recursive SQL Statement - Problem with... 1

[Ovatvvon]

Hello all,

I am trying to make a recursive sql statement. It will pull from a "Navigation" table which has all the links for a navigation menu. The table is setup like so:

NAVIGATION
id, parent_id, title, active


The query that I have so far is like this:

SELECT
CASE	When N3.id Is Not Null Then N3.id
	When N2.id Is Not Null Then N2.id
	When N1.id Is Not Null Then N1.id
End As 'id',

CASE	When N3.id Is Not Null Then N3.parent_id
	When N2.id Is Not Null Then N2.parent_id
	When N1.id Is Not Null Then N1.parent_id
End As 'parent_id',

CASE	When N3.id Is Not Null Then N3.title
	When N2.id Is Not Null Then N2.title
	When N1.id Is Not Null Then N1.title
End As 'title'

FROM
(SELECT * FROM NAVIGATION WHERE parent_id=0 AND active=1) N1
LEFT OUTER JOIN (SELECT * FROM NAVIGATION WHERE ACTIVE=1) N2 ON (N1.id = N2.parent_id)
LEFT OUTER JOIN (SELECT * FROM NAVIGATION WHERE ACTIVE=1) N3 ON (N2.id = N3.parent_id)

The (two) Problems:
I have two problems with this:
1. (the minor problem) is that I am manually specifying how many joins (i.e. levels) there is in the menu structure. For every sub-level, I have to create another join manually. It would be nice if this was more dynamic, but I'm not sure how to do it. Does anyone else know?

2. (the bigger problem) is that this current setup calls most of the data I need, however, it does not grab the group / main link for any navigation-menu-item that has sub-menues. For instance, If at the root level I have categories 1) "my place", 2) "his place", 3) "her place" and 4) "their place"; and then the category 2 (i.e. His Place) has 3 sub-menu items that are 5) "abc", 6) "def" and 7) "ghi" (i.e. their parent_id = 'b', the above query will give me the following results in the view:

id, parent_id, title
1, 0, my place
5, 2, abc
6, 2, def
7, 2, ghi
3, 0, her place
4, 0, their place

It didn't display the following item:
2, 0, his place

But I need this missed item to be placed above its sub-menu items. After each category I want that categories submenu items to be listed, and then rollback and continue on with the menu structure the level above those sub-menu items.

Does anyone know how to make this work?

-Ovatvvon :-Q

 

[FlashMerlot]

This is a difficult task. Hierarcial structures of 'N' depth are not SQL server's strong point. We use a single table to store 100's of menu items. Each User can be a member of one or more groups. Each group has a different set of menu items allowed. Thus, to get a User's dynamic hierarcial menu was a tough task.

Below is the final StoredProc we use. Unfortunately, we ended up using a cursor ... but ... this works.

Sorry, but I don't have the time to walkthrough the code with you. Hopefully, you can glean some insight from how we accomplished the task.

By-the-way, this proc handles the automatic forced-inclusion of a non-direct parent node when a child node appears.

Good Luck

======================> FlashMerlot


ALTER PROC MenuGet(@UserID int) as
SET NOCOUNT ON
DECLARE
@CurrentMenuID int,
@MenuItemID int,
@ItemName varchar(50),
@Terrace int,
@PageURLView varchar(200),
@PageURLEdit varchar(200),
@PeakMenuItemID int,
@ItemIDofParent int,
@MenuContextID int,
@HooverText varchar(200),
@PageTitle varchar(100),
@CycleTillZero int,
@ContextName varchar(100)

SET @PeakMenuItemID = null

CREATE TABLE #RetTable
(
MenuItemID int,
ItemName varchar(100),
NavURL varchar(200),
ParentItemID int,
nest int,
AccessNone bit,
HasVisibleChild bit,
AccessView bit,
AccessEdit bit,
PageURLView varchar(200),
PageURLEdit varchar(200),
HooverText varchar(200),
PageTitle varchar(100)
)
CREATE TABLE #stack
(
item int,
Terrace int,
ItemName varchar(100),
ParentItemID int,
PageURLView varchar(200),
PageURLEdit varchar(200),
HooverText varchar(200),
PageTitle varchar(100)
)

DECLARE MyCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT MenuContexts.ContextName FROM UserMenuContexts
INNER JOIN MenuContexts ON UserMenuContexts.MenuContextID = MenuContexts.MenuContextID
WHERE (UserMenuContexts.UserID = @UserID)

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @ContextName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @PeakMenuItemID=PeakMenuItemID, @MenuContextID=MenuContextID FROM dbo.MenuContexts WHERE ContextName=@ContextName
SELECT
@ItemName=ItemName,
@PageURLView=PageURLView,
@PageURLEdit=PageURLEdit,
@HooverText=HooverText,
@PageTitle=PageTitle
FROM dbo.MenuItems WHERE MenuItemID=@PeakMenuItemID
INSERT INTO #stack VALUES
(
@PeakMenuItemID,
1,
'',
0,
isnull(@PageURLView,''),
isnull(@PageURLEdit,''),
isnull(@HooverText,''),
isnull(@PageTitle,'')
)
INSERT INTO #RetTable VALUES
(
@PeakMenuItemID,
isnull(@ItemName,''),
'',
0,
0,
0,
0,
0,
0,
isnull(@PageURLView,''),
isnull(@PageURLEdit,''),
isnull(@HooverText,''),
isnull(@PageTitle,'')
)
SET @Terrace = 1
WHILE @Terrace > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE Terrace = @Terrace)
BEGIN
SELECT @CurrentMenuID = item FROM #stack WHERE Terrace = @Terrace
IF @Terrace>1
BEGIN
SELECT
@ItemName=ItemName,
@ItemIDofParent=ParentItemID,
@PageURLView=PageURLView,
@PageURLEdit=PageURLEdit,
@HooverText=HooverText,
@PageTitle=PageTitle
FROM #stack WHERE item = @CurrentMenuID
INSERT INTO #RetTable VALUES
(
@CurrentMenuID,
isnull(@ItemName,''),
'',
@ItemIDofParent,
@Terrace - 1,
0,
0,
0,
0,
isnull(@PageURLView,''),
isnull(@PageURLEdit,''),
isnull(@HooverText,''),
isnull(@PageTitle,'')
)
END
DELETE FROM #stack WHERE Terrace = @Terrace AND item = @CurrentMenuID
INSERT #stack SELECT
MenuItems.MenuItemID,
@Terrace + 1,
MenuItems.ItemName,
@CurrentMenuID,
PageURLView,
PageURLEdit,
HooverText,
PageTitle
FROM MenuItems
INNER JOIN MenuItemsRelations ON MenuItems.MenuItemID = MenuItemsRelations.ChildMenuItemID
WHERE
(MenuItemsRelations.ParentMenuItemsID = @CurrentMenuID) AND (MenuItemsRelations.MenuContextID = @MenuContextID)
IF @@ROWCOUNT > 0 SET @Terrace = @Terrace + 1
END
ELSE
SET @Terrace = @Terrace - 1
END
FETCH NEXT FROM MyCursor INTO @ContextName
END
END
DROP TABLE #stack
CLOSE MyCursor
DEALLOCATE MyCursor

UPDATE #RetTable
SET AccessView=1 WHERE
MenuItemID in
(
SELECT DISTINCT GroupsMenuItems.MenuItemID
FROM GroupsUsers
INNER JOIN GroupsMenuItems ON GroupsUsers.GroupID = GroupsMenuItems.GroupID
WHERE (GroupsUsers.UserID = @UserID) AND (GroupsMenuItems.AccessView = 1)
)

UPDATE #RetTable
SET AccessEdit=1 WHERE
MenuItemID in
(
SELECT DISTINCT GroupsMenuItems.MenuItemID
FROM GroupsUsers
INNER JOIN GroupsMenuItems ON GroupsUsers.GroupID = GroupsMenuItems.GroupID
WHERE (GroupsUsers.UserID = @UserID) AND (GroupsMenuItems.AccessEdit = 1)
)

UPDATE #RetTable SET AccessNone=1 WHERE AccessEdit=0 AND AccessView=0
UPDATE #RetTable SET NavURL = PageURLView WHERE (AccessView=1) AND (PageURLView <> '')
UPDATE #RetTable SET NavURL = PageURLEdit WHERE (AccessEdit=1) AND (PageURLEdit <> '')
SET @CycleTillZero=1
WHILE @CycleTillZero>0
BEGIN
UPDATE #RetTable SET HasVisibleChild=1 WHERE
(MenuItemID IN
(
SELECT DISTINCT ParentItemID FROM #RetTable WHERE ((AccessView=1) OR (AccessEdit=1) OR (HasVisibleChild=1))
))
AND (HasVisibleChild = 0)
SET @CycleTillZero=@@ROWCOUNT
END

Select * from #RetTable order by nest, ItemName
Drop TABLE #RetTable

 

[FlashMerlot]

I should also mention, our MenuItems table has two URL columns. If a user gets View-Only access, we use the PageURLView for the hyperlink. Else we use the PageURLEdit.

 

[FlashMerlot]

I suddenly remember how hard this problem was to solve. (smile)

So, I'm also sending the related schema. Note: I've turned off most of the referential integrity but left stubs. Run the script, then using Enterprise Manager, create a new diagram and drop all these table into the new diagram. Hopefully you can figure out how stuff relates.

P.S. we use this mechanism in a corporate application supporting 1500 users. Works good.

Again good luck. Hierarcial 'N' depth menus are tough!

======================================
CREATE TABLE [dbo].[Groups] (
[GroupID] [int] IDENTITY (1, 1) NOT NULL ,
[Descript] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TS] [timestamp] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[GroupsMenuItems] (
[GroupMenuItemID] [int] IDENTITY (1, 1) NOT NULL ,
[MenuItemID] [int] NOT NULL ,
[GroupID] [int] NOT NULL ,
[TS] [timestamp] NULL ,
[AccessNone] [bit] NOT NULL ,
[AccessView] [bit] NOT NULL ,
[AccessEdit] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[GroupsUsers] (
[GroupUserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[GroupID] [int] NOT NULL ,
[TS] [timestamp] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MenuContexts] (
[MenuContextID] [int] IDENTITY (1, 1) NOT NULL ,
[ContextName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PeakMenuItemID] [int] NULL ,
[TS] [timestamp] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MenuItems] (
[MenuItemID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HooverText] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PageURLView] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PageURLEdit] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PageTitle] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PeakMenuItem] [bit] NOT NULL ,
[HelpURL] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PageNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TS] [timestamp] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MenuItemsRelations] (
[MenuItemRelationID] [int] IDENTITY (1, 1) NOT NULL ,
[MenuContextID] [int] NOT NULL ,
[ParentMenuItemsID] [int] NOT NULL ,
[ChildMenuItemID] [int] NOT NULL ,
[TS] [timestamp] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserMenuContexts] (
[UserMenuContextID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[MenuContextID] [int] NOT NULL ,
[TS] [timestamp] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[LoginID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TS] [timestamp] NULL ,
[Password] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StatusID] [int] NOT NULL ,
[SupervisorLoginID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FullName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NameFirst] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NameLast] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NameMiddle] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NameSuffix] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Initials] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Groups] WITH NOCHECK ADD
CONSTRAINT [PK_Groups] PRIMARY KEY CLUSTERED
(
[GroupID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[GroupsMenuItems] WITH NOCHECK ADD
CONSTRAINT [PK_GroupsMenuItems] PRIMARY KEY CLUSTERED
(
[GroupMenuItemID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[GroupsUsers] WITH NOCHECK ADD
CONSTRAINT [PK_GroupsUsers] PRIMARY KEY CLUSTERED
(
[GroupUserID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MenuContexts] WITH NOCHECK ADD
CONSTRAINT [PK_MenuContexts] PRIMARY KEY CLUSTERED
(
[MenuContextID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MenuItems] WITH NOCHECK ADD
CONSTRAINT [PK_MenuItems] PRIMARY KEY CLUSTERED
(
[MenuItemID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MenuItemsRelations] WITH NOCHECK ADD
CONSTRAINT [PK_MenuItemsRelations] PRIMARY KEY CLUSTERED
(
[MenuItemRelationID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[UserMenuContexts] WITH NOCHECK ADD
CONSTRAINT [PK_UserMenuContexts] PRIMARY KEY CLUSTERED
(
[UserMenuContextID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[GroupsMenuItems] ADD
CONSTRAINT [DF_GroupsMenuItems_AccessNone] DEFAULT (1) FOR [AccessNone],
CONSTRAINT [DF_GroupsMenuItems_AccessView] DEFAULT (0) FOR [AccessView],
CONSTRAINT [DF_GroupsMenuItems_AccessEdit] DEFAULT (0) FOR [AccessEdit]
GO

ALTER TABLE [dbo].[MenuItems] ADD
CONSTRAINT [DF_MenuItems_PeakMenuItem] DEFAULT (0) FOR [PeakMenuItem],
CONSTRAINT [DF_MenuItems_HelpURL] DEFAULT ('./help/Default.htm') FOR [HelpURL]
GO

ALTER TABLE [dbo].[MenuItemsRelations] ADD
CONSTRAINT [DF_MenuItemsRelations_MenuContextID] DEFAULT (1) FOR [MenuContextID]
GO

ALTER TABLE [dbo].[Users] ADD
CONSTRAINT [DF_Users_Password] DEFAULT ('P@ssw0rd') FOR [Password],
CONSTRAINT [DF_Users_StatusID] DEFAULT (1) FOR [StatusID]
GO

CREATE INDEX [IX_Users] ON [dbo].[Users]([LoginID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[GroupsMenuItems] ADD
CONSTRAINT [FK_GroupsMenuItems_Groups] FOREIGN KEY
(
[GroupID]
) REFERENCES [dbo].[Groups] (
[GroupID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_GroupsMenuItems_MenuItems] FOREIGN KEY
(
[MenuItemID]
) REFERENCES [dbo].[MenuItems] (
[MenuItemID]
) NOT FOR REPLICATION
GO

alter table [dbo].[GroupsMenuItems] nocheck constraint [FK_GroupsMenuItems_Groups]
GO

alter table [dbo].[GroupsMenuItems] nocheck constraint [FK_GroupsMenuItems_MenuItems]
GO

ALTER TABLE [dbo].[GroupsUsers] ADD
CONSTRAINT [FK_GroupsUsers_Groups] FOREIGN KEY
(
[GroupID]
) REFERENCES [dbo].[Groups] (
[GroupID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_GroupsUsers_Users] FOREIGN KEY
(
[UserID]
) REFERENCES [dbo].[Users] (
[UserID]
) NOT FOR REPLICATION
GO

alter table [dbo].[GroupsUsers] nocheck constraint [FK_GroupsUsers_Groups]
GO

alter table [dbo].[GroupsUsers] nocheck constraint [FK_GroupsUsers_Users]
GO

ALTER TABLE [dbo].[MenuItemsRelations] ADD
CONSTRAINT [FK_MenuItemsRelations_MenuContexts] FOREIGN KEY
(
[MenuContextID]
) REFERENCES [dbo].[MenuContexts] (
[MenuContextID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_MenuItemsRelations_MenuItems] FOREIGN KEY
(
[ParentMenuItemsID]
) REFERENCES [dbo].[MenuItems] (
[MenuItemID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_MenuItemsRelations_MenuItems1] FOREIGN KEY
(
[ChildMenuItemID]
) REFERENCES [dbo].[MenuItems] (
[MenuItemID]
) NOT FOR REPLICATION
GO

alter table [dbo].[MenuItemsRelations] nocheck constraint [FK_MenuItemsRelations_MenuContexts]
GO

alter table [dbo].[MenuItemsRelations] nocheck constraint [FK_MenuItemsRelations_MenuItems]
GO

alter table [dbo].[MenuItemsRelations] nocheck constraint [FK_MenuItemsRelations_MenuItems1]
GO

ALTER TABLE [dbo].[UserMenuContexts] ADD
CONSTRAINT [FK_UserMenuContexts_MenuContexts] FOREIGN KEY
(
[MenuContextID]
) REFERENCES [dbo].[MenuContexts] (
[MenuContextID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_UserMenuContexts_Users] FOREIGN KEY
(
[UserID]
) REFERENCES [dbo].[Users] (
[UserID]
) NOT FOR REPLICATION
GO

alter table [dbo].[UserMenuContexts] nocheck constraint [FK_UserMenuContexts_MenuContexts]
GO

alter table [dbo].[UserMenuContexts] nocheck constraint [FK_UserMenuContexts_Users]
GO

 

[Ovatvvon]

oh boy, i didn't realize there was so much to it. This is going to take me a bit to go over everything. I'll get back to ya. Thanks for posting everything!!

-Ovatvvon :-Q

 

[Ovatvvon]

This seems like a lot of stuff. I don't think I need n-depth capability that bad. If we go to a 4th level on the menu structure, I can always create another join manually into the view.

However, Do you (or does anyone else) know if there is a way to force the Category field to display regardless of whether it has relation or not? (With the current setup, it would only display if there were no sub-categories associated with it (i.e. no items that had a parent_id that point to it). Note: This is in reference to my second question at the top of this thread.

(FlashMerlot, I'm going to go ahead and give you a star, because I'm going to hang onto everything you posted for future reference, incase there is another situation where I may need something like that. Thank you for all your help in that area!)

-Ovatvvon :-Q