Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2001
    Posts
    12

    Unanswered: Recursive aggregation query

    Hi!

    I have table like this, which makes dynamic tree.

    Id Parent Cost
    1 0 10
    2 1 20
    3 1 30
    4 2 40
    5 4 60

    Now I would like to calculate for example the total cost of the id 2 (20+40+60=120). How to make the sql statement, which aggregate all the cost of selected id and also from the child objects ? How about grouping by the level of parent?

    -Petri-

  2. #2
    Join Date
    Sep 2002
    Posts
    8

    Lightbulb solution: use php to sort info into tree form

    ok well here is the solution to this i have made....

    1) I don't know how to make SQL return the tree type relationship you want, and i don't think it can...

    2) I decided to implement this in php with this badly named function

    3) this function assumes input like this. each array item represents a row of the database, and each row is an array hashed with the column names. I user the PEAR DB object to pull out the table info like this: you could easily rewrite this function to use another form of input:

    array(
    array( 'id' => int, 'name' => string, 'parent' => int),
    array( 'id' => int, 'name' => string, 'parent' => int),
    array( 'id' => int, 'name' => string, 'parent' => int),
    array( 'id' => int, 'name' => string, 'parent' => int)
    )

    5) the result is of a form which you can easily step through to generate a tree
    $categories[ $id ] = array( 'name', 'parent', 'children', 'descendants', 'ancestors' );


    6) you can easliy expand this function to track more than just the name.

    7) you can easily modify this function to calculate info by stepping through once rather than saving all info as an array

    <?php
    function getCategoryHashesFromSqlResults( $results )
    {
    /// top category (not in database so we make it here)
    $categories = array( NULL => array( "id" => NULL,
    "name" => '_TOP_',
    "parent" => NULL,
    "children" => array(),
    "descendents" => array(),
    "ancestors" => array() ) );

    foreach ( $results as $result ) {
    $category_id = $result['id'];
    $parent_id = $result['parent'];

    if ( !isset($categories[$category_id]) ) {
    $categories[$category_id] = array( "id" => $category_id,
    "name" => $result['name'],
    "parent" => NULL,
    "children" => array(),
    "descendants" => array(),
    "ancestors" => array() );
    } elseif ( !isset($categories[$category_id]['name']) ) {
    $categories[$category_id]['name'] = $result['name'];
    }

    if ( !isset($categories[$parent]) ) {
    $categories[$parent_id] = array( "id" => $parent_id,
    "name" => NULL,
    "parent" => NULL,
    "children" => array(),
    "descendants" => array(),
    "ancestors" => array() );
    }

    /// add self to children list of parent
    $categories[$parent_id]['children'][] = $category_id;

    /// add parent to self as parent
    $categories[$category_id]['parent'] = $parent_id;

    }

    while ( list($category_id,$category) = each($categories) ) {

    if ( $category_id == NULL ) {
    continue;
    }

    /// add self to descendant list of all ancestors
    $parent_id = $categories[$category_id]['parent'];

    while ( $parent_id !== NULL ) {
    /// add self to descendant list of parent
    $categories[$parent]['descendants'][] = $category_id;

    /// add parent to ancestor list of self
    $categories[$category_id]['ancestors'][] = $parent_id;

    /// get next parent
    $parent_id = $categories[$parent_id]['parent'];
    }

    $categories[NULL]['descendants'][] = $category_id;

    }

    return $categories;
    }

    ?>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •