news module admin pagination?

[qzy]

Hello all,


I've just migrated a site having more than 4000 articles to cmsms. What I noticed after migration is that the news module admin interface lists _all_ articles on one page, if no category is selected (however it cannot be selected until the page appears for at least once). Having this number of articles brings incredible performance penalty as the page needs at least 70 seconds to be generated, which just makes it totaly unusable (this way). As a workaround I've added a LIMIT 100 postfix to the mysql query when category is empty, but this is just a dirty hack. It would strongly be appreciated if a pagination feature could be implemented so that large number of articles can be splitted to smaller units. Or is there a way to do it using existing features?

Another note is that by default there is no index defined for the news_category_id and news_date rows of the module_news table, while they appear as part of a join and an order by expression.


Thanks for any advice,
Balint

[calguy1000]

Yeah, pagination in the news admin would be nice, and no, there's probably no way to do it right now.  Could you please file a feature request in the tracker for news so we don't lose track of this.

[Vin]

In fact, Elijah Lofgren have decided not to implement the pagination for the News, but for the Calendar (a month pagination) -
http://forum.cmsmadesimple.org/index.ph ... 199.0.html
http://forum.cmsmadesimple.org/index.ph ... 487.0.html
and you can use calendar as a blog  .

I tried it, but the fact it is combined with calendar makes it a little harder to understand (meaning, there's more smarty tags, and I'm not used to them). I'm a little annoyed because I've made effort to enhance the current UDT pagination for News recently and didn't realize there is possibly an alternative.

I dunno... it's nice that Elijah took care of it, but I can't stop asking myself whether this is a good approach. I mean, News and Calendar, although very similar and possibly cooperative, are not the same.

To Calguy: what does the note in News-about about pagination mean (changes to 1.6)

[calguy1000]

Vin.......  qzy was talking about pagination in the admin, not the frontend.... afaik, elijahs calendar stuff and the udt methods solve pagination on the frontend, not the backend.

[qzy]

Hi,

Calguy1000: feature request added. However there was a similiar request (actually for frontend pagination, which is yes, a bit different) from early 2006, so chances are not too good, i think...

Anyway: must we do....? 

[calguy1000]

The pagination stuff isn't hard to do (for the frontend, or the backend)..... I'm just a little busy lining up paid projects at the moment.  It'd probably only take me about 2 or 3 hours to do.

[Vin]

Elijah did mention he wanted to implement the backend pagination, but whether it is solved by now, I don't know...
...anyway, I could try to split the news into multiple categories to simulate it, but this would require setting news of a default category instead of all categories to display on the frontend. And I'm not sure how to implement it. Will give it a try though...

[qzy]

Wow!
Anyhow, if the feature once appeared in the module, many people would really be thankful, including me.

[Vin]

Found a solution. Not approved by the developers though. (I PMed calguy, but he hasn't responded yet.)
Just copy 'n paste this into the action.defaultadmin.php in your News module folder.

Code: Select all

<?php
if (!isset($gCms)) exit;
#The tabs
echo $this->StartTabHeaders();
if (FALSE == empty($params['active_tab']))
{
	$tab = $params['active_tab'];
} else {
	$tab = '';
}
if ($this->CheckPermission('Modify News'))
{
	echo $this->SetTabHeader('articles',$this->Lang('articles'), ('articles' == $tab)?true:false);
	echo $this->SetTabHeader('categories',$this->Lang('categories'), ('categories' == $tab)?true:false);
}
					
if ($this->CheckPermission('Modify Templates'))
{
	echo $this->SetTabHeader('summary_template',$this->Lang('summarytemplate'), ('summary_template' == $tab)?true:false);
	echo $this->SetTabHeader('detail_template',$this->Lang('detailtemplate'), ('detail_template' == $tab)?true:false);
}
			
if ($this->CheckPermission('Modify Site Preferences'))
{
	echo $this->SetTabHeader('options',$this->Lang('options'), ('options' == $tab)?true:false);
}
echo $this->EndTabHeaders();

#The content of the tabs
echo $this->StartTabContent();
if ($this->CheckPermission('Modify News'))
{
	echo $this->StartTab('articles', $params);
	echo $this->CreateFormStart($id, 'defaultadmin');
	
	$curcategory = (isset($params['curcategory'])?$params['curcategory']:'');
	$allcategories = (isset($params['allcategories'])?$params['allcategories']:'no');
	$newcategory = $curcategory;
	
	if (isset($params['submitcategory']))
	{
		$newcategory = (isset($params['newcategory'])?$params['newcategory']:$newcategory);
	}
	
	$curcategory = $newcategory;
	$categorylist = array();
	$categorylist[$this->Lang('allcategories')] = '';
	$query = "SELECT * FROM ".cms_db_prefix()."module_news_categories ORDER BY hierarchy";
	$dbresult = $db->Execute($query);
	
	while ($dbresult && $row = $dbresult->FetchRow())
	{
		$categorylist[$row['long_name']] = $row['long_name'];
	}
	
	echo '<p>'.$this->Lang('category').': ' . $this->CreateInputDropdown($id, 'newcategory', $categorylist, -1, $newcategory) . ' ' . $this->Lang('showchildcategories') . ': ' . $this->CreateInputCheckbox($id, 'allcategories', 'yes', $allcategories) . ' ' . $this->CreateInputSubmit($id, 'submitcategory', $this->Lang('selectcategory')) . $this->CreateInputHidden($id, 'curcategory', $curcategory) . '</p>';
	
	echo $this->CreateFormEnd();
/*paste*/
$page = 1;
if (isset($_GET['m1_page']))$page = $_GET['m1_page'];

$result = $db->Execute("SELECT news_id FROM ".cms_db_prefix()."module_news");
$totalrows = $result->RecordCount();

$limit = 20;
$page_string = "";
$from = ($page * $limit) - $limit;

if ($result && $result->RecordCount() > 0) {
	$page_string = $this->CreatePagination($id, 0, $returnid, $page, $totalrows, $limit);
	echo "<p class=\"pageshowrows\">".$page_string."</p>";
  }
/*paste*/	
	//Load the current articles
	$entryarray = array();
	
	$query = '';
	$dbresult = '';
/*Replaced $db->Execute to $db->SelectLimit by Vin*/	
	if ($curcategory != '')
	{
		$query = "SELECT n.*, nc.long_name FROM ".cms_db_prefix()."module_news n LEFT OUTER JOIN ".cms_db_prefix()."module_news_categories nc ON n.news_category_id = nc.news_category_id WHERE nc.long_name LIKE ? ORDER by news_date DESC";
		if ($allcategories == 'yes')
		{
			$dbresult = $db->SelectLimit($query, $limit, $from, array($curcategory . '%'));
		}
		else
		{
			$dbresult = $db->SelectLimit($query, $limit, $from, array($curcategory));
		}
	}
	else
	{
		$query = "SELECT n.*, nc.long_name FROM ".cms_db_prefix()."module_news n LEFT OUTER JOIN ".cms_db_prefix()."module_news_categories nc ON n.news_category_id = nc.news_category_id ORDER by news_date DESC";
		$dbresult = $db->SelectLimit($query, $limit, $from);
	}
/*End of replacement by Vin*/	
	
	$rowclass = 'row1';
	
	while ($dbresult && $row = $dbresult->FetchRow())
	{
		$onerow = new stdClass();
	
		$onerow->id = $row['news_id'];
		$onerow->title = $this->CreateLink($id, 'editarticle', $returnid, $row['news_title'], array('articleid'=>$row['news_id']));
		$onerow->data = $row['news_data'];
		$onerow->postdate = $row['news_date'];
		$onerow->startdate = $row['start_time'];
		$onerow->enddate = $row['end_time'];
		$onerow->category = $row['long_name'];
		$onerow->rowclass = $rowclass;
	
		$onerow->editlink = $this->CreateLink($id, 'editarticle', $returnid, $gCms->variables['admintheme']->DisplayImage('icons/system/edit.gif', $this->Lang('edit'),'','','systemicon'), array('articleid'=>$row['news_id']));
		$onerow->deletelink = $this->CreateLink($id, 'deletearticle', $returnid, $gCms->variables['admintheme']->DisplayImage('icons/system/delete.gif', $this->Lang('delete'),'','','systemicon'), array('articleid'=>$row['news_id']), $this->Lang('areyousure'));
	
		$entryarray[] = $onerow;
	
		($rowclass=="row1"?$rowclass="row2":$rowclass="row1");
	}
	
	$this->smarty->assign_by_ref('items', $entryarray);
	$this->smarty->assign('itemcount', count($entryarray));
	
	$this->smarty->assign('addlink', $this->CreateLink($id, 'addarticle', $returnid, $gCms->variables['admintheme']->DisplayImage('icons/system/newobject.gif', $this->Lang('addarticle'),'','','systemicon'), array(), '', false, false, '') .' '. $this->CreateLink($id, 'addarticle', $returnid, $this->Lang('addarticle'), array(), '', false, false, 'class="pageoptions"'));
	$this->smarty->assign('titletext', $this->Lang('title'));
	$this->smarty->assign('postdatetext', $this->Lang('postdate'));
	$this->smarty->assign('categorytext', $this->Lang('category'));
	#Display template
	echo $this->ProcessTemplate('articlelist.tpl');
	
	echo $this->EndTab();
	
	echo $this->StartTab('categories', $params);
	
	#Put together a list of current categories...
	$entryarray = array();
	
	$query = "SELECT * FROM ".cms_db_prefix()."module_news_categories ORDER BY hierarchy";
	$dbresult = $db->Execute($query);
	
	$rowclass = 'row1';
	
	while ($dbresult && $row = $dbresult->FetchRow())
	{
		$onerow = new stdClass();
	
		$depth = count(split('\.', $row['hierarchy']));
	
		$onerow->id = $row['news_category_id'];
		$onerow->name = str_repeat(' ', $depth-1).$this->CreateLink($id, 'editcategory', $returnid, $row['news_category_name'], array('catid'=>$row['news_category_id']));
	
		$onerow->editlink = $this->CreateLink($id, 'editcategory', $returnid, $gCms->variables['admintheme']->DisplayImage('icons/system/edit.gif', $this->Lang('edit'),'','','systemicon'), array('catid'=>$row['news_category_id']));
		$onerow->deletelink = $this->CreateLink($id, 'deletecategory', $returnid, $gCms->variables['admintheme']->DisplayImage('icons/system/delete.gif', $this->Lang('delete'),'','','systemicon'), array('catid'=>$row['news_category_id']), $this->Lang('areyousure'));
	
		$onerow->rowclass = $rowclass;

		$entryarray[] = $onerow;
	
		($rowclass=="row1"?$rowclass="row2":$rowclass="row1");
	}
	
	$this->smarty->assign_by_ref('items', $entryarray);
	$this->smarty->assign('itemcount', count($entryarray));
	
	#Setup links
	$this->smarty->assign('addlink', $this->CreateLink($id, 'addcategory', $returnid, $this->Lang('addcategory'), array(), '', false, false, 'class="pageoptions"'));
	$this->smarty->assign('addlink', $this->CreateLink($id, 'addcategory', $returnid, $gCms->variables['admintheme']->DisplayImage('icons/system/newfolder.gif', $this->Lang('addcategory'),'','','systemicon'), array(), '', false, false, '') .' '. $this->CreateLink($id, 'addcategory', $returnid, $this->Lang('addcategory'), array(), '', false, false, 'class="pageoptions"'));
	
	$this->smarty->assign('categorytext', $this->Lang('category'));
	
	#Display template
	echo $this->ProcessTemplate('categorylist.tpl');
	
	echo $this->EndTab();
}
if( $this->CheckPermission( 'Modify Templates' ) )
{
	echo $this->StartTab('summary_template', $params);
	
	echo $this->CreateFormStart($id, 'updatesummarytemplate');
	
	echo '<p>'.$this->CreateTextArea(false, $id, $this->GetTemplate('displaysummary'), 'templatecontent', 'pagebigtextarea').'</p>';
	
	echo $this->CreateInputSubmit($id, 'submitbutton', $this->Lang('submit'));
	echo $this->CreateInputSubmit($id, 'defaultsbutton', $this->Lang('sysdefaults'), '', '', $this->Lang('restoretodefaultsmsg'));
	
	echo $this->CreateFormEnd();
	
	echo $this->EndTab();
	
	echo $this->StartTab('detail_template', $params);
	
	echo $this->CreateFormStart($id, 'updatedetailtemplate');
	
	echo '<p>'.$this->CreateTextArea(false, $id, $this->GetTemplate('displaydetail'), 'templatecontent2', 'pagebigtextarea').'</p>';
	
	echo $this->CreateInputSubmit($id, 'rsssubmitbutton', $this->Lang('submit'));
	echo $this->CreateInputSubmit($id, 'defaultsbutton', $this->Lang('sysdefaults'), '', '', $this->Lang('restoretodefaultsmsg'));
	
	echo $this->CreateFormEnd();
	
	echo $this->EndTab();
}

if ($this->CheckPermission('Modify Site Preferences'))
{
	echo $this->StartTab('options', $params);
	
	// CreateFormStart sets up a proper form tag that will cause the submit to
	// return control to this module for processing.
	$this->smarty->assign('startform', $this->CreateFormStart ($id, 'updateoptions', $returnid));
	$this->smarty->assign('endform', $this->CreateFormEnd ());

	$this->smarty->assign ('title_showautodiscovery', $this->Lang('showautodiscovery'));
	$this->smarty->assign('input_showautodiscovery', $this->CreateInputCheckbox($id, 'showautodiscovery', 'yes', $this->GetPreference('showautodiscovery', 'yes')));

	$this->smarty->assign ('title_autodiscoverylink', $this->Lang('autodiscoverylink'));
	$this->smarty->assign('input_autodiscoverylink', $this->CreateInputText($id, 'autodiscoverylink', $this->GetPreference('autodiscoverylink', ''), '50', '255'));

	$this->smarty->assign('title_dateformat', $this->Lang('helpdateformat'));
	$this->smarty->assign('input_dateformat', $this->CreateInputText($id, 'dateformat', $this->GetPreference('dateformat', ''), '50', '255'));

	$categorylist = array();
	$query = "SELECT * FROM ".cms_db_prefix()."module_news_categories ORDER BY hierarchy";
	$dbresult = $db->Execute($query);

	while ($dbresult && $row = $dbresult->FetchRow())
	{
		$categorylist[$row['long_name']] = $row['news_category_id'];
	}

	$this->smarty->assign('title_default_category', $this->Lang('default_category'));
	$this->smarty->assign('input_default_category', $this->CreateInputDropdown($id, 'default_category', $categorylist, -1, $this->GetPreference('default_category', '')));

	$this->smarty->assign('submit', $this->CreateInputSubmit ($id, 'optionssubmitbutton', $this->Lang('submit')));

	// Display the populated template
	echo $this->ProcessTemplate ('adminprefs.tpl');
	echo $this->EndTab();
}

echo $this->EndTabContent();

# vim:ts=4 sw=4 noet
?>

The added pagination code is between the /*paste*/ comments, and replaced Execute methods to SelectLimit. If you want to have a bigger limit of pages shown per page, just change the value of the $limit variable.

I believe there ARE people who will appreciate THIS!
(especially those who can't afford extra memory limit)

[qzy]

Hi Vin,


the code looks nice, I'll give it a try, and will report on the experiences. Thanks for your work and time!

[mahjong]

Exactly what I needed. Thank very much, Vin.

[cyberman]

Found a solution. Not approved by the developers though.

Thanks for that - maybe you can post this solution as patch on project forge  .

[Vin]

I thought I should

[stopsatgreen]

While I wait for a stable release, this could be just the ticket!

Thanks