Pagination issue [Fixed in jD3.2.13]

[Arno]

@ Olivier,
@ Colin,
many thanks for the hints. I think i have found the problem and will publish later today a fixed version. 

[ColinM]

@Oliver  Yes  reversing #3945 does fix problem

Colin

[ColinM]

Arno
A couple og people are working the issue see
https://github.com/joomla/joomla-cms/issues/4472

If it is any help the part of Joomla mod #3945 that causes problem are the lines marked //*

Code Select Expand

public function setQuery($query, $offset = 0, $limit = 0)
{
$this->sql = $query;

if ($query instanceof JDatabaseQueryLimitable)
{
// start of #3945
//* if (!$limit && $query->limit)
//* {
//* $limit = $query->limit;
//* }

if (!$offset && $query->offset)
{
$offset = $query->offset;
}
// end of #3945
$query->setLimit($limit, $offset);
}
else
{
$this->limit = (int) max(0, $limit);
$this->offset = (int) max(0, $offset);
}

return $this;
}

Colin

[ColinM]

Hi
The Joomla people have found the error and have a patch.

Joomla fix  Clear limit for all JDatabaseQuery objects #4485
https://github.com/joomla/joomla-cms/pull/4485/files for changed code
https://github.com/joomla/joomla-cms/pull/4485 for explanation

libraries/legacy/model/legacy.php  circa line 315
should now read as below

Code Select Expand

protected function _getListCount($query)
{
// Use fast COUNT(*) on JDatabaseQuery objects if there is no GROUP BY or HAVING clause:
if ($query instanceof JDatabaseQuery
&& $query->type == 'select'
&& $query->group === null
&& $query->having === null)
{
$query = clone $query;
$query->clear('select')->clear('order')->clear('limit')->select('COUNT(*)');

$this->_db->setQuery($query);
return (int) $this->_db->loadResult();
}

// Otherwise fall back to inefficient way of counting all results.

// Remove the limit part if it's a JDatabaseQuery object
                if ($query instanceof JDatabaseQuery)
                {
                       $query = clone $query;
                       $query->clear('limit');
                }
               
$this->_db->setQuery($query);
$this->_db->execute();

return (int) $this->_db->getNumRows();
}

[Arno]

Hi,
thanks for the informations. I have it also read on GitHub.

I have now online a new 3.2.13 beta version which worked with the original Joomla 3.3.6 files. So we must not wait on the official next Joomla version.

About the problem:
I had found out, that i have a few MySql 'group by' definitions in some from my queries. The changed core files in Joomla 3.3.4 had in this case wrong getTotal() values. But i have now seen, that the 'group by' clauses are not really required. So i have removed it and get now the correct results for the pagination.

Many thanks for your help again!