Forum OpenACS Q&A: Instructions on how to sort in the listbuilder - part II

How to add sorting to listbuilder - Part 2

Jade kindly brought everything together on how to work with the list builder that is generated from a database query

This little how to describes how to sort lists that are not database driven

Adding orderby to ad_page_contract

ad_page_contract {

 } {
    orderby:optional
}

Indicating which colums should get sorted

-orderby {
        element_name {orderby column_name}
        ...
    }

Sort the multirow

if { [exists_and_not_null orderby] } {
    regexp {([^,]*),(.*)} $orderby match column order

    if { $order == "asc" } {
        template::multirow sort emails -increasing $column
    } elseif { $order == "desc" } {
        template::multirow sort emails -decreasing $column
    }

}

Note: Behind the scenes lsort is used. You can add any swith that lsort understands.

References

1. http://rubick.com:8002/openacs/list-builder
2. https://openacs.org/forums/message-view?message_id=115009
3. /api-doc/proc-view?proc=template::multirow

Use the procedure template::list::orderby_clause to generate an order by clause to add to a query.

Most instances just call the procedure directly from the XQL file in the query text.

So you would have

select * from my_table
[template::list::orderby_clause -name my_list -orderby]

as your query.

I suspect this will be much faster than sorting the rows with lsort in tcl.

It also simplifies your code as you don't have to check for asc/desc at all.

I am pretty sure this has been addressed before in the forums. Now, if your multirow is not generated from the database, it looks like a good solution.

I'm going to take my chance in this forum to point a possible bug in list builder. When you try to create a filter using the template::list::filter_where_clauses without the -and switch, the query has a formation problem. Instead of putting the where instruction before the clause, it's passing directly the clause, wich throws an error in the query. For example:

select * from my_table
[template::list::filter_where_clauses -name "list"]

Let's presume your where clause is something like:

" state = $state_var"

The final query will be:

select * from my_table
state = $state_var

The procedure makes the select to be broken if you don't have any other native where in your select. My way to fix it was to change the procedure in the list-procs.tcl file adding a -where switch to the procedure. It was somethnig like this:

ad_proc -public template::list::filter_where_clauses {
-name:required
-where:boolean
-and:boolean
} {
@param and Set this flag if you want the result to start with an 'and' if the list of where clauses returned is non-empty.
@param where
} {
# Get an upvar'd reference to list_properties
get_reference -name $name

if { [llength $list_properties(filter_where_clauses)] == 0 } {
return {}
}

set result {}
if { $and_p } {
append result "and "
}
#If you don't have a where in your query, insert one
if { $where_p } {
append result "where "
}

append result [join $list_properties(filter_where_clauses) "\n and "]

return $result
}

The final result after the fix for the above example will be:

select * from my_table
where state = $state_var