Difference between revisions of "Issue:Count and %PAGES%"

Latest revision as of 00:52, 29 February 2008

Problem

When using the 'count' parameter, %PAGES% only shows the number of rows retrieved, not the total number of pages in the query.

Could it use SQL_CALC_FOUND_ROWS and FOUND_ROWS() to retrieve the total? This would allow "Showing xx pages of yyyy" style headings.

Or am I just doing something wrong?

Cheers,

Eclecticdave 02:56, 26 February 2008 (CET)

Reply

Indeed this is a known flaw. I wasn´t aware of the variables or functions you are mentionig. Could you send me a patch how to do it. I would then publish a new release ...

Gero 22:42, 25 February 2008 (CET)

Patch

Sure, I threw this together over the last couple of hours, so it might have some rough edges. Seems to work for me at least ;-)

The one refinement that's probably needed is an option to turn it on/off. This change can make things slower for larger rowsets, as MYSQL has to run the whole query internally to figure out how many rows would have been returned without the LIMIT.

Eclecticdave 02:55, 26 February 2008 (CET)

diff --git a/DynamicPageList2.php b/DynamicPageList2.php
index 0f3b987..0217ee2 100644
--- a/DynamicPageList2.php
+++ b/DynamicPageList2.php
@@ -2257,6 +2257,10 @@ class ExtDynamicPageList2
         $sSqlPage_counter = '';
         $sSqlPage_size = '';
         $sSqlPage_touched = '';
+				if ( isset($iCount) && $sGoal != 'categories' )
+					$sSqlCalcFoundRows = 'SQL_CALC_FOUND_ROWS';
+				else $sSqlCalcFoundRows = '';
+
         if ($sDistinctResultSet == 'false') $sSqlDistinct = '';
         else								$sSqlDistinct = 'DISTINCT';
         $sSqlGroupBy = '';
@@ -2577,9 +2581,9 @@ class ExtDynamicPageList2
         
         // SELECT ... FROM
         if ($acceptOpenReferences)
-            $sSqlSelectFrom = "SELECT $sSqlDistinct " . $sSqlCl_to . 'pl_namespace, pl_title' . $sSqlSelPage . $sSqlSortkey . ' FROM ' . $sPageLinksTable;
+            $sSqlSelectFrom = "SELECT $sSqlCalcFoundRows $sSqlDistinct " . $sSqlCl_to . 'pl_namespace, pl_title' . $sSqlSelPage . $sSqlSortkey . ' FROM ' . $sPageLinksTable;
         else
-            $sSqlSelectFrom = "SELECT $sSqlDistinct " . $sSqlCl_to . $sPageTable.'.page_namespace as page_namespace,'.
+            $sSqlSelectFrom = "SELECT $sSqlCalcFoundRows $sSqlDistinct " . $sSqlCl_to . $sPageTable.'.page_namespace as page_namespace,'.
             					$sPageTable.'.page_title as page_title' . $sSqlSelPage . $sSqlSortkey . $sSqlPage_counter .
                                 $sSqlPage_size . $sSqlPage_touched . $sSqlRev_user .
                                 $sSqlRev_timestamp . $sSqlRev_id . $sSqlCats . $sSqlCl_timestamp . 
@@ -2975,6 +2979,14 @@ class ExtDynamicPageList2
             $aArticles[] = $dplArticle;
         }
         $dbr->freeResult( $res );
+
+				$rowcount = -1;
+				if ( isset($iCount) && $sGoal != 'categories' ) {
+					$res = $dbr->query('SELECT FOUND_ROWS() as rowcount');
+					$row = $dbr->fetchObject ( $res );
+					$rowcount = $row->rowcount;
+					$dbr->freeResult( $res );
+				}
     
     // ###### SHOW OUTPUT ######
     
@@ -2989,25 +3001,27 @@ class ExtDynamicPageList2
                         $aSecLabels, $aSecLabelsMatch, $aSecLabelsNotMatch, $bIncParsed, $parser, $logger, $aReplaceInTitle, 
                         $iTitleMaxLen, $defaultTemplateSuffix, $aTableRow);
                         
+				if ($rowcount == -1) $rowcount = $dpl->getRowCount();
+
         $dpl2result = $dpl->getText();
         $header='';
-        if ($sOneResultHeader != '' && $dpl->getRowCount()==1) {
+        if ($sOneResultHeader != '' && $rowcount==1) {
             $header = str_replace('%PAGES%',1,$sOneResultHeader);
-        } else if ($dpl->getRowCount()==0) {
+        } else if ($rowcount==0) {
             if ($sNoResultsHeader != '')	$output .= 	str_replace( '\n', "\n", str_replace( "¶", "\n", $sNoResultsHeader));
             if ($sNoResultsFooter != '')	$output .= 	str_replace( '\n', "\n", str_replace( "¶", "\n", $sNoResultsFooter));
             if ($sNoResultsHeader == '' && $sNoResultsFooter == '')	$output .= $logger->escapeMsg(DPL2_i18n::WARN_NORESULTS);
         }
         else {
-            if ($sResultsHeader != '')	$header = str_replace('%PAGES%',$dpl->getRowCount(),$sResultsHeader);
+            if ($sResultsHeader != '')	$header = str_replace('%PAGES%',$rowcount,$sResultsHeader);
         }
         $header = str_replace( '\n', "\n", str_replace( "¶", "\n", $header ));
         $header = str_replace('%VERSION%', self::VERSION,$header);
         $footer='';
-        if ($sOneResultFooter != '' && $dpl->getRowCount()==1) {
+        if ($sOneResultFooter != '' && $rowcount==1) {
             $footer = str_replace('%PAGES%',1,$sOneResultFooter);
         } else {
-            if ($sResultsFooter != '')	$footer = str_replace('%PAGES%',$dpl->getRowCount(),$sResultsFooter);
+            if ($sResultsFooter != '')	$footer = str_replace('%PAGES%',$rowcount,$sResultsFooter);
         }
         $footer = str_replace( '\n', "\n", str_replace( "¶", "\n", $footer ));
         $footer = str_replace('%VERSION%', self::VERSION, $footer);
@@ -3914,4 +3928,4 @@ class DPL2Logger {
 
 }
 
-?>
\ No newline at end of file
+?>

Resolved

Thanks! I added %TOTALPAGES% which triggers the extra SQL statement. Thus it is only called when needed. A test case demonstrates the usage.

Gero 18:38, 26 February 2008 (CET)

Oops

I've just discovered a bug, caused by these changes.

My patch causes an extra space to be added between SELECT and DISTINCT when SQL_CALC_FOUND_ROWS is not in effect, which means the regexp at line 2775 no longer matches. I fixed it here by adding the extra space in the regexp.

diff --git a/DynamicPageList3.php b/DynamicPageList2.php
index aac4b53..5f02119 100644
--- a/DynamicPageList2.php
+++ b/DynamicPageList2.php
@@ -2772,7 +2772,7 @@ class ExtDynamicPageList2
          

         if ($sGoal=='categories') {

             $sSqlSelectFrom = 'select distinct cl3.cl_to from '.$sCategorylinksTable.' as cl3 where cl3.cl_from in ( ' . 

-                                preg_replace('/SELECT DISTINCT .* FROM /','SELECT DISTINCT '.$sPageTable.'.page_id FROM ',$sSqlSelectFrom);

+                                preg_replace('/SELECT  DISTINCT .* FROM /','SELECT DISTINCT '.$sPageTable.'.page_id FROM ',$sSqlSelectFrom);

             if ($sOrder == 'descending')	$sSqlWhere .= ' ) order by cl3.cl_to DESC';

             else							$sSqlWhere .= ' ) order by cl3.cl_to ASC';

         }

Eclecticdave 00:36, 29 February 2008 (CET)