Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
settinUpShop
Starting Member
28 Posts |
 Posted - 2003-11-04 : 17:12:50
|
| I've been carefully studying this article http://www.sqlteam.com/item.asp?ItemID=8866 by rob, and trying to figure out how I can extend this concept one step further so that it will allow me to modify the sort order of the nodes. My purpose in this pursuit is to create a heirachy for the web pages in a content management utility I'm creating. Right now I have a table called PageInfo which has a ParentPageID field which has the PageInfoID of the page's parent page. I also have a "Sort_Order" field which I use to influence the order in which the menu items will be displayed. The agency model i'm using here works but the code I use in my ASP to output the levels is really complex, because I need to also account for the sort order.Any suggestions, on how I might be able to do this? I know it's got to be possible. Just look at your the Favorites in your browser. They're in a heirachical structure with links and folders that can be ordered in various ways.Anyway, thanks for any help. I'm trying to get my brain around this, but it just isn't quite stretching far enough. :) |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-11-04 : 17:28:17
|
Hi thereI spent a bunch of time with this technique a while back and sorting drove me pretty mad too  The best way I found is to have the linage table seeded in the right order. So I had a proc that deleted and reseeded data in the table when a record was added or updated. It works quickly enough, and once the data is in the right order, it comes out that way.Damian |
 |
|
|
settinUpShop
Starting Member
28 Posts |
Posted - 2003-11-04 : 18:42:57
|
| Thanks for your reply Damian. Were you able to alter the sort order using the technique you used? The issue I've been running up against is that in Rob's solution the Node value is used as the defacto sort order value in the lineage. So lets say that Node A and B share the same parent. Node A is created with a Node value of 130, and Node B has a value of 150, Node A will always come before Node B, even if I give Nobe B a Sort_Order number which would place it before Node A.The only solution I can conceive of would be to return all the nodes according to their lineage as you suggested. And then somehow reordering siblings according to the sort order values assigned to them. The catch is that you need to also reorder each siblings children so they don't become misplaced. This type of reording would need to be performed down through all the various generations or layers of nodes. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-11-04 : 18:53:08
|
| What I am saying, is that you create lineage in the order you want to sort by. So if you insert a record that needs to go above the others you need to change the lineage table. The easiest way is to delete and reinsert.This method doesn't make it possible to have a dynamic sort order though.Damian |
|
|
|
settinUpShop
Starting Member
28 Posts |
Posted - 2003-11-04 : 19:00:51
|
| I see what you're saying now. The dynamic sort order, that's a good way of putting it, is what I'm after.How is this done, then? There must be a way, since this kind of thing is acccomplished with file directories. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-11-04 : 19:21:39
|
| If you do a self-join and ORDER BY the parent's lineage first, then by the column you want to sort, it should work:SELECT E.Name AS NameFROM Employees E INNER JOIN Tree T ON E.EmployeeID=T.EmployeeIDINNER JOIN Employees P ON T.EmployeeID=P.BossIDORDER BY P.Lineage, E.NameThis example would work for the one I had in the article, it may or may not work correctly with your data though. I also suggest trying what Damian mentioned, rebuilding the lineage in the order you want. It seems kinda dumb but unless you have very large tables to model you'd be surprised at how quickly it goes. You can also try building a temporary tree table that creates its own lineage for dynamic ordering. |
|
|
|
|
|
|
|
|
tree heirarchy with modifiable sort order