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:
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
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:
Code:
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