Forum OpenACS Q&A: multirow insert?

Hi David,

It would be helpful if you gave an example of what you're trying to achieve.
Are your multirows coming from 2 select statements? If so, maybe you can achieve what you want by having just 1 select statement and using a join. Then use the GROUP tag in your adp .

I can expand on this if I haven't given enough detail.

Brian

You can append to a multirow, but in this case I believe you should write one query that returns all the rows you will need.

Doing the seperate query for each package_id will be inefficient. I believe the results of the get_content_items procedure may be cached, but it still seems like the wrong way to go about it.

Another option would be to allow get_content_items to accept a list of package_ids or have it append to the multirow name that you pass in if the multirow already exists.

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]]
}

Hi David,

I am sorry I am little time right now.  But the basic query is like this.

select                      i.item_id,
                            i.name,
                            i.parent_id,
                            tree_level(i.tree_sortkey) as level,
                            tree_level(p.tree_sortkey) as parent_level,
                    from cr_items i,
                        (select item_id, tree_sortkey from cr_items where item_id = :parent_id) p
                    where
                        and i.tree_sortkey between p.tree_sortkey and tree_right(p.tree_sortkey)
                    order by i.tree_sortkey

That should display a subtree of CR.  Take note that tree_sortkey is not a CR issue.  Its the way oacs was ported in pg.

I think Rob added a table to make the navigation.  When I was working on modetp, I simply had rules and derived the navigation from it.  Rob made a table to explicitly put which items are in navigation or not.