Forum OpenACS Development: Small feature enhancement for template::list

Hello everyone,

in my company we use template::list a lot and sometimes we need to get the entire number of records it contains.

Actually there is no mean to have this information, because template::paginator::get_row_count will stop at number_of_rows_per_page * number_of_pages.

To fill this gap I've created a proc that suits our needs, but maybe could be useful for others, so I paste it here for opinion. Is it worth being included in the standard? Is there a better way to achieve this? The code to obtain paging query I've borrowed from template::list::prepare.

Here you are...

ad_proc -public template::list::get_full_row_count  {
} {
    Get full number of records in a template list
} {
    # Get reference to template::list                                                                                                                                                
    template::list::get_reference -name $name -local_name list_properties
    # Get pagination query                                                                                                                                                          
    if {$list_properties(page_query) eq ""} {
        set page_query [uplevel $list_properties(ulevel) [list db_map $list_properties(page_query_name)]]
    } else {
        set page_query [uplevel $list_properties(ulevel) [list subst -nobackslashes $list_properties(page_query)]]
    # Exec query in a count to retrieve number of records
    return [uplevel 1 [list db_string query "select count(*) from ($page_query) t"]]
Posted by Gustaf Neumann on
Dear Antonio,

i am not a template::list expert, but nevertheless, here are my 2 cents:
a) it looks to me as if the proposed function recalculates something, which was computed before. The full number of rows must be know when the pagination happens. So, either this value is stored in the guts of the list template, or maybe it should be stored there in order to retrieve it from you function efficiently (without recalculation).

b) are you sure, the "uplevel 1" is correct? should it not be a "uplevel $list_properties(ulevel)"?


Posted by Antonio Pisano on
Dear Gustaf,

it seemed strange to me too that this value was not stored somewhere, but truth is it is not!

I think the reason is that the full count is not required to the paginator, as it really just needs to know how many records get to populate all the pages and then stop.

The original, untampered with, paginator query is never called: it is always wrapped into "offset" clauses that limit its results to number_of_pages * rows_per_page, and also overwritten, so I had to retrieve the original one with some code replication...

The uplevel is required so I can see filter vars in the caller scope. I can do better than that I've seen, because I have a reference to filter names and values in the list.

I think from a performance perspective, as the value is not anyway, it is ok to have a different proc doing this calculation. I could change the template::list::prepare so a reference to the original query is maintained and I can retrieve it, then remove the upvars by looking into list properties instead.

What do you think?

For reference:

Posted by Gustaf Neumann on
The paginator has to know, how many pages should be offered.

Storing the bound sql query to save on the uplevel is performance-wise not a big difference. sql-queries are (in good cases) in the range of milliseconds, tcl/xotcl statements in the range of micro-seconds. so, saving a sql query (especially an aggregating query) is certainly useful, since people use pagination on large tables, where the count query might be as well slow.

Posted by Antonio Pisano on
Ok then, I will look for an elegant way to exec the count when list is created, then store the value in the list properties.

To my next proposal 😊


Posted by Antonio Pisano on
Think I have come out with the proper solution:

on template::list::prepare, I save an entry for "count_query" in the list properties, then pass it to template::paginator::create together with "page_query_substed" as before.

Here, I check for a cache entry for the full_row_count, as happens for paginated row_ids. As happened before, if row_ids are not in the cache, I call paginator query into template::paginator::init. Into this proc I have added also the call to the count_query and the saving of the value in the cache.

This way now paginator has a new property called "full_row_count", which can be retrieved from template::paginator::get_full_row_count.

To have a common interface for getting the count in template::list, I have created a proc called template::list::get_rowcount. On a paginated list, it calls template::paginator::get_full_row_count, otherwise it just returns the size of the undelying multirow.

Its easier done than explained... Anyway, checks on both paginated and not paginated lists on my instance work. Is it ok if I commit the change?


Posted by Gustaf Neumann on
sounds perfect!
Posted by Antonio Pisano on
Change has been committed.

On up-to-date systems, you will find the new proc


Giving the proper count of the number of records retrieved by a template::list

Thanks to Gustaf for his suggestions on this!

All the best