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

Collapse
1: TIP#128 (Implemented) Add filter_select_clause and filter_from_clause to template::list filters
Posted by Dave Bauer on 08/29/08 08:01 PM
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.

Collapse
3: Re: TIP#128 Add filter_select_clause and filter_from_clause to template::list filters (response to 1)
Posted by Dave Bauer on 08/30/08 04:03 PM

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)
	    }
	}
Collapse
4: Re: TIP#128 Add filter_select_clause and filter_from_clause to template::list filters (response to 1)
Posted by Malte Sussdorff on 08/30/08 05:02 PM
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).
Collapse
5: Re: TIP#128 Add filter_select_clause and filter_from_clause to template::list filters (response to 1)
Posted by Dave Bauer on 08/30/08 11:57 PM
Malte, the column clause is what is called "select" clause in my implementation.
Collapse
6: Re: TIP#128 Add filter_select_clause and filter_from_clause to template::list filters (response to 3)
Posted by Rocael Hernández Rizzardini on 08/31/08 04:32 PM
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!

Collapse
7: Re: TIP#128 Add filter_select_clause and filter_from_clause to template::list filters (response to 1)
Posted by Dave Bauer on 08/31/08 04:53 PM
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.

Collapse
8: Re: TIP#128 Add filter_select_clause and filter_from_clause to template::list filters (response to 7)
Posted by Rocael Hernández Rizzardini on 08/31/08 11:42 PM
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.

Collapse
10: Re: TIP#128 Add filter_select_clause and filter_from_clause to template::list filters (response to 5)
Posted by Malte Sussdorff on 09/05/08 10:51 AM
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.
Collapse
11: Re: TIP#128 Add filter_select_clause and filter_from_clause to template::list filters (response to 1)
Posted by Dave Bauer on 09/05/08 03:32 PM
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.