Jun,
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
