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

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)
	    }
	}