Forum OpenACS Development: More Dynamic List Builder defintion

Collapse
Posted by Dave Bauer on
I need to allow for a dynamic definiton of an entire query from the settings of list builder filters.

This occurs because I have created a dynamic list builder where the user can select which columns are displayed and filter on the values in each column. It is too expensive to query for data that is not used in the display or filter so we need to change the select, from, and where clauses of the query.

I have implemented this by adding a select_clause and from_cluase proprety to list builder filters with the appropriate support to manage this data and generate the query fragments.

Now the queston is, should I submit a TIP to add this to list builder, the code is already written and tested for this.

OR

Submit a parallel tip that allows for arbitrary properties to be added to a list. In this case I would add the additonal entries outside of the list builder libray and use these features to add the data to the list data structure. To do this I need to rewrite all my code to support this.

Collapse
Posted by Malte Sussdorff on
First of all, the idea of the select_clause and the from_clause is great. Contacts has been doing this for more than two years now and it works great.

What we also do is have a callback which allows other modules to register what extension columns they provide for this list. As I use the same technique for the generic object_type list (it's an include which allows you to get all objects of an object_type in the list, with the ability to add and remove columns as you see fit. It also works with relationship object types, displaying both object_id_one and object_id_two with the extension columns for both) I would love to see this method made easier by the TIP (currently the code for the object_type list is over 650 lines long, too much for my liking).

Just mentioning it, not sure if this has any effect on your decision what to TIP, but the parallel TIP sounds a little bit like it would make my code shorter.

I presume you will document those new features properly in the docs for template::list::create ?

Collapse
Posted by Malte Sussdorff on
One more thing. As parts of contacts (and becoming now part of dynamic types) you get a search interface which lets you create "filters" for the list based on the attributes (including related) from a user interface. Maybe that would be nice for you to use, as adding filters for 10 columns can be tedious. If you are interested, I can provide a screencast (both for the list as well as the search functions).
Collapse
Posted by Dave Bauer on
Malte,

Yes I have seen contacts, and probably got some ideas from that code. This just adds the support into listbuilder so it can be reused. Assessments generic storage and contacts generic storage are very similar in the end. Of course this works with any query that requires additional joins to show additional columns.

Overall I have found that most lists are not specific to show only one object type, but they need to work on multiple tables and objects to generate all the information. In some cases where the joins can be expensive, it can help to avoid making the query too complex. In assessment this is taken to an extreme since each additional column added to the list requries a multiple column join on the content repository and assessment data storage tables. Also, the original application that inspired this code, there were possibly 100 columns that might be displayed.

Of course, the code is already documented, I'll be happy to review and make sure the documentation is complete and clear before it is added (if approved).

I have submitted a TIP see: https://openacs.org/forums/message-view?message_id=1840450

Collapse
Posted by Malte Sussdorff on
Hi Dave,

that is great, will look forward to the result. Just some notions to your comment.

a) The whole functionality of contacts which resided on AMS has been changed to purely use acs_object_types, acs_object_type_tables and acs_attributes in intranet-dynfields. This means, we don't have a meta storage anymore, making things much faster. It also means that we know from the database where to get the object information from (which can be multiple tables).

b) Usually, if you want to display a list then you need to at least know how parts of the list relate to other parts. Most of the time you will find, that you just have the same object_type dispersed over multiple tables. Take into account that XoDynfields is object oriented, so Users contain Person and Parties (as they inherit down the object_type hierarchy). It is still one object type though.

Another way of doing this is to have a foreign key reference. This means you link a second object (1:n relation) of a certain object type into the list. This is also handled automatically by database table inspection. So if you have a company which references a main_office_id, you can display a list of companies with main_offices automatically in the list.

Last but not least you could have a real relationship (acs_rel_type). Then instead of saying you want to display the object_type company you want to display employees (or company projects). This will return a list of the n*m matrix of companies and persons (or projects) where the end user can easily extend the table to display any column of both types. This again could be extended by the 1:n relationship mentioned above (so you could display a list of all employees along with their company and their office).

XoDynfields automatically figures out the necessary relationships and the correct joins and returns you a join and table_clause (read: where_clause and from_clause).

So, as you can see, the TIP is highly welcome as I don't have to work with that on my own anymore. But I would ask you to add a "column_clause" as well, so I can define which columns I want to retrieve (as I might have PL/SQL functions which return a value from and ID, reducing the need for me to translate in TCL the ID into the correct value).