Forum OpenACS Development: sorting with listbuilder when you need a URL variable

Hello

I am using list builder to create and order by a table, but I can't find instructions on how to use the order by with a needed URL variable. Here is my code:

ad_page_contract {


@creation-date 03-19-2008
} {
position_id:integer
orderby:optional
task_position_id:integer,optional
}

template::list::create \
-name score \
-multirow score \
-key eval_id \
-no_data "This Position Has No Submitted Evaluations" \
-elements $elements \
-selected_format csv \
-formats {
csv { output csv }
} -orderby {
lname {orderby lname}
fname {orderby fname}
evaluator {orderby evaluator}
datesubmitted {orderby datesubmitted}
score {orderby score}
default_value lname,desc
} -filters {
position_id{}
}

# $SQL is created with $element
db_multirow -extend { position_id } score x "

SELECT em.fname,em.lname,e.eval_comments,e.recommend,$SQL SUM(s.score) AS score, SUM(s.score * rb.weight) as weighted_score, em.datesubmitted,
p.first_names||' '||p.last_name as evaluator
FROM (((((usurf_jobs_empapp_map m
JOIN usurf_jobs_empapp em ON(m.empapp_id = em.empapp_id))
JOIN usurf_jobs_eval e ON(m.empapp_id=e.empapp_id))
JOIN persons p ON(e.user_id=p.person_id))
JOIN usurf_jobs_score s ON (e.eval_id=s.eval_id))
JOIN usurf_jobs_req_bank rb ON (s.req_bank_id=rb.req_bank_id))
WHERE m.position_id=:position_id
GROUP BY em.fname,em.lname,e.eval_comments,e.recommend,em.datesubmitted,evaluator

With the position_id required and the order by not inserting it into the URL, the page does not work when i try to order by a different column.

Any ideas?

Thanks, Brad

Collapse
Posted by Dave Bauer on
This looks OK
-filters {
position_id{}
}

You need to add an empty filter to get the variable passed in the URL. It might be that you need a space between position_id and the curly braces. It might be naming the filter "position_id{}"

Hi Dave I had this script and I don´t know how keep the variale value for each pagination or somethig like that, I´m new in tcl and any help is useful to me, by the way I´m sorry for my English.

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

#sql1 is a param that come from another tcl
#sql1 have the value of a simple sql query

if { ! [request is_valid] } { return }

list::create -name users \
-multirow users \
-key num_sec \
-page_size 10 \
-page_query { select num_sec from procadm.t_normas } \
-elements {
num_sec {
display_template {<table width="700" border="0"><tr><td width="96%" valign="top" align="justify">@users.desc_norma@&nbsp;@users.num_norma@&nbsp;de&nbsp;@users.anio_gac@
Número de Gaceta:&nbsp;@users.num_gaceta@
Autoridad:&nbsp;@users.desc_autoridad@
ítulo:&nbsp;@users.titulo@</td></tr></table> }
}
}

set query "select a.num_sec num_sec, num_gaceta,to_char(fecha_gaceta,'yyyy') anio_gac, 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 \
a.norma=b.num_sec and \
a.cod_autoridad=c.num_sec"

append query " and " $sql1
append query " and "
append query "a.num_sec in ([template::list::page_get_ids -name users])"

db_multirow users users_query $query

on the page=1 show the result fine, but when I past to the another page for example page=2 display this error:

nsoracle.c:3904:ora_tcl_command: error in `OCIStmtExecute ()': ORA-00936: missing expression

SQL: select a.num_sec num_sec, num_gaceta,to_char(fecha_gaceta,'yyyy') anio_gac, 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 a.norma=b.num_sec and a.cod_autoridad=c.num_sec and !>>>!and a.num_sec in ('916','917','918','919','921','922','925','926','927','928')

It´s looks like the value of sql1 not past to the next page

Collapse
Posted by Brad Poulton on
You were right I needed a space after position_id and the braces.

-filters {
position_id {}
}

As a side note, I also forgot the [template::list::orderby_clause -name score -orderby] at the end of my SQL. 😊

Thanks!

Collapse
Posted by Dave Bauer on
Due to limitations in the template::list code
any variable you need to pass needs to have an empty filter specified for it like this:
were right I needed a space after position_id and the braces.

-filters {
position_id {}
}

That said, DO NOT pass SQL fragments in the URL. That is a tremendous security vunerlability. Anything, ANYTHING, can be passed in that URL and the user has full access to your database. You really need to find another solution that passes in information in the URL that is then verified to be safe by your application before it is used to generate a SQL query.