Janine,
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
} {
item_id:integer
orderby:optional
{validity_date ""}
{search_date ""}
is_active_p:optional
{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\
order="0">}
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"\
border="0">}
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 {
-item_id:required
{-level "0"}
-multirow:required
-validity_date:required
} {
@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,
u.um_code,
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]"]
db_release_unused_handles
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
}
}
