I needed a way to show the last X posts from the latest threads and I couldn't find any existing extension that does it for FB 1.0.4 and J! 1.5.3
So I decided to hack the
fb latest posts module in order to do just that.
Steps involved:
1. cleanup/create table
| Code: |
drop procedure get_latest_thread_post;
drop table if exists jos_fb_latest_thread_post;
create table jos_fb_latest_thread_post select id, catid, name, userid, subject, time, topic_emoticon from jos_fb_messages where 1=0;
|
2. create stored procedure
| Code: |
create procedure get_latest_thread_post()
begin
DECLARE done INT DEFAULT 0;
DECLARE threadv int;
DECLARE latest_threads CURSOR FOR select thread from jos_fb_messages where parent=0 order by time desc limit 20;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN latest_threads;
/* initialise */
truncate table jos_fb_latest_thread_post;
FETCH latest_threads INTO threadv;
REPEAT
insert into jos_fb_latest_thread_post (id, catid, name, userid, subject, time, topic_emoticon)
select id, catid, name, userid, subject, time, topic_emoticon from jos_fb_messages
where thread = threadv
order by time desc
limit 1;
FETCH latest_threads INTO threadv;
UNTIL done END REPEAT;
CLOSE latest_threads;
end
|
3. modify helper.php -
backup first- just after the comment "// Get latest post from the DBase"
| Code: |
// Get latest post from the DBase
//ORIGINAL $query = ' SELECT id, catid,name, userid, subject, time, topic_emoticon FROM #__fb_messages ORDER BY time DESC LIMIT '.$postings.'';
//ORIGINAL $database -> setQuery( $query );
//ORIGINAL $rows = $database -> loadObjectList();
// call stored procedure which populates the table with the last 20 posts of latest threads
$query = 'call get_latest_thread_post';
$database -> setQuery( $query );
$database->query();
//$rows = $database -> loadObjectList();
$query = 'select id, catid, name, userid, subject, time, topic_emoticon from #__fb_latest_thread_post order by time desc limit '.$postings.'';
$database -> setQuery( $query );
$rows = $database -> loadObjectList();
|
Favoured: 0
