cphalcon: Having in query builder breaks total pages and total items of paginator
Using having of a query builder forces it’s paginator to show total pages and total items as if there was no having set.
Issue confirmed in 2.1.x and 3.0.0 on windows 7, php 5.6
How to reproduce: /bootstrap.php
use Phalcon\Loader;
use Phalcon\Mvc\View;
use Phalcon\Mvc\Application;
use Phalcon\Di\FactoryDefault;
use Phalcon\Db\Adapter\Pdo\Mysql as DbAdapter;
// Register an autoloader
$loader = new Loader();
$loader->registerDirs(array(
'models/',
))->register();
$di = new FactoryDefault();
$di->set('view', function () {
$view = new View();
$view->setViewsDir('../app/views/');
return $view;
});
$di->set('url', function () {
$url = new UrlProvider();
return $url;
});
$di->set('db', function () {
return new DbAdapter(array(
"host" => "localhost",
"username" => "root",
"password" => "",
"dbname" => "phalcon_test"
));
});
// Setup the view component
$application = new Application($di);
// Handle the request
//$response = $application->handle();
$query = new \Phalcon\Mvc\Model\Query\Builder();
$query->setDI($di);
$query->columns('*, COUNT(*) as stock_count');
$query->from('A');
$query->groupBy('name');
$query->having('SUM(A.stock) > 0');
var_dump($query->getQuery()->getSql());
$paginator = new \Phalcon\Paginator\Adapter\QueryBuilder(array(
"builder" => $query,
"limit" => 1,
"page" => 2
));
var_dump($paginator->getPaginate());
//$response->send();
/model/a.php:
class A extends Phalcon\Mvc\Model
{
}
sql database dump:
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
CREATE TABLE `a` (
`id` int(11) NOT NULL,
`name` varchar(32) NOT NULL,
`stock` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `a` (`id`, `name`, `stock`) VALUES
(1, 'Apple', 2),
(2, 'Carrot', 6),
(3, 'pear', 0);
ALTER TABLE `a`
ADD PRIMARY KEY (`id`);
ALTER TABLE `a`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
Expected result:

Query builder result:

About this issue
- Original URL
- State: closed
- Created 8 years ago
- Comments: 19 (15 by maintainers)
This can be closed. It’s implemented in 3.2.x