Forum OpenACS Development: Dynamic Columns and FIlters for listbuilder

Dynamic columns are columns that are added to a query only if they are visible.

The idea is that some queries might be expensive and we don't want to do them unless the data is being used.

Same thing for dynamic filters. The data for the filter does not need to be queried unless we are actually retricting the results on that filter. This eliminates a join in the query.

This came up when we were building a list where the columns could be the answers to questions from an assessment. To add a column for an assessment answer, we need to do a join for every question we are interested in. An assessment can have many questions but only a few might be interesting at any point, for the list.

The idea I had so far, is to add new attributes for elements and filters.

For elements I added a select_clause, from_clause, where clause (probably can do the join in the select clause also, I just thought of that).

For filters I added from_clause.

I also added a check so that is an element and filter have the same from clause or where clause, it is not duplicated.

I added some helper procedures to pull these out:

template::list::element_select_clauses
template::list::element_from_clauses
template::list::element_where_clauses
template::list::filter_from_clauses

and the appropriate variables to list_properties to make it work.

I am looking for feedback on this design, and and ideas people might have on how this could work.

Collapse
Posted by Malte Sussdorff on
I think these are good ideas, yet I have a couple of points to make.

a) Filters don't need a from clause. Or do you want to be able to filter by something which is not an element? In that case you would need a from and a where clause and make sure that part of the where clause is the join with the primary table.

b) Wouldn't it be better instead of saying "from" and "where" clause to make sure an element can be picked up, to have a general "condition" element which is a list of {primary_key table_name join_key} and then use that named condition on all elements that need it? This way you do not have to add the select, from, and where clause to each element and make sure that you are not adding the clause twice just because you have two elements that have that clause in them.

c) If you go with conditions you have the additional benefit that you could allow for editing a row as well, as you could get the list of elements and from which table they are from (due to the condition) and then create an ad_form which lets you edit one row in your table across multiple phyiscal tables. Obviously that might have other consequences, so we might actually think instead of using primary_key and table_name to use object_type, so we could join up any object_type into the current table and use attributes / elements from that object_type.

My main concern is that though the clauses give you flexibility I would prefer to have something more formalized so it is easier to reuse in different environments where the from clause might not be enough. Thats why I would use conditions and object type, but probably you thought about that as well and dropped that idea for a reason.

Collapse
Posted by Dave Bauer on
To be able to generate any sort of usable query, we need to be more specific in the SQL.

I see the point of duplicating the from clause with multiple elements, but in my case, as I said we are using assessment, so each one is unique.

Its simple to check if its already used though, with an lsearch, so I don't see any reason to be too picky.

I think it would be nice to be able to get this info from the object type definition but it gets to complex too fast I think, for the kind of specialized work we need to do with assessments. I think contacts/AMS would be similar.

I use helper procedures to generate the clauses from the assessment questions, and I think, that if you wanted to generate them from an object_type that would be the way to go.

Thanks for the feedback.

Collapse
Posted by Dave Bauer on
Again, regarding editing a row.

We assume here that the developer has intimate knowledge of the application. We aren't doing an deep introspection of the system here. That could be another level of abstraction.

You could write the code to do the edit based on the visible columns outside the listbuilder.

I tried to make these dynamic features add-ons to listbuilder but the way it works, I could not easily do it.

I still think it might be best if I refactored listbuilder and allowed adding columns/filters after the call to template::list::create.

This would keep the internals of listbuilder a little simpler (a good thing! have you looked inside there?) and make it more clear in the code exactly what was happening.

The reason I tried to integrate was that the filter_where_clauses etc... are generated from "prepare_elements" and "prepare_filters" clauses.

But it should be straightforward to append to those properties after template::list::create is called.

So I could create a template::list::prepare_filters_again (name for illustration purposes only ...) that takes a list of filter names and prepares just those filters. Ditto for prepare_elements.

In fact I added the filter_form feature and I think it makes sesne to add that explicitly too outside the call to template::list::create.

I'll post more, but I think this way will make the code much cleaner for anyone who does not need this features.

It also makes it possible to add them incrementally without modifying the core code until we have a good understanding of it.