I'm trying to retrieve the folder_id from an URL, but I got stuck finding a solution, so I did this very unelegant one:
in the .tcl file:
set splitted_url [split $the_url "/"]
set item_url_title [lindex $splitted_url end]
set item_url_folder [lindex $splitted_url end-1]
set item_url_parent_folder [lindex $splitted_url end-2]
set file_id [db_string upgraded_item_id {} -default 0]
and in the .xql:
select i.item_id
from cr_revisions r, cr_items i
where r.item_id = i.item_id
and r.title = :item_url_title
and i.parent_id = (select item_id
from cr_items
where name = :item_url_folder
and parent_id = (select item_id
from cr_items
where name = :item_url_parent_folder))
This screams for a reverse connect by / tree view, especially as this only takes into account two folders for distinction (not all of them). But I don't know how to achieve this. Maybe someone else can help out?