in reply to Re: Trees in SQL, adjacency lists and sorting.
in thread Trees in SQL, adjacency lists and sorting.

The problem with this is that you can't do a single select query to get an entire branch of the tree in the correct order. You have to write something like a recursive function and you end up with one query per level of the tree.

Someone above mentioned "nested set" - this is what I use and it's great for quick lookups, at the cost of expensive (relatively) inserts. I've never had a database big enough where inserts have been a problem.
  • Comment on Re^2: Trees in SQL, adjacency lists and sorting.

Replies are listed 'Best First'.
Re^3: Trees in SQL, adjacency lists and sorting.
by demerphq (Chancellor) on Jan 10, 2006 at 16:27 UTC

    Thats not really true. Assuming your nodes can be ordered, and assuming you store a root id, you grab everything from the tree on or later to the node you are interested in. Then filter out the noncompliant nodes when you read them. One query sufficies then. Admittedly at the cost of some client side processing, but if that isnt a problem then it should be fine.

    For the record this exactly what is doe in Recently Added Threads. We grab all the relevent nodes in two queries normally. (Roots then children as our roots arent the same type as the children).

    ---
    $world=~s/war/peace/g

[reply]
Re^3: Trees in SQL, adjacency lists and sorting.
by DungeonKeeper (Novice) on Jan 09, 2006 at 10:01 UTC
    You certainly can't do that with the single table solution either. Such a branch is iterative and therefore cannot be expressed as an RSE whichever RDBMS solution you choose.

    Everything but the troll

[reply]
      I beg to differ. In fact, here is an article about it: click
[reply]
        About twelve years ago I had a similar idea to the one in the article for a multiple concurrent junior-senior structure but it isn't as simple as the article pretends. A whole family of stored procedures are in fact needed to support the complexity the writer either isn't seeing or isn't presenting and the developer is more likely to be told to come up with something simpler, especially if the DBMS doesn't have stored procedures.

        Update: the complexity I refer to occurs when the structure has to support insert update and delete by concurrent users.

        Everything but the troll

[reply]

In Section Seekers of Perl Wisdom