Forum OpenACS Q&A: Re: multirow insert?

I remembered that Robert Locke send me this kinda code some time ago... The best way to get the root_package_id would be to enhance ad_context_bar to also return the package_ids within its list. This way you wouldn't have to perform redundant queries.

How would I have to change the following code? If nobody answers I will just work the code a little (lazy me ...

Will I need any kind of modetp code to use this with etp?

#####

set root_item_id [modetp::get_root_item_id [ad_conn package_id]]

db_multirow all_pages all_pages {
    select
        i.item_id,
        tree_level(i.tree_sortkey) - (select tree_level(tree_sortkey)
from
cr_items where item_id = :root_item_id) as item_level,
        null as indent, -- set below
        etp__get_title(i.item_id, r.title) as title,
        etp__get_absolute_url(i.item_id, i.name) as url,
        o.object_type
    from
        cr_items i left join modetp_revisions r on (i.live_revision =
r.revision_id)
        left join cr_modetp_content c on (i.item_id = c.content_id),
        acs_objects o
    where
        i.item_id = o.object_id
        and o.object_type in ('content_item', 'content_folder')
        and (i.name != 'index' or i.item_id = :root_item_id)
        and i.tree_sortkey >= (select tree_sortkey from cr_items where
item_id = :root_item_id)
        and i.tree_sortkey < (select tree_right(tree_sortkey) from
cr_items
where item_id = :root_item_id)
        and (coalesce(c.level, r.level) != 0 or i.item_id =
:root_item_id)
        and (coalesce(c.live_date, r.live_date) is null
            or current_timestamp > coalesce(c.live_date, r.live_date))
        and (coalesce(c.archive_date, r.archive_date) is null
            or current_timestamp < coalesce(c.archive_date,
r.archive_date))
        order by
            i.tree_sortkey
} {
    set indent [string repeat "&nbsp;" [expr $item_level * 4]]
}