Forum OpenACS Development: nesting and list-builder

Posted by Janine Ohmer on
I've got some data I want to display thusly:


and so on.  In case it's not clear, the goal is to indent/nest the subprojects, to make it visually obvious that they are subprojects.

A subproject is identified in the data by having it's parent_id equal to the item_id of it's parent.  However, even top-level projects have parent_id filled in, so it can't be used to deduce level even though I know I will only have to display two levels and so could cheat. :)

Any suggestions as to how to accomplish this in list-builder?  I've looked at the api-docs, Jade's doc and the examples in the code, and I've searched the fora, but I haven't found anyone doing this.  Maybe I am attempting the impossible, in which case I'd love to know what the best approach would be.

Posted by Jade Rubick on
I think can use tree_level to deduce the level of in the tree. I think if you look way back in CVS for project-manager, there might be some code that did this.

You can have to tree_level(parent) - tree_level(child) in order to get the right level, I believe. Then you can use that to pad the spacing. I believe this was done in project-manager at one point.

Posted by Janine Ohmer on
Actually, I just figured out that the top-level parent is set to the root folder id, so I can use that to tell what level I'm at (as long as I'm sticking with only two levels).  But I have no clue whatsoever about how to use that information to set up list-builder to group and sort the rows properly, and I haven't been able to find an example of anything like this. Maybe I'm just being dense (wouldn't be the first time :).  Did Project Manager use list-builder to accomplish this?
Posted by Dave Bauer on
I used something like this:

display_template {<div style="text-indent: @folder_tree.level@em;">@folder_tree.label@</div>}

You could adjust the amount of the indent in the code block of the db_multirow.

You just need to use a tree query in oracle or pg to calculate the level relative to the project manager root folder I think.

Looking at some code on PG if you call tree_level() on the tree sortkey of the folder, and then tree_level() on each items tree sortkey, you can subject those to get the relative level of the items. I suspect you can do something similar with oracle, I think if you start with the folder as parent_id, oracle calculates the level for you.

Posted by Claudio Pasolini on
Janine, perhaps you can use list builder, but it's very easy with db_multirow and the multiple tag, as in this small example:


ad_page_contract {

    @author Claudio Pasolini
    @creation-date  12/04/2003

    @cvs-id index.tcl
} {
    {root_id "486"}
} -properties {

set page_title "Lista scripts"
set context [list "Lista scripts"]

# get tree_sortkey from root node
set root_sortkey [db_string root "select tree_sortkey from acs_objects where object_id = $root_id"]

db_multirow scripts query "
    select ci.item_id,
           repeat(' ', tree_level(sc.tree_sortkey) * 4) as indent,
           sc.title as name
    from   mis_scriptsx sc, cr_items ci
    where  sc.tree_sortkey between :root_sortkey and tree_right(:root_sortkey) and
           sc.revision_id = ci.live_revision
   order by sc.tree_sortkey



  <property name="title">@page_title;noquote@</property>
  <property name="context">@context;noquote@</property>


  <tr bgcolor=#6699cc>
    <th align=left>Descrizione</th>

  <multiple name=scripts>

    <if @scripts.rownum@ odd>
      <tr bgcolor=#eeeeee>
    <if @scripts.rownum@ even>
      <tr bgcolor=#ffffff>



Hope this helps.
Posted by Janine Ohmer on
Thanks, guys! I'm hoping to get it working with list builder, but it's good to know I have options if I can't get there.

My first task was to get the data out in the right order. I sort of got there by following the example of file-storage. I'm currently doing this in PG, though the final implementation will be in Oracle.

The query's where clause originally looked like this:

        p.project_id = i.live_revision and
        s.status_id           = p.status_id
        [template::list::filter_where_clauses -and -name projects]
        [template::list::orderby_clause -orderby -name projects]
I modified it to
        p.project_id = i.live_revision and
        s.status_id           = p.status_id
        and i.tree_sortkey between l.tree_sortkey and tree_right(l.tree_sortkey)
        and l.item_id = pm_project__get_root_folder (:package_id, 'f')
        [template::list::filter_where_clauses -and -name projects]
        order by i.tree_sortkey
This works, but has two problems:
  1. Although the various levels are sorted correctly, I can't figure out how to also get the items to sort alphabetically by title within each level
  2. I've lost the call to orderby_clause and have no idea how to get the order by tree_sortkey integrated with it. I've read all the docs I can find on list builder's orderby and I still don't understand it very well.
Posted by Janine Ohmer on
Ok, some progress.

I got the indentation working correctly, and I put the call to  orderby_clause back in by adding i.tree_sortkey to the values of orderby_desc and orderby_asc for project_name.  So far, so good.  All of the other orderbys are working again.  But at each level, the projects are sorted in creation order, not alphabetically by name, and nothing I do seems to fix that (while preserving the hierarchy).

The problem, of course, is that the tree_sortkey is different for every project, so doing a secondary sort by title has no effect.  If one could sort by level instead of tree_sortkey it would fix that problem, but then you lose the hierarchy.

After trying a number of approaches I am beginning to think that what I want to do is just not possible with the tools I have available, but I also have a feeling I'm overlooking something really dumb and obvious....

Posted by Claudio Pasolini on

I had a similar problem and AFAIK you can't in no way hack the standard query to sort the results alphabetically respecting the hierarchy.

To solve the problem I had to explode the hierarchy (a bill of materials in my case) level by level as per the following sample:

ad_page_contract {

  Explodes a bill of material related to the item passed as parameter.

  @author Claudio Pasolini
  @creation-date 2004-10-22
  @cvs-id explode.tcl

} {
    {validity_date ""}
    {search_date ""}
    {mode "edit"}

ah::script_init -script_id 44761

# check that item_id is not null
ah::key_selected_p -key $item_id

set page_title "Esplosione scalare"
set context [list [list list {Lista prodotti}] $page_title]

# get parent item data
mis::product::get -item_id $item_id -array prod
set parent_code        $prod(product_code)
set parent_description $prod(title)

set today [db_string query "select current_date"]

set today [db_string query "select current_date"]
if {[string equal $validity_date ""]} {
    set validity_date $today

if {![string equal $search_date ""]} {
    regexp {([0-9]*)/([0-9]*)/([0-9]*)} $search_date match dd mm yyyy
    set validity_date ${yyyy}-${mm}-${dd}

set base_url [ad_conn url]

# Prepares bom list (indented explosion)

template::list::create \
    -name bom \
    -multirow bom \
    -elements {
        edit {
            link_url_col edit_url
            display_template {<img src="/resources/acs-subsite/Edit16.gif" width="16" height="16" b\
            link_html {title "Modifica bom"}
            sub_class narrow
        init_date {
            label "Dt. inizio"
        end_date {
            label "Dt. fine"
        level {
            label "Lvl."
        product_code {
            label "Codice"
            link_url_col bom_url
            link_html {title "Distinta Base"}
        product_description {
            label "Descrizione prodotto"
        qty_pretty {
            label "Q.ta"
            html {align right}
        um_code {
            label "UM"

        delete {
            link_url_col delete_url
            display_template {<img src="/resources/acs-subsite/Delete16.gif" width="16" height="16"\
            link_html {title "Cancella bom" onClick "return(confirm('Confermi la cancellazione?'));\
            sub_class narrow
    } \
    -orderby {
        default_value seq,asc
        seq {
            label "Seq. e codice"
            orderby "seq, product_code"
        product_code {
            label "Codice"
            orderby product_code
    } \
    -filters {
        item_id {
            hide_p 1
            values {$item_id $item_id}
            where_clause {1 = 1}
        validity_date {
            label "Data validità "
            values {{"Tutte" "9999-12-31"} [list "Oggi $today"]}
            where_clause {((b.init_date <= :validity_date and
                          (b.end_date  >= :validity_date or
                           b.end_date is null)) or
                          '$validity_date' = '9999-12-31')}
            default_value "$today"

# save current url vars for future reuse
set url_vars [export_ns_set_vars]

# defines multirow
multirow create bom edit_url bom_url delete_url init_date end_date level product_code product_descr\
iption um_code qty_pretty

mis::bom::explode \
    -item_id        $item_id \
    -level          0 \
    -multirow       bom \
    -validity_date  $validity_date
The last procedure is called recursively:
ad_proc -public mis::bom::explode {
    {-level "0"}
} {
    @param item_id        The product to explode
    @param level          The depth level of this item in the BOM
    @param multirow       The name of a multirow data structure to fill.
                          Note that this param is passed by reference and
                          upvared in the caller scope.
    @param validity_date  Used to filter bom lines
} {
    upvar 1 $multirow bom
    incr level

    if {$level > [parameter::get -parameter max_bom_level]} {
        ad_return_complaint 1 "
        E' stato superato il massimo livello consentito in una distinta base:
        probabilmente è stato creato un loop, che deve essere rimosso
        utilizzando l'esplosione a livello singolo."
        return 0

    # to avoid excessive use of db handles we release the handle
    # immediately after use and store the results in a list of lists
    # using then a foreach instead of db_foreach to loop
    set partlist [db_list_of_lists query "
        select b.bom_id, b.child_id,
               to_char(b.init_date, 'DD/MM/YYYY') as init_date,
               to_char(b.end_date,  'DD/MM/YYYY') as end_date,
               repeat('.', $level * 4) || p.product_code as product_code,
               p.title as product_description,
               ah_edit_num(qty, 2) as qty_pretty
        from  mis_bom b, mis_active_products p, mis_um u
        where  b.parent_id = :item_id and
               p.item_id   = b.child_id and
               p.um_id     = u.um_id
        [template::list::filter_where_clauses -name bom -and]
        [template::list::orderby_clause       -name bom -orderby]"]


    foreach part $partlist {

        # decode the list
        set bom_id              [lindex $part 0]
        set child_id            [lindex $part 1]
        set init_date           [lindex $part 2]
        set end_date            [lindex $part 3]
        set product_code        [lindex $part 4]
        set product_description [lindex $part 5]
        set um_code             [lindex $part 6]
        set qty_pretty          [lindex $part 7]

        set edit_url   [export_vars -base "bom" {item_id bom_id}]
        set delete_url [export_vars -base "delete" {item_id bom_id}]
        set bom_url    [export_vars -base "bom?item_id=$child_id" {}]

        template::multirow append bom $edit_url $bom_url $delete_url $init_date $end_date $level $p\
roduct_code $product_description $um_code $qty_pretty

        # call recursively
        explode \
            -item_id       $child_id \
            -level         $level \
            -multirow      multirow \
            -validity_date $validity_date
Posted by Don Baccus on
Claudio is correct, you can not maintain the tree hierarchy and alphabetize in a single query.  Same's true of CONNECT BY in Oracle.  Just can't be done ...
Posted by Malte Sussdorff on
If this is possible in your scope of work, could you implement it in a general way for listbuilder, giving it the column name of the parent_id as a clue. This way, you can call listbuilder and give it the parent_id and listbuilder automatically figures out which lines will be indented and how to sort the items accordingly. It might be nicer (and faster ?) to presort in Postgres, but maybe it is more convenient to do it in TCL. I'm pretty sure there already exist some algorithms to achieve this kind of sorting. This way you can just leave your queries as is (though they have to retrieve the parent_id) and listbuilder sorts them in TCL automatically for you (including multiple levels of trees). Another switch would be great for color schemes and from which level on to use the same color for all sublevels.
Posted by Janine Ohmer on
Claudio, thanks very much for posting your work.  This will help me out a lot.
Posted by Janine Ohmer on
Oops, just noticed Malte's post.

Considering I just barely grok list builder at this point, I don't think I should be hacking on it.  I might accidentally do something like rewrite the UI to make it actually easy for mere mortals to use. ;)  I do intend to put my work into project manager, provided Jade agrees, and if someone who is more comfortable with list builder internals, and has more time, wants to take it and add it in I'd be happy to answer questions.  But I can't do it myself right now.

Posted by Andrew Grumet on
<blockquote>Claudio is correct, you can not maintain the tree
hierarchy and alphabetize in a single query.  Same's true
of CONNECT BY in Oracle.  Just can't be done ...

Here's a "cheat":  load the three into memory and sort it there.  I think used the tree package ( to do this once.  It wasn't fast, but it worked.

Posted by Dirk Gomez on
That's not try anymore for Oracle >= 9i. There is an order by siblings (and some other order by goodies).