news module admin pagination?

qzy · **Posted:** Sun Apr 01, 2007 10:14 am

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 · **Posted:** Sun Apr 01, 2007 2:12 pm

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 · **Joined:** Sun Jan 07, 2007 8:54 pm **Posts:** 267

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 · **Posted:** Sun Apr 01, 2007 4:16 pm

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 · **Posted:** Sun Apr 01, 2007 4:23 pm

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 · **Posted:** Sun Apr 01, 2007 4:25 pm

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 · **Joined:** Sun Jan 07, 2007 8:54 pm **Posts:** 267

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 · **Posted:** Sun Apr 01, 2007 5:53 pm

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

Vin · **Joined:** Sun Jan 07, 2007 8:54 pm **Posts:** 267

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:

<?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 · **Posted:** Thu Apr 05, 2007 10:05 pm

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 · **Joined:** Mon Jun 12, 2006 6:00 pm **Posts:** 227

Exactly what I needed. Thank very much, Vin.

cyberman · **Posted:** Fri Apr 06, 2007 4:13 am

Vin wrote:

Found a solution. Not approved by the developers though.

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

.

Vin · **Joined:** Sun Jan 07, 2007 8:54 pm **Posts:** 267

I thought I should

stopsatgreen · **Posted:** Fri May 25, 2007 10:57 am

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

Thanks

news module admin pagination?

Who is online