Two things I did to get it working.
First the create a UDT named prev_next (actually changed the code used with news in another topic):
Code: Select all
if (!function_exists('MyGetModuleInstance'))
{
function &MyGetModuleInstance($module)
{
global $gCms;
if (isset($gCms->modules[$module]) &&
$gCms->modules[$module]['installed'] == true &&
$gCms->modules[$module]['active'] == true)
{
return $gCms->modules[$module]['object'];
}
// Fix only variable references should be returned by reference
$tmp = FALSE;
return $tmp;
}
}
$gCms = cmsms();
$db = cmsms()->GetDb(); // changed this line.
$cgblog = cms_utils::get_module('CGBlog'); // changed this line.
// call from cgblog detail_template: {prev_next pageid=$page_id currid=$entry->id currdate=$entry->postdate}
$pageid = ($_REQUEST['pageid']) ? $_REQUEST['pageid'] : $params['pageid'];
$currid = ($_REQUEST['currid']) ? $_REQUEST['currid'] : $params['currid']; // added this line for testing purposes.
$currdate = $params['currdate'];
if(!$currdate) return;
// call from cgblog detail_template: {prev_next pageid=$page_id currid=$entry->id currdate=$entry->postdate}
// For performance, added an index over two columns on ".cms_db_prefix()."module_cgblog_blog_categories
// get all CGBlog articles sorted by ascending date
$query_next = "
SELECT cgblog_id, cgblog_title
FROM ".cms_db_prefix()."module_cgblog
WHERE cgblog_date > ?
AND status = 'published'
AND (end_time >= CURRENT_DATE OR end_time IS NULL)
AND cgblog_id IN
((SELECT blog_id
FROM ".cms_db_prefix()."module_cgblog_blog_categories
WHERE category_id IN
(SELECT category_id
FROM ".cms_db_prefix()."module_cgblog_blog_categories
WHERE blog_id = ".$currid.")))
ORDER BY cgblog_date
ASC LIMIT 1";
$result_next =& $db->GetRow($query_next,array($currdate));
$query_prev = "
SELECT cgblog_id, cgblog_title
FROM ".cms_db_prefix()."module_cgblog
WHERE cgblog_date < ?
AND status = 'published'
AND (end_time >= CURRENT_DATE OR end_time IS NULL)
AND cgblog_id IN
((SELECT blog_id
FROM ".cms_db_prefix()."module_cgblog_blog_categories
WHERE category_id IN
(SELECT category_id
FROM ".cms_db_prefix()."module_cgblog_blog_categories
WHERE blog_id = ".$currid.")))
ORDER BY cgblog_date
DESC LIMIT 1";
// end of get all CGBlog items
$result_prev = &$db->GetRow($query_prev,array($currdate));
if($result_next['cgblog_id']) {
$aliased_title = munge_string_to_url($result_next['cgblog_title']);
$prettyurl = 'logboek/' . $result_next["cgblog_id"] .'/'.$pageid."/$aliased_title";
$next_uri = $cgblog->CreateLink('cntnt01', 'detail', $pageid, '', array('articleid' => $result_next["cgblog_id"]) ,'', true, false, '', true, $prettyurl);
}else{
$next_uri = "";
}
if($result_prev['cgblog_id']) {
$aliased_title = munge_string_to_url($result_prev['cgblog_title']);
$prettyurl = 'logboek/' . $result_prev["cgblog_id"] .'/'.$pageid."/$aliased_title";
$prev_uri = $cgblog->CreateLink('cntnt02', 'detail', $pageid, '', array('articleid' => $result_prev["cgblog_id"]) ,'', true, false, '', true, $prettyurl);
}else{
$prev_uri = "";
}
$smarty->assign('cgblog_next_url', $next_uri);
$smarty->assign('cgblog_next_text', $cgblog->lang("next"));
$smarty->assign('cgblog_prev_url', $prev_uri);
$smarty->assign('cgblog_prev_text', $cgblog->lang("prev"));
type of index is 'Primary'. This helped to reduce the execution time from 15sec to far less than a second.cms_module_cgblog_blog_categories
A working example can be found at my site http://www.uisge-beatha.eu/logboek/270/ ... trijd.html
If one sees any improvements or suggestions, please let me know.
Gregor