Re: Proper siblings sorting in nested sets model

"Mikito Harakiri" <mikharakiri_at_iahu.com> wrote in message news:<hvAdc.12$zY.111_at_news.oracle.com>...
> You have to build Materialized Path with the column of your choice and sort
> by it. For employee name you'll get strings like this:
>
> KING
> KING.BLAKE
> KING.BLAKE.ALLEN
>
> You can build such strings, though, only if your database supports LIST
> aggregate function. Google any of the following: "Litwin LIST aggregate",
> "iAnywhere LIST". Alternatively, if your database supports user-defined
> aggregates, then you can code this aggregate function. Yet another
> alternative is to program LIST aggregate function on database application
> level if your database supports object extensions/collections. Be careful,
> since string concatenation is not commutative, so you have to warry about
> the order.
>
> Assuming that you have LIST aggregate, then find path from the root to the
> leaf in the subquery within the "select" clause (does your database support
> subqueries within the "select" clause?) for each leaf and aggregate those
> node names with the LIST function.

hm...it sounds interesting, thanks!

But first, back to earth - we have mysql 4.1 :( LIST is surely not supported and i'm not sure if it's possible to program such an UDF, i'll try to find out. At least it supports subqueries.

Ok, while i'm on the way to discover if mysql fits the task, i've got another question. Example with employers is quite artificial. In the reality we may have several sort parameters. Here's what result we need to achieve(detailed explanation below):

LEVEL IDENTIFIER           CLASS             CLASS_ORDR   CLASS_ID
----- -------------------- ----------        -----------  --------
    1 KING                 CONTAINER         0            1
    2   A-FILES            FILES_FOLDER      0            2
    3     A-FILES          FILES_FOLDER      0            2
    3     B-FILES          FILES_FOLDER      0            2
    3     F1               FILE              1            3
    3     F2               FILE              1            3
    3     F3               FILE              1            3
    2   B-DOCS             DOCUMENTS_FOLDER  0            7
    3     D1               DOCUMENT          1            4
    2   C-IMAGES           IMAGES_FOLDER     0            5 
    3     A-IMAGES         IMAGES_FOLDER     0            5 
    4       SMITH          IMAGE             1            6
    3     B-IMAGES         IMAGES_FOLDER     0            5
    4       ADAMS          IMAGE             1            6

So siblings are ordered by CLASS_ORDR and by IDENTIFIER. (in short it looks just like a file system in your Total Commander, Midnight Commander, FAR etc.)

We can achieve this by sorting database result with script, but it's not a very elegant solution, moreover it's very slow. I don't think it's even possible with LIST function...

Of course the task may be simplified: not to retrieve the whole tree but only collapsed children of the certain node, simple list:

LEVEL IDENTIFIER           CLASS             CLASS_ORDR   CLASS_ID
----- -------------------- ----------        -----------  --------
    2   A-FILES            FILES_FOLDER      0            2
    2   B-DOCS             DOCUMENTS_FOLDER  0            7
    2   C-IMAGES           IMAGES_FOLDER     0            5 

But why would i post in this group then? :) Received on Sat Apr 10 2004 - 11:13:58 CEST