in reply to Re^2: message board thread quandary
in thread message board thread quandary

It is one way to do it. With the approach you suggested, it means going through this process:
  1. fetch an entry
  2. find any children
  3. for each child, call step 1
This requires multiple calls to the DB, which is likely to be slower than issuing one single call to find all descendants. The process of sending the query to the DB, having the DB parse the SQL, run the query, return the results, and parsing the results has to happen many more times (depending on the nature of your data) than with the structure I suggested.

It, too, is not without its problems though. For instance, keeping the two tables in sync can be tricky. The Nested Set Model of Trees describes a way to do it in a single table, and it is very fast for reads. But inserting a node into the tree can involve a large number of updates, so, depending on your data, it may not be applicable.

I think that there is no perfect solution - each one has its pros and cons, and has to be considered in the context of the type of data (and use of that data) that you have.

Clint

Replies are listed 'Best First'.
Re^4: message board thread quandary
by shmem (Chancellor) on Sep 02, 2007 at 15:41 UTC
    Your comparison is incomplete.
    It is one way to do it. With the approach you suggested, it means going through this process:
    1. fetch an entry
    2. find any children
    3. for each child, call step 1

    And with an additional table, the steps are?

    1. fetch an entry
    2. fetch all descendants of that entry (gives a collection)
    3. build a tree doing ... what exactly? Probably a select to get its immediate parent.

    There. :-)

    update - having read the Nested Set Model of Trees - well, that's one way to do it. You have to jump through hoops still to get your data into a tree structure suitable to be displayed in a TT template. But anyways, trading simplicity and additional database calls for just one call (which I doubt would satisfy the OP's requirements) and more code complexity is something I would do only if I had proven the fact that multiple queries are the bottleneck in that application.

    update 2: Thinking about it, building a tree given the parent_id in each record would probably be pretty simple, so there's no point discussing your improvement.

    cheers,

    --shmem 

    _($_=" "x(1<<5)."?\n".q·/)Oo.  G°\        /
                              /\_¯/(q    /
----------------------------  \__(m.====·.(_("always off the crowd"))."·
");sub _{s./.($e="'Itrs `mnsgdq Gdbj O`qkdq")=~y/"-y/#-z/;$e.e && print}
[reply]
      Good point :)

      However, by selecting all the descendants at once, you do have all the information to build a tree within your program. I'm guessing here, but I would think that doing it in memory would still be faster than doing it with multiple selects.

      I would concede to those with more experience though :)

      Clint

      Update I'm assuming that by selecting all of the objects, you are also receiving the parent_id of each object, which would give you the information to build the tree.

[reply]

In Section Seekers of Perl Wisdom