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: image

Query builder result:

image

About this issue

  • Original URL
  • State: closed
  • Created 8 years ago
  • Comments: 19 (15 by maintainers)

Most upvoted comments

This can be closed. It’s implemented in 3.2.x