Forum OpenACS Improvement Proposals (TIPs): TIP#128 (Implemented) Add filter_select_clause and filter_from_clause to template::list filters

Request notifications

This proposal is to add filter_select_clause and filter_where_clause properties to template::list filter definitons.

This allows the resulting query to be built depending on the filter settings. I have an application where there are 100's of potential columns to be generated for a list, but most are not displayed. The column can be added to the display and used for filtering results. This means the column only needs to appear in the query when it is actually used.

More concretely the columns are answers to assessment questions. This leads to a frightening join on assessment and CR tables for EACH column. It actually performs reasonably well (with forthcoming performance improvements/hacks in assessment).

The result is that in additional to changing the where clause, we also needed to change the from and select clauses of the query when the filters are changed.

Thanks for reminding me.

This TIP is now edited to include the following:

In addition to filters, elements also may have a select and from clause. This is to support selecting additional columns which require an addition join to show the column in the list.

See this code sample for the code that builds a dynamic element specification:

ad_proc as::list::column_element_spec {
-as_item_id
-item_title
-cr_item_id
{-hide_p 0}
{-element_select 1}
{-element_from 1}
} {
set item_type [db_string get_item_type {}]
if {$item_type eq "as_item_type_mc"} {
# MC
set select_clause "as_item_id_${cr_item_id}.choice_value as as_item_id_${cr_item_id}"
} else {
# Default
set select_clause "as_item_id_${cr_item_id}.text_answer as as_item_id_${cr_item_id}"
}
set from_clause "left join (select dd.* from as_item_data dd where dd.as_item_cr_item_id = $cr_item_id) as_item_id_$cr_item_id on as_item_id_$cr_item_id.session_id = m.session_id"

set where_clause " as_item_id_$cr_item_id.session_id = m.session_id"
set spec [list \
hide_p $hide_p \
label $item_title \
where_clause ""]
if {$element_select} {
lappend spec select_clause $select_clause
}
if {$element_from} {
lappend spec from_clause $from_clause
}
if {$element_select || $element_from} {
lappend spec aggregate none
}
return $spec
}

Here is the code that generates the filter specification:

ad_proc as::list::filter_spec {
{-as_item_id:required}
{-cr_item_id:required}
{-item_title:required}
} {
Generate list builder filter spec from one assessment question

@param as_item_id Revision_id of hte question
@param item_type Type of question
@param item_title What we display for the filter label for the question
} {

set item_type [db_string get_item_type {}]
set type "singleval"

set spec \
[list \
label "'$item_title'"]
if { $item_type eq "as_item_type_mc" } {
lappend spec type "multival"
set values [concat [list [list "--" ""]] [db_list_of_lists item_choices {}]]
set i 0
foreach l $values {
if {[string length [lindex $l 0]] > 21} {
set values [lreplace $values $i $i [list "[string range [lindex $l 0] 0 21]..." [lindex $l 1]]]
}
incr i
}
lappend spec values $values
lappend spec null_where_clause " 1=1 "
lappend spec where_clause_eval "subst \"( ( :as_item_id_$cr_item_id is null and coalesce(trim( from as_item_id_${cr_item_id}.choice_value),'') = '' ) or (:as_item_id_${cr_item_id} is not null and btrim(as_item_id_${cr_item_id}.choice_value) in (\[template::util::tcl_to_sql_list \$as_item_id_${cr_item_id}\]) ) ) and as_item_id_$cr_item_id.session_id = m.session_id\""
# FIXME Check elements list for this

lappend spec form_element_properties [list widget ajax_list_select options $values]
lappend spec select_clause "as_item_id_${cr_item_id}.choice_value as as_item_id_${cr_item_id}"
} else {
lappend spec values ""; #[set as_item_id_$cr_item_id]
lappend spec where_clause "lower(as_item_id_${cr_item_id}.text_answer) like '%'||lower(:as_item_id_$cr_item_id)||'%' and as_item_id_$cr_item_id.session_id = m.session_id"
# FIXME Check elements list for this
lappend spec select_clause "as_item_id_${cr_item_id}.text_answer as as_item_id_${cr_item_id}"
}

lappend from_clause "left join (select dd.* from as_item_data dd where dd.as_item_cr_item_id = $cr_item_id) as_item_id_$cr_item_id on as_item_id_$cr_item_id.session_id = m.session_id "
return $spec
}

And here is the code that generates the list properties from these element and filter specs to generate the clauses. Note that it only generates the filter from and select clauses if they don't already exist for the same element name, since we are supporting dynamic elements and dynamic filters for those same elements.

Filters:

# get the from clause
# check if there is a dynamic column
# see if we have an element with the same name
if {[lsearch $list_properties(elements) $filter_properties(name)] > -1} {
template::list::element::get_reference -list_name $name -element_name $filter_properties(name)

if {[info exists element_properties(from_clause_eval)] && $element_properties(from_clause_eval) ne "" && [lsearch $list_properties [string trim [uplevel $list_properties(ulevel) $filter_properties($property)]]] < 0} {
lappend list_properties(from_clauses) [uplevel $list_properties(ulevel) $filter_properties($property)]
} elseif {[info exists element_properties(from_clause)] && $element_properties(from_clause) ne "" && [lsearch $list_properties(from_clauses) [string trim $element_properties(from_clause)]] < 0} {
lappend list_properties(from_clauses) [string trim $filter_properties(from_clause)]
}

}
# get the select clause
if {$filter_properties(select_clause_eval) ne "" && [lsearch $list_properties(element_select_clauses) $filter_properties(select_clause_eval)] < 0} {
lappend list_properties(filter_select_clauses) [uplevel $list_properties(ulevel) $filter_properties(select_clause_eval)]
} elseif {$filter_properties(select_clause) ne "" && [lsearch $list_properties(element_select_clauses) $filter_properties(select_clause)] < 0} {
lappend list_properties(filter_select_clauses) $filter_properties(select_clause)
}

And elements

# support dynamic coluumns
if {!$element_properties(hide_p)} {
if {$element_properties(from_clause_eval) ne ""} {
set evaluated_from_clause [uplevel $list_properties(ulevel) $element_properties($property)]
if {[lseach $list_properties(from_clauses) $evaluated_from_clause] < 0} {
lappend list_properties(from_clauses) $evaluated_from_clause
}
} elseif {$element_properties(from_clause) ne "" && [lsearch $list_properties(from_clauses) $element_properties(from_clause)] < 0} {
lappend list_properties(from_clauses) $element_properties(from_clause)
}
# get the select clause
if {$element_properties(select_clause_eval) ne "" && [lsearch $list_properties(element_select_clauses) [string trim [uplevel $list_properties(ulevel) $element_properties(select_clause_eval)]]] < 0} {
lappend list_properties(element_select_clauses) [uplevel $list_properties(ulevel) $element_properties(select_clause_eval)]
} elseif {$element_properties(select_clause) ne "" && [lsearch $list_properties(element_select_clauses) [string trim $element_properties(select_clause)]] < 0} {
lappend list_properties(element_select_clauses) $element_properties(select_clause)
}
# get the where clause
if {$element_properties(where_clause_eval) ne "" && [lsearch $list_properties(element_where_clauses) [string trim [uplevel $list_properties(ulevel) $element_properties(where_clause_eval)]]] < 0} {
lappend list_properties(element_where_clauses) [uplevel $list_properties(ulevel) $element_properties(where_clause_eval)]
} elseif {$element_properties(where_clause) ne "" && [lsearch $list_properties(element_where_clauses) [string trim $element_properties(where_clause)]] < 0} {
lappend list_properties(element_where_clauses) $element_properties(where_clause)
}
}

Could you add a filter_column_clause which allows us to define which elements we want to retrieve, highly useful if you have PL/SQL functions that can do something on the retrieved values (e.g. dereference an ID into a readable value).
Malte, the column clause is what is called "select" clause in my implementation.
Following the example, can you put the template::list::create where you use the procs:
as::list::column_element_spec
as::list::filter_spec

Thanks!

Roc, it looks like this:

template::list::create \
-name list_name \
-multirow multirow_name \
-elements $elements \
-filters $filters

It's not quite that simple, since there are fixed elements and filters that are added as well but the spec for those is just added to the dynamic ones with concat to combine the lists.

Sounds like a good addition, although I haven't found any scenarios where I can use them at this point.
Anyway, please put some real examples on how to use it along with your contribution, if not we might be adding some "hidden" functionalities.

Approved.

Ah... okay, then I would ask for a "table" clause as I need to define which columns, which tables and which joins to use. But that would only be an convenience for me.
Malte,

I did ammend the original spec to note that there is a select clause, from clause, and where clause for the filters and elements. That should cover any case you have.