About treeview data display

imran_python · #1 (**permalink**) December 23rd, 2008, 05:39 AM

what kind of procedure is to maintain to display data as treeview & how will be the design of database?

plz,can any one give me an example?

Imar · #2 (**permalink**) December 23rd, 2008, 11:51 AM

Hi there,

One way is to store tree data in a tabe with a self join. E.g.:

Code:

 
Id        Name            ParentId
1         Root            <null>
2         Child 1         1
3         Child 2         1
4         Grand Child 1   3

The ParentId column then refers to the Id column of the same table.

This way, you can always see to what parent an item belongs.

You can learn more about trees in ASP here:

http://www.codeproject.com/KB/asp/treecontrol.aspx
http://www.planet-source-code.com/vb...txtCodeId=6411

Hope this helps,

Imar

Old Pedant · #3 (**permalink**) December 23rd, 2008, 05:55 PM

The problem with storing data such as Imar shows is that retrieving the data in tree-order via a SQL query is nearly impossible if the "depth" of the tree is unknown and/or large.

You have to JOIN the table to itself as many times are there are levels in the tree. One way to do this is via a UNION:

Code:

SELECT id AS level1, 0 AS level2, 0 AS level3, name
FROM table WHERE parentID = 0
UNION
SELECT T1.id AS level1, T2.id AS level2, 0 AS level3, T2.name
FROM table AS T1, table AS T2
WHERE T1.parentID = 0 AND T2.parentID = T1.id
UNION
SELECT T1.id AS level1, T2.id AS level2, T3.id AS level3, T3.name
FROM table AS T1, table AS T2, table AS T3
WHERE T1.parentID = 0 AND T2.parentID = T1.id AND T3.parentID = T2.id
ORDER BY level1, level2, level3

And that works. But what about if there might be 17 levels in the tree?

Another way is to better organize the DB.

Something like this:

Code:

Id        Name            ParentId   Path
1         Root            0          0001-
2         Child 1         1          0001-0002-
3         Child 2         1          0001-0003-
4         GC 1 of C2      3          0001-0003-0004-
5         GC 1 of C1      2          0001-0002-0005-
6         Child 3         1          0001-0006-
7         GC 2 of C1      2          0001-0002-0007-
8         GC 1 Of C3      6          0001-0006-0008-
9         GGC1/GC1/C1     5          0001-0002-0005-0009

And now, if you simply do

Code:

SELECT * FROM table ORDER BY path

it all comes out in ready-to-use-in-your-tree order, thus:

Code:

Id        Name            ParentId   Path
1         Root            0          0001-
2         Child 1         1          0001-0002-
5         GC 1 of C1      2          0001-0002-0005-
9         GGC1/GC1/C1     5          0001-0002-0005-0009
7         GC 2 of C1      2          0001-0002-0007-
3         Child 2         1          0001-0003-
4         GC 1 of C2      3          0001-0003-0004-
6         Child 3         1          0001-0006-
8         GC 1 Of C3      6          0001-0006-0008-

It's a bit more work to put the PATH value into the records as you build the table (you need to convert the id's to strings and pad them out to all the same length, as I show there) but it's well worth the trouble for large trees or trees with a lot of depth.

Imar · #4 (**permalink**) December 24th, 2008, 12:22 PM

With the new Common Table Expressions in SQL Server 2005 and later, it's a lot easier to get complex trees from SQL Server.

But I agree that your solution makes it a lot easier to get your trees available in code.

Cheers,

Imar

Old Pedant · #5 (**permalink**) December 24th, 2008, 09:36 PM

Yes, I keep forgetting about the availability of recursion in SQL Server 2005 and later. So far as I know, that's the only DB that supports such. Do you know of any other?

FWIW, the system I show works in Access and MySQL and Oracle, and of course SQL Server 2000. Even with Access, with a bit of cleverness in building the PATH value (not as simple as I thow there, that is), you can go 63 levels deep in a table with up to 16 million records. [Use MOD 64 encoding with no delimiters, so 4 bytes per level, so even 255 character limit in Access gets 63 levels. Easy to change to Unicode and get many more than that.]

Imar · #6 (**permalink**) December 25th, 2008, 05:35 AM

I don't know of other DBMS's that can do this, but that doesn't necessarily mean they don't exist... ;-)

Imar

Need to download code?

Navigation