Forum OpenACS Q&A: Re: multirow insert?

could you help out with that query... I was working on it, but didn't understand all the depth of CR + I am using etp instead of modetp.

The main problems I had was limiting the results to exactly those that etp is actively using.

I eliminated the rows that didn't work with "--" and manually put in my root_item_id so that I can see the results in the db.

You basically order by tree_sortkey and limit it by (among others) "select tree_right(tree_sortkey)". If I do this only the root_item_id is being displayed. If I don't do it the results are too many and the external links are not being shown.

And what did you use the modetp_cr_content table for???

I think that this kind of navigation would really be useful for oacs (marketing ...

Cheers

#####

    select
        i.item_id,
        tree_level(i.tree_sortkey) - (select tree_level(tree_sortkey) from cr_items where item_id = 2157) as item_level,
        null as indent, -- set below
        etp__get_title(i.item_id, r.title) as title,
        i.name,
        --etp__get_absolute_url(i.item_id, i.name) as url,
        o.object_type
    from
        cr_items i
          left join cr_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 = 2157)
        and i.tree_sortkey >= (select tree_sortkey from cr_items where item_id = 2157)
        --and i.tree_sortkey < (select tree_right(tree_sortkey) from cr_items where item_id = 2157)
        --and (coalesce(c.level, r.level) != 0 or i.item_id = 2157)
        --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