List Builder (LB) was introduced in acs-templating 4.6.4, and it allows you to make tables very easily. It took me about two hours to convert a table of mine to LB, and the time is well worth it, because you don't have to manually put in all the code for sorting. It also seems very flexible.
Plus, since you are benefiting from my learning process, you probably won't take two hours to learn it!
How it works? To use list builders you simply do the following, template::list::create \
-name someName \ -multirow someMultirow \ -key someKey \ -elements someElements
db_multirow someMultirow {
SQL select statement here or leave blank and define SQL statement in the XQL file
} {
Things to do per row
}
And add to the ADP file: <listtemplate name="someName"></listtemplate>
What happens is that the multirow would be executed, as it fetches each row some processing is done to them if defined in the "Things to do per row" section. Then each attribute of the row would be assigned to the respective element.
The options are: -name is the name of the list -multirow is the name of the multirow that will be used to populate the listbuilder with data. Optional, defaults to name of the list. -key is an attribute used to identify the row (primary key/unique key). Must be a single column, and is optional. Required only if you have a bulk action. -elements are the elements that will be shown in the list builder. Each element is a list of spec names and values. For instance, the following would define a bookname element: -elements {
book_name {
spec1 value1
spec2 value2
}} Some of the available specs are: -hide_p: to hide the element from default display, give it value 1, else 0 (default) -display_col: the column from the SQL query to show for the element, if the element doesn't have the same name as the column -link_url_col: name of column in multirow which contains the url to redirect to. for example we could make a hidden element called book_url that contains the url of the page that has the information about a book, and add in book_name the following spec "link_url_col book_url". This would show book_name as a hyperlink, which when pressed would go to the book_url.
There are a lot more specs that can be used, check the list::template::element::create documentation for a full listing.
For a full listing of available switches for the list builder, check the list::template::create documentation. Some of the options are explained, just scroll down :)
From Alex Vorobiev:
it is not clear from the docs, and may not be obvious, but the -multirow switch lets developer point to a db_multirow statement. in other words, one MUST create a db_multirow statement with a name that matches -multrow switch of the list-builder. the db_multirow statement, in turn, points to an sql query in the corresponding .xql file
db_multirow *to add url columns, use extend with export_vars *to get row number, use rownum; also see rowcount (in docs for the "multiple" template tag)
An action is a button that would be above your list builder, pressing the button would simply redirect you to a new page. This is usually used for tasks that has nothing to do with what is already available in the list builder, such as adding a new item.
Add as part of your definition:
-actions {
"Use process" "process-use" "Use a process"
"Task calendar" "my_url" "View task calendar"
} \The parameters are button label, url, and title... repeated for every new action
If you put variables or functions in there, you'll notice they don't get substituted. So instead, you do this:
set actions [list "Use process" "process-use" "Use a process" "Task calendar" "my_url" "View task calendar"]
-actions $actions \Thanks, Dave.
Bulk actions are actions that affect a group of selections. For example, if your list builder shows some book information, you can have a bulk action to delete a group of books, instead of having to delete them one by one. Checkboxes appear next to the list so you can choose which items to perform the bulk action on. Bulk actions work on the key attribute you specify in your list builder, so make sure your query does return it. template::list::create \
-name someName \
-key someKey \
-multirow someMultirow \
-bulk_actions [list label1 url1 title1 label2 url2 title2 ...] \
-bulk_action_method method \
-bulk_action_export_vars {additional vars} \
-has_checkboxes boolean\
-elements someElements \
etc...the method in bulk_action_method switch should be post or get... i don't know what they do. and it is optional when you do a bulk action, only the key attribute is passed to the receiving page. to send more variables you specify the bulk_action_export_vars with additional variables to be passed. if your table already has a checkbox column set has_checkboxes to true. if it doesn't then the list builder will add a checkbox column to your list. one last thing is that the receiving page must have the key attribute in its page contract as a multiple variable.
for the default case, one must specify column name FOLLOWED by comma and sort order such as ASC or DESC
In order to add sorting to your list builder do the following: First: add an orderby switch to your list builder like so template::list::create \
-name someName \
-key someKey \
-multirow someMultirow \
-elements {
someElements
} \
-orderby {
columnToSortBy {
label "same label as in element for this column"
orderby {groupOfColumnsToOrderBy}
any other specs you feel like defining here (e.g: default_direction, check possible specs in documentation of template::list::orderby::create)
}
repeat for more columns
}Second: edit your SQL query, simply add the following at the end of it
[template::list::orderby_clause -name listBuilderName -orderby]
or if you already have and order by statement in your query, drop the -orderby switch
Third: add the following to your page contract
orderby:optional
without it, if you press the column name to sort the data, the form will not be able to accept the data when it reloads and thus it won't sort.
The previous steps would turn the label of the column into a hyperlink, which, once pressed, would sort the data in the default direction, and then if pressed again it reveresed the sort. However, the first time the page is loaded the data will NOT be sorted. to make the data sorted when you load the page first time add the following as the first statement in -orderby { default_value columnToSortBy for instance if you have one block to sort by name and another by title you could add default_value name... and when the page loads it would be sorted by name. Alternatively, as you might have guessed, since we do have an orderby in our page contract we can set this value and not use the default_value statement... example: when the page first loads you can add if { !exists_and_not_null orderby } { set orderby "name" }
See adding in sorting From Alex Vorobiev (http://openacs.org/forums/message-view?message_id=115009):
To add pagination do the following: First: add to your list builder the following template::list::create \
-name someName \
-key someKey \
-multirow someMultirow \
-page_size n \
-page_flush_p 0 \
-page_groupsize m \
-page_query_name listName_pagination \
-elements {
someElements
} \
etc...where n is the size of rows per page, m is the number of groups (i.e: if you have 1000 rows and n = 10 this gives you 100 groups, instead of showing all 100 at the same time on the page, assuming m = 5 you show groups 1 - 5 which corresponds to rows 1 - 50, and groups 6 - 10 and so on)... page_groupsize is optional for pagination... i don't know what page_flush_p 0 is all about, to be honest, and the whole thing works w/o it... you must specify a page_query_name, which corresponds to a query in your XQL file, but the listName_pagination is only a convention, you can call it anything...
Second: go to your XQL file and do the following copy your entire query, the one defined in your multirow in the Tcl file, and rename it to the name provided in page_query_name switch... then to the original one, add the following at the end template::list::page_where_clause -and -name listName. Note: if you have an order by clause (i.e: template::list::orderby_clause etc...) you place the page_where_clause BEFORE the orderby_clause now make sure the pagination version of the query have the key that you defined in the list... or better yet, only the key.
Third: add the following to your page contract:
page:optional
so that when you press on a page number it gets passed to the form as it is reloaded...
Now what happens is this, first the pagination query is executed... it fetches the IDs of items in your DB. Then the actual query is executed, but now it has the page_where_clause which makes the query add a constraint that the ID of the item retrieved must be in whatever the pagination query returned. For little number of rows, this doesn't affect anything, but for large number of rows this actually help increase the performance of your query, besides the obvious advantage of getting pagination...
From Alex Vorobiev:
-multirow vs -page_query it is not immediately clear from the docs, and may not be obvious, but when using pagination, both switches are necessary. the purpose of the -page_query is to fetch the ENTIRE result set, whereas the sql statement used by multirow would fetch only the current page set.
caching with current paginator (not jon griffin's new paginator) use "-page_flush_p 1" to prevent paginator from caching the page set and confusing the hell out of you (when newly added records are suddenly not appearing on the page)
See pagination for list-builder (http://openacs.org/forums/message-view?message_id=115009)
An example is found in the cvs browser for logger (http://cvs.openacs.org/cvs/openacs-4/packages/logger/www/). It uses list-builder well, and does nifty things with filters, etc.. It does not use list-builder efficiently, however!
Walk with me as I convert my index.tcl and index.adp file to list-builder. This is the end of the .tcl file:
# An HTML block for the breadcrumb trail
set context_bar [ad_context_bar]
set title "Organizations"
db_multirow orgs orgs_query { }The index.adp file looks something like this:
<master>
<property name="title">@title@</property>
<property name="context_bar">@context_bar@</property>
<if @orgs:rowcount@ gt 0>
<table cellpadding="3" cellspacing="0">
<tr>
<th>Action
<th>Name
<th>Notes
</if>
<multiple name="orgs">
<tr>
<td>
<a href="one?organization_id=@orgs.organization_id@">Details</a>
<if @write_p@>
<a href="add-edit?organization_id=@orgs.organization_id@">Edit</a>
</if>
<if @delete_p@>
<a href="delete?organization_id=@orgs.organization_id@">Delete</a>
</if>
</td>
<td>@orgs.name@</td>
<td>@orgs.notes@</td>
...
</tr>
</multiple>
<if @orgs:rowcount@ gt 0>
</table>
</if>
<if @create_p@>
<ul>
<li><a href="add-edit">Add an organization</a></p>
</ul>
</if>You would then change the .tcl file to look like this:
template::list::create \
-name orgs \
-multirow orgs \
-key organization_id \
-elements {
organization_id {
}
name {
}
notes {
}
} \
-main_class {
narrow
}
db_multirow -extend { item_url } orgs orgs_query {
} {
set item_url [export_vars -base "one" {organization_id}]
}Then you change the .adp file to look like this:
<master> <property name="title">@title@</property> <property name="context_bar">@context_bar@</property> <listtemplate name="orgs"></listtemplate>
If you get an error like this:
Too many positional parameters specified
while executing
"template::list::create__arg_parser"
(procedure "template::list::create" line 1)
invoked from within
"template::list::create \ "
("uplevel" body line 86)
invoked from withinThen the problem is this:
This error means one of your arguments is being interpreted not as a named switch (-elements ...) but as a positional argument (no switch).
This could easiliy be a line that doesn't have the \ at the end, or that has a space character after the \, or some other little typo like that.
OpenACS has a very cool <group> tag, which allows you to show several rows from the database in one row. For example, if you have the table foo, with columns x and y, with values of
x y --- 1 3 1 4 2 5
You may want the output to look like:
1 3 4 2 5
or close to that, using an HTML table. Well, the <group> tag does that. Can we do that with list-builder?
Well, quite easily, it turns out.
Just put something like this in the definition of your column (preferably the last one):
last_name {
label "Who"
display_template {
<group column="item_id">@tasks.first_names@ @tasks.last_name@
</group>
}
}Unfortunately, this will mess up the alternating bands of light and dark, because the odd and even rows will not be valid.
If you want your table to be more compact, put this in:
-main_class {
narrow
} \If you'd like to return a comma-separated-value file, follow Lars' instructions on returning a CSV from list-builder. These CSV files are opened by Excel or other spreadsheet programs, and make an easy way of getting information out of the database for office workers.
From Alex Vorobiev:
- copy acs-templating/tcl-list-procs.tcl and acs-templating/resources/lists from CVS/HEAD to the appropriate locations in 4.6.3 distribution - 4.6.3 does not support the new "noquote" tag; any such tags can be safely removed from templates in versions of openacs prior to 5.0
Caroline Meeks adds:
- you have to use list-procs.tcl version 1.3. - Restart the server, don't just watch the files.
Alex Vorobiev:
any information below that mentions pagination refers to old "broken" paginator - basically not possible with the current implementation of list-builder, see http://openacs.org/forums/message-view?message_id=28810 - poor man's hierarchies: one can build db_multirow as a join that would fetch parent and children records in the same query, and then use <group column=group_key> tag to iterate over the records with the same "key" until the key changes (see "group" template tag docs). in this case, pagination is very hairy, especially with joins: -> group "key" has to be same as the -page_query "key", where "key" is the query column -> if multirow query and page_query use the same joins, then the page_where_clause will list the same "key" id multiple times: for instance, project_id in ('455','455','466') - notice 455 appears twice. this causes fewer rows (than page_size) to be displayed on a page. the solution is to change page_query - use table aliasing 'spm_projects j', the same as in the multirow query, but no joins. if we don't do table aliasing, then the order by will break (order by j.project_id)
Alex Vorobiev:
- pagination: add the aliased column name to -key: j.project_id - sorting: add the aliased column name to orderby statements in the orderby block - sorting by columns in more than one table (j.project_id p.proposal_id) breaks because both tables need to appear in the page_query, which in turn breaks # of records per page... i thought that wrapping the page_query select in a "select distinct" or select with a group by would yield, but both actually re-sort by the distinct or groupby column, so there is no way to preserve the sort order of the inner select statement... giving up for now until the new paginator is out which may make all of this a moot point.