Forum OpenACS Q&A: Using list-builder and order by

http://rubick.com/openacs/list-builder

Here's my question:

I have set up a list builder as follows:

.tcl

    template::list::create \
        -name revisions \
        -multirow revisions \
        -key revision_id \
        -elements {
            revision_id {
                label "Subject"
                display_col task_title
                link_url_col item_url
                link_html { title "View this revision" }
                display_template {<if @revisions.live_revision@ eq @revisions.revision_id@><B>@revisions.task_title@</B></if><else>@revisions.task_title@</else>}
            }
            description {
                label "Description"
            }
            percent_complete {
                label "Status"
                display_template mailto:"@revisions.percent_complete@\%"
            }
            start_date {
                label "Start date"
            }
            end_date {
                label "End date"
            }
        }

    db_multirow -extend { item_url } revisions task_revisions_query {
    } {
        set item_url [export_vars -base "task-one" { revision_id task_id}]
    }

.adp

<listtemplate name="revisions"></listtemplate>

.xql

  <fullquery name="task_revisions_query">
    <querytext>
    SELECT
        t.item_id,
        t.revision_id,
        i.live_revision,
        t.title as task_title,
        t.description,
    to_char(t.start_date,'MM/DD/YYYY') as start_date,
    to_char(t.end_date,'MM/DD/YYYY') as end_date,
        t.percent_complete
    FROM
    pm_tasks_revisionsx t, cr_items i
    WHERE
        t.item_id = :task_id and
        t.item_id = i.item_id
        ORDER BY
        t.revision_id desc
    </querytext>
  </fullquery>

-------------------------
This works quite well. However, I'm having difficulty figuring out how to set up order by statements. The docs don't really seem to go into this much. Could anyone help out here? I imagine I need to put something in the .xql file, and add in the -orderby clause.

Also, is there a way to make the table created by list-builder to be width=100%?

I left out part of it.

In template::list::create you need to pass in the spec for which elements can be ordered.

-orderby {
   element_name {orderby column_name}
}

You can optionall specify addition attributes for the orderby including the default direction. The full doc of what you can specify is in the api-doc for template::list::orderby_create.

A few minor comments:

1) You can get template::list::orderby_clause to spit out the 'order by' part as well by adding the switch -orderby. I just realized that this switch wasn't documented so I added the documentation.

2) I usually specify the call to orderby_clause directly in the query in the .xql file:

select ...
from  ...
where  ...
[template::list::orderby_clause -name listname -orderby]

3) Your -filters trick to add task_revision_id is unfortunalyte the only way to get the list builder to export variables right now. However, for good measure, you should add an empty list afterwards, like this:

-filters {
    task_revision_id {}
}

The reason is that filters normally have specificaion blocks, just like any other element. It doesn't make any difference in this situation, but if you were to add another filter later, you could run into trouble, because the 'name' and 'specification' blocks could be confused.

-filters {
  task_revision_id
  some_other_filter {
      label "This will not do what you want it to"
  }
}

4) If you add a 'default_value refvision_id' to your orderby section, you won't have to do if exists_and_not_null orderby part:

-orderby {
        revision_id {orderby revision_id}
        percent_complete {orderby percent_complete}
        start_date {orderby start_date}
        end_date {orderby end_date}
        default_value revision_id
    } \

5) And then to answer your question to me in private email: No, there's currently no way to change the name of the query parameter used for orderby currently named 'orderby'. Which means you cannot have multiple sorted lists on the same page. I'll look into fixing this.

/Lars

say

template::list::create \
    ... \
    -selected_format csv \
    -formats {
        ...
        csv { output csv }
    }

Then around the end of the .tcl file, say

template::list::write_output -name listname

/Lars

But in the end, we should not get overly used to the default_template part and hope that funding or an excuse for fixing arises soon so that people will not have to dig through too much code once this part is done as you intended to - placing adp where adp belongs.

I sincerely hope that this issue will not be forgotten since I really dislike the idea of mixing tcl and html again (and adp is output-html in the end), although I have to agree that ACS3 had way worse code than this, but still....

Greetings to Kopenhagen from India,
Timo

Now I have three list-builders going in one page.

I've linked in this thread from my LB page.

An example of the direction that maybe a problem is.  I wan't my check box on the right side.  I think this can't be done on the adp/template of the list.  I haven't use the paginator of the list builder, but it would be nice if.  The future direction is that the paginator is not too coupled with the list builder.  Its also a bit hard to customize your template, you need to use noparse, or some other trick.  Read the list-procs.tcl code.  Its not something a designer level developer may always have.

All in all, I think the list builder is helpful to me.  Just giving some feedback on it and hopefully it will get better.

I've just started using Lars' cool list-builder and have a few questions/comments:

* Since my installation is based on oacs-4-6-3-final, I copied over list-procs.tcl, pagination-procs.tcl, and acs-templating/resources/lists/* from the tip of oacs-4-6.  Things seem to be working, but would you happen to know if that would break anything?

* In order to get pagination to work, I specified the page_size and page_query parameters.  I then had to call get_reference to get the name of the paginator object.  I then called paginator::get_data to create the final multirow.  Here's the code:

ad_page_contract {
} {
    {page:naturalnum 1}
}

list::create -name users \
        -multirow users \
        -key user_id \
        -elements {user_id    {label "User ID"}
                  email      {label "Email"}
                  full_name  {label "Name"}
                  registered {label "Registered"}
        } \
        -page_size 5 \
        -page_query {
            select user_id from cc_users
        }

list::get_reference -name users

paginator get_data fetch_users $list_properties(paginator_name) users {
        select
            user_id,
            email,
            first_names || ' ' || last_name as full_name,
            to_char(creation_date, 'MM/DD/YYYY HH:MI AM') as registered,
            to_char(last_visit, 'MM/DD/YYYY HH:MI AM') as last_visit
        from
            cc_users where user_id in (CURRENT_PAGE_SET)
    } user_id $page

* Is that the proper way to do pagination?  What about pagination context?

* Will try to figure out "filters" now.  But, while I have your attention, I would appreciate a primer on the purpose of filters and how they work.  Perhaps an example? =)

Thanks for contributing this Lars.  I think it's wonderful that we now have a standard and powerful way for creating tables/lists.

Create the list the way you do, then include the following in your query:

select ...
from  ...
where  ...
and    activity_id in ([template::list::page_get_ids -name $list_name])

That should do it.

Pagination is still kindof sketchy. The "old" paginator which I'm using is complicated and does this pagination in Tcl instead of in the database, etc.

Whenever I get a chance, I want to try and integrate with Jon Griffin's new paginator, which should be simpler.

/Lars

http://openacs-4-6.cph02.collaboraid.net/index-list-builder.tcl.txt

/Lars

http://cvs.openacs.org/cvs/openacs-4/packages/logger/www/

Also, while I think the list builder is cool, it is way to complex for most uses and as you see you have to specify different code just to paginate.

I wrote, and Brad Duell has been adding to, my paginator to combine both funtions and it is much faster.

A. All sorts are done in the DB where it should be, no more tcl madness to sort.
B. It uses the almost the same syntax as the old ad_ procs.
C. It has been extended by Brad to do much more.
D. It is already integrated with external dhtml widgets.
E. It is in production on several sites, some with very large querys that took minutes with the old paginator and under a second with the new.

I am looking into integrating my stuff with list-builder, but with so much dependency on templating procs it will be difficult to do. I feel that unfortunatly list-builder is becoming the de-facto UI widget, but I will update my code base with Brad's latest changes tomorrow.

It's not the entire increase, we also started recording thread-count information rather than use count(*) queries, a couple of slow queries were found and improved by people other than myself, etc.

But the list builder itself now scales quite nicely if you use pagination, and even more nicely if you enable caching (which requires some simple logic to figure out when to flush the cache).

<< < 1 2 3 4 5 6 7 8 9 10 > >>

Gaceta norma numero año autoridad
---------------------------------------------------------
375 49 25 1906 8
753 49 12 1909 8

the tipe for 49=Ley and 8=asamblea

but I want list like this

Ley 25 1906
375
asamblea

ley 12 1909
753
asamblea

and go on

is this possible or I have to limited for the tradicional pagination list

###########################################################
request create -params {
sql1 -datatype text -optional
}

ad_page_contract {
} {
    {page:naturalnum 1}
}

list::create -name users \
        -multirow users \
        -key num_sec \
        -page_size 10 \
        -page_query { select num_sec from procadm.t_normas where $sql1} \
        -elements {
                  num_sec {
                        display_template {<table width="800" border="0"><tr><td width="2%" valign="top" align="center"><font face="Arial, Helvetica, sans-serif" color="#FF0000" size="3"><b><img src="puntero.gif" width="17" height="17"></b></font></td><td width="96%" valign="top" bgcolor="#E8E8E8" align="justify">@users.desc_norma@&nbsp;@users.num_norma@&nbsp;de&nbsp;@users.anio_gac@<br>Número de Gaceta:&nbsp;@users.num_gaeta@<br>Autoridad:&nbsp;@users.desc_autoridad@<br>Título:&nbsp;@users.titulo@</td></tr></table> }
            }
        }

  set query  "select a.num_sec num_sec, num_gaceta,to_char(fecha_gaceta,'yyyy') anio_gac, a.norma, desc_norma, num_norma, titulo, cod_autoridad,desc_autoridad \
from procadm.t_normas a, procadm.t_tipo_norma b,procadm.t_autoridad c where $sql1 and \
a.norma=b.num_sec and \
a.cod_autoridad=c.num_sec and \
a.num_sec in ([template::list::page_get_ids -name users])"

    db_multirow users users_query $query

#########################################################

I past the value sql1 from another script and list the first  10 rows fine when I put the value from keyboard but when I clik the next 10 rows I feel like sql1 lost the value in the array and list me an error.

What I am doing wrong.  Please sorry about my english

Also I don't think you need the "request create" - just use ad_page_contract to handle any form variables.

How I use ad_page_contract to do this, handle any form variables from another script passed

this is a portion of the script that I want to pass parameters
index_lista.tcl

if {  [string equal $keyword {}] } {

  if {  ! [ string compare $sql1 "no"] } {

        set display "doc_list"
        set band4 "12"
        element set_error func_search error2_search " Por favor introduzca su busqueda"
        return
        } else {
# past of parameter to prueba_list_builder?
template::forward prueba_list_builder?sql1=$sql1
}

#This is the code for the prueba_list_builder

ad_page_contract {
} {
            {page:optional}
}

request create -params {
  sql1 -datatype text -optional
}

if { ! [request is_valid] } { return }

set sql1 $sql1

list::create -name users \
        -multirow users \
        -key num_sec \
        -pass_properties { sql1 } \
        -page_size 10 \
        -page_query { select num_sec from procadm.t_normas where $sql1} \
        -elements {
                  num_sec {
                        display_template {<table width="800" border="0" cellspacing="6" cellpadding="6"><tr><td width="2%" valign="top" align="center"><font face="Arial, Helvetica, sans-serif" color="#FF0000" size="3"><b><img src="puntero.gif" width="17" height="17"></b></font></td><td width="96%" valign="top" align="justify">@sql1@<br>@users.desc_norma@&nbsp;@users.num_norma@&nbsp;de&nbsp;@users.anio_gac@<br>Número de Gaeta:&nbsp;@users.num_gaceta@<br>Autoridad:&nbsp;@users.desc_autoridad@<br>Título:&nbsp;@users.titulo@</td></tr></table> }
            }
        }

  set query  "select a.num_sec num_sec, num_gaceta,to_char(fecha_gaceta,'yyyy') anio_gac, a.norma, desc_norma, num_norma, titulo, cod_autoridad,
desc_autoridad \
from procadm.t_normas a, procadm.t_tipo_norma b,procadm.t_autoridad c where $sql1 and \
a.norma=b.num_sec and
a.cod_autoridad=c.num_sec and
a.num_sec in ([template::list::page_get_ids -name users])"

db_multirow users users_query $query

the view showme the value of variable for the first page but when I past to the second the variable lost the value