Forum OpenACS Development: Re: Trying to fix problem with Last Post date in forums

Collapse
Posted by Raad Al-Rawi on
Janine

I came across the "last post date" issue with the instance of dotLRN/SloanSpace we started running back in January.
I fixed it by changing the "messages_select" query in "default-view-oracle" to pick up the latest post to the thread. The fix is not as proper as the real solution, and there is undoubtedly a small performance hit; but if you are struggling and getting nowhere you could use the query for display purposes until you find the cause. I've posted the whole query, including the bit for last_modified.

Of course it might not fit in with how things currently stand in forums, but it was just a thought 😊

<Raad>

select fm.message_id,
fm.subject,
fm.user_id,
person.name(fm.user_id) as user_name,
fm.posting_date,
fm.state,
(
select count(*)
from forums_messages_approved fm1
where fm1.forum_id = :forum_id
and fm1.tree_sortkey between tree.left(fm.tree_sortkey)
and
tree.right(fm.tree_sortkey)
) as n_messages,
nvl((
select to_char(ao2.last_modified, 'Mon DD YYYY HH24:MI:SS')
from acs_objects ao2,
forums_messages_approved fm2
where ao2.object_id = fm2.message_id
and fm2.forum_id = fm.forum_id
and fm2.tree_sortkey =
(
select max(fm3.tree_sortkey)
from forums_messages_approved fm3
where fm3.forum_id = fm2.forum_id
and fm.parent_id is null
and fm3.tree_sortkey between tree.left(fm.tree_sortkey)
and
tree.right(fm.tree_sortkey)
)
), to_char(ao.last_modified, 'Mon DD YYYY HH24:MI:SS')) as last_modified,
case when ao.last_modified > (sysdate - 1) then 't' else 'f' end as new_p
from forums_messages_approved fm,
acs_objects ao
where fm.forum_id = :forum_id
and fm.parent_id is null
and fm.message_id = ao.object_id
order by fm.posting_date desc