Site Map Structure 1

[bdichiara]

We have an internal website and I would like to take all the information in our database and output it into a simple tree structure. I've had two ideas on how to do this, but was wondering which would prove to be the better method, or if there was a better way.

The first way was to add 1 additional field to the database named "sub_of" and simply put the ID of the item above it in the heirachy, and if it is the top level, it simply doesn't have a value for "sub_of". Secondly, I would need to add another field called "alias_of" and it would basically be the same as another database record (as far as name, information etc.) however it would be under a second category in the structure.

Example:
HR > Forms > 401K
Forms > HR > 401K

The second item would just alias the first one, so when I made a change to the entry for 401K (say changed the URL to the 401K form), it would automatically change all of it's aliases.

--------------------

The other way would be using a format such as:
HR::Forms
Departments:hone Number::Main Office



I'm not sure the benefits/disadvantages to each, but i'm just looking for some feedback on the best way to tackle this.

_______________
_brian.

 

[bdichiara]

Well, after thinking about this for a while, the easiest (and seems like most efficient) way of doing this would be to use the method of storing the PARENT id of the item in the "sub_of" field.

My only question now is, how do I run a recursive query that pulls all of these items in the right order. i.e.:

Root  |  sub_of (NULL)
  Main Topic 1  |  sub_of (Root)
  Main Topic 2  |  sub_of (Root)
    Sub Topic 1a  |  sub_of (Main Topic 2)
    Sub Topic 2a  |  sub_of (Main Topic 2)
  Main Topic 3  |  sub_of (Root)
    Sub Topic 1b  |  sub_of (Main Topic 3)
    Sub Topic 2b  |  sub_of (Main Topic 3)

So how would I accomplish this where:
1. items just under Root (items with sub_of = 1) come out in a custom order (i think rudy-r937 already showed me how to do this - see code.

order
    by case when type='g' then 1
            when type='f' then 2
            when type='h' then 3
                 else 4 end
     , etc

2. If its a sub-item under another one, it's value of "sub_of" is not null, 0 or 1
3. I would like all the sub-items to be in alphabetical order (field name is description)

_______________
_brian.

 

[bdichiara]

Oh yeah, i forgot about ALIAS_OF

If it has a value for ALIAS_OF, it will need to pull some fields from a different id.

So if item 6 is alias of item 3, then item 6 needs to use the url for item 3, but still maintain it's originality because it's somewhere else in the Navigation with it's own Description.

_______________
_brian.

 

[r937]

i did not reply to this question earlier because your mention of aliases threw me off the trail

perhaps this article will help you --

Categories and Subcategories

http://sqllessons.com/categories.html

i still don't understand how you want to address the issue of aliases

r937.com | rudy.ca

 

[bdichiara]

Well, an alias record is to help us because our navigation is a bit "exhaustive" and sometimes we have links in multiple locations in our navigation. So I would like to be able to edit one of them and it change all of its aliases. It only changes the URL of the link. It doesn't change the description, or the parent id. That way anytime you click 401K, it always goes to the same place without us having to change it 10 times.

_______________
_brian.

 

[bdichiara]

That link you sent me was great, however, I don't think it explains how to have an unlimited number of sub-items.
Like:
Root > Main > Sub > Topic > Category > Item > Variation > etc.

_______________
_brian.

 

[r937]

the article discusses "unlimited" levels by noting that if you go more than X levels deep, you probably have an information architecture problem

in other words, don't do that ;-)

but if you have

Root > Main > Sub > Topic > Category > Item > Variation > etc.

then that's only 8 levels

queries with up to 15 LEFT OUTER JOINs shold work with no appreciable performance problem

if you want to go for the recursive solution, be my guest, but it will not be as efficient

and remember, in order to understand recursion, you must first understand recursion

r937.com | rudy.ca

 

[bdichiara]

Ok, that makes sense. I trust you about the 15, so I went ahead and went 8 out. This is my query:

SELECT root.description as root_name
     , down1.description as down1_name
     , down2.description as down2_name
     , down3.description as down3_name
	 , down4.description as down4_name
     , down5.description as down5_name
     , down6.description as down6_name
     , down7.description as down7_name
     , down8.description as down8_name
  from links as root
left outer
  join links as down1
    on down1.sub_of = root.linkid
left outer
  join links as down2
    on down2.sub_of = down1.linkid
left outer
  join links as down3
    on down3.sub_of = down2.linkid
left outer
  join links as down4
    on down4.sub_of = down3.linkid
left outer
  join links as down5
    on down5.sub_of = down4.linkid
left outer
  join links as down6
    on down6.sub_of = down5.linkid
left outer
  join links as down7
    on down7.sub_of = down6.linkid
left outer
  join links as down8
    on down8.sub_of = down7.linkid
 where root.sub_of = 0 AND root.sub_of IS NOT NULL AND root.deleted <> '1' AND down1.deleted <> '1' AND down2.deleted <> '1' AND down3.deleted <> '1' AND down4.deleted <> '1' AND down5.deleted <> '1' AND down6.deleted <> '1' AND down7.deleted <> '1' AND down8.deleted <> '1'
order 
    by case when description='Contact Information' then 1
            when description='Human Resources' then 2
            when description='Telco Forms' then 3
			when description='Marketing/Events/News' then 4
			when description='Operations' then 5
			when description='Rates' then 6
			when description='ATM/Service Center Info' then 7
			when description='Websites' then 8
			when description='Auto Info' then 9
			when description='New Account' then 10
			when description='Sales & Service' then 11
                 else 12 end 
     , down1_name 
     , down2_name 
     , down3_name 
     , down4_name 
     , down5_name 
     , down6_name 
     , down7_name 
     , down8_name

I must have done something wrong, though because I get an error:

Column 'description' in order clause is ambiguous

Thanks for the help rudy.

_______________
_brian.

 

[r937]

in the query, there are 9 columns called description

you need to qualify which one

probably down1_name

r937.com | rudy.ca

 

[bdichiara]

oh, yeah, right.. thanks.

_______________
_brian.

 

[r937]

actually, probably root_name

r937.com | rudy.ca

 

[bdichiara]

yep, that's what it was.

OK, now that I can get all this in a semi-orderly fashion, how would i go about filtering. I can barely comprehend how this works, there are so many dimensions... I'm trying to filter out any of the rows where deleted <> '1' first. That should clean some of this up.

Using WHERE as shown above, i get no results.

_______________
_brian.

 

[r937]

root.sub_of should actually be NULL, but i suppose your use of 0 is okay too

but if you have

where root.sub_of = 0

then you don't need

AND root.sub_of IS NOT NULL

now, as to why your query returns nothing, that's easy -- the other conditions in your WHERE clause all belong in the ON clauses of their respective joins

left outer
  join links as down1
    on down1.sub_of = root.linkid
   AND down1.deleted <> 1  
left outer
  join links as down2
    on down2.sub_of = down1.linkid
   AND down2.deleted <> 1
...

r937.com | rudy.ca

 

[bdichiara]

Excellent. Now, do you have any clue how to help me implement the alias_of part? Some of my aliases are just regular links, and some are whole sections. Anything to get me started would help. Thanks.

_______________
_brian.

 

[r937]

sorry, i'd have to know a lot more about your aliases before i could offer a suggestion

r937.com | rudy.ca

 

[bdichiara]

My table is setup like this:

[b]Links[/b]

id description       sub_of  alias_of  url          deleted
------------------------------------------------------------
1  Item 1            5       NULL      blah1.html   0
2  Item 2            5       NULL      blah2.html   0
3  Item 3            5       NULL      blah3.html   0
4  Item One          9       1         blah1.html   0
5  Item 4            0       NULL      blah4.html   0
6  Item 5            5       NULL      blah5.html   0
7  Item 6            9       NULL      NULL         0
8  Item 7            6       NULL      blah6.html   1
9  Item 8            6       NULL      blah7.html   0
10 Item Three        9       3         blah3.html   0
11 Item 10           9       NULL      blah8.html   0
12 Item 11           5       6         NULL         0

In this example, ID-4 is an Alias of ID-1. So their URLs are the same. And when I update ID-1's URL, it updates ID-4 as well. Also, ID-12 is an alias of ID-6, and ID-8 and ID-9 are "SUB_OF" ID-6, so ID-12 would also have ID-8 and ID-9 under it.

Let me know if I need to explain better. Anyway, it just helps when we when to put the same link in multiple places and don't have to worry about updating every instance of it.

_______________
_brian.

 

[bdichiara]

The Above would Output:

Item 4
   Item 1
   Item 2
   Item 3
   Item 5
      Item 7
      Item 8
         Item 6
         Item 10
         Item One
         Item Three
   Item 11
      Item 7
      Item 8
         Item 6
         Item 10
         Item One
         Item Three

Not a very practical example, but you get the picture...

_______________
_brian.

 

[bdichiara]

Oops, in the example above, Item 7 wouldn't be there, cause it's deleted.

_______________
_brian.

 

[r937]

i can't make sense of your examples, sorry

also, if ID-4 is an Alias of ID-1, shouldn't ID-1 be an Alias of ID-4 too?



r937.com | rudy.ca

 

[bdichiara]

No. The way it's setup is I have one master entry and when I display a list of links, any of them that are the alias of another link gets hidden. (they can be deleted/changed on a separate page).

That way, ID-4, ID-200, and ID-416 can all be alias_of ID-1. So if ID-1 is changed, so is ID-4, 200 and 416

_______________
_brian.