2.0.10: Fatal error with Query Builder + Count + Group By + Paginator #11395

hailie-rei · 2016-02-10T07:16:24Z

== See the next comment ==

I use Phalcon 2.0.8.

use Phalcon\Paginator\Adapter\QueryBuilder as PaginatorModel;

        $data = $this->getModelsManager()->createBuilder()
            ->from(array('a' => '\Db\Article'))
            ->columns(array(
                'a.*',
                'COUNT(ac.id) AS c'
            ))
            ->leftJoin('\Db\ArticleComment', 'a.id = ac.article_id', 'ac')
            ->where('a.status = :status:', array('status' => 'approved'))
            ->orderBy(array('a.date_post DESC', 'a.date_add DESC'))
            ->groupBy('a.id')
        ;
        $paginator = new PaginatorModel(array(
            'builder' => $data,
            'limit' => 10,
            'page' => 1
        ));
        $paginate = $paginator->getPaginate();
        var_dump($paginate->total_items); die;

returns: int(1)

Working example with data instead of query builder object for Paginator:

use Phalcon\Paginator\Adapter\Model as PaginatorModel;

        $data = $this->getModelsManager()->createBuilder()
            ->from(array('a' => '\Db\Article'))
            ->columns(array(
                'a.*',
                'COUNT(ac.id) AS c'
            ))
            ->leftJoin('\Db\ArticleComment', 'a.id = ac.article_id', 'ac')
            ->where('a.status = :status:', array('status' => 'approved'))
            ->orderBy(array('a.date_post DESC', 'a.date_add DESC'))
            ->groupBy('a.id')
            ->getQuery()->execute();
        $paginator = new PaginatorModel(array(
            'data' => $data,
            'limit' => 10,
            'page' => 1
        ));
        $paginate = $paginator->getPaginate();
        var_dump($paginate->total_items); die;

returns: int(62)

My tables:

`CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` tinytext NOT NULL,
  `text` mediumtext NOT NULL,
  `date_add` datetime NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` enum('new','approved','declined','deleted') NOT NULL,
  `view_count` int(11) NOT NULL DEFAULT '0',
  `keywords` text,
  `description` text,
  `date_post` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`),
  KEY `date_post` (`date_post`)
) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=utf8;

CREATE TABLE `article_comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `article_id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL DEFAULT '0',
  `user_name` tinytext,
  `comment` text NOT NULL,
  `date_add` datetime NOT NULL,
  `status` enum('new','approved','declined') NOT NULL DEFAULT 'new',
  PRIMARY KEY (`id`),
  KEY `index` (`article_id`,`parent_id`,`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

I guess problem is somewhere here: https://github.com/phalcon/cphalcon/blob/master/phalcon/paginator/adapter/querybuilder.zep#L189

Just to compare: https://github.com/phalcon/cphalcon/blob/master/phalcon/paginator/adapter/model.zep#L97 n is total items count. This code works.

hailie-rei · 2016-02-10T09:11:23Z

UPD: Upgraded to 2.0.10, and this code:

use Phalcon\Paginator\Adapter\QueryBuilder as PaginatorModel;

        $data = $this->getModelsManager()->createBuilder()
            ->from(array('a' => '\Db\Article'))
            ->columns(array(
                'a.*',
                'COUNT(ac.id) AS c'
            ))
            ->leftJoin('\Db\ArticleComment', 'a.id = ac.article_id', 'ac')
            ->where('a.status = :status:', array('status' => 'approved'))
            ->orderBy(array('a.date_post DESC', 'a.date_add DESC'))
            ->groupBy('a.id');
        ;
        $paginator = new PaginatorModel(array(
            'builder' => $data,
            'limit' => 10,
            'page' => $page
        ));
        $paginate = $paginator->getPaginate();
        var_dump($paginate->total_items); die;

causes an error:

Warning: Phalcon\Paginator\Adapter\QueryBuilder::getPaginate(): 
Invalid arguments supplied for fast_join() in ...
Article.php on line 32

Fatal error: 
Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: 
Syntax error or access violation: 
1064 You have an error in your SQL syntax;
 check the manual that corresponds to your MySQL server version for the right syntax to use near
 ') AS `rowcount` FROM `article` AS `a` LEFT JOIN `article_comment` AS `ac` ON `a' at line 1' 
in ...Article.php:32 

Stack trace: #0 [internal function]: PDOStatement->execute() 
#1 [internal function]: Phalcon\Db\Adapter\Pdo->executePrepared(Object(PDOStatement), Array, NULL) 
#2 [internal function]: Phalcon\Db\Adapter\Pdo->query('SELECT COUNT() ...', Array, NULL) 
#3 [internal function]: Phalcon\Mvc\Model\Query->_executeSelect(Array, Array, NULL) 
#4 [internal function]: Phalcon\Mvc\Model\Query->execute() 
#5 ...Article.php(32): Phalcon\Paginator\Adapter\QueryBuilder->getPaginate() 
#6

tobasar · 2016-02-11T05:13:15Z

#11390

Fix issue #11395 #11390

sergeyklay · 2016-02-24T08:43:11Z

@hailie-rei Can you please test now 2.0.x branch?

hailie-rei · 2016-02-24T18:39:59Z

@sergeyklay I followed these steps:

git clone git://github.com/phalcon/cphalcon.git
git checkout 2.0.x
cd cphalcon/build
sudo ./install
restart apache

Please correct me if I was wrong.

For my steps I received an error:

Warning: Phalcon\Paginator\Adapter\QueryBuilder::getPaginate(): Invalid arguments supplied for fast_join() in /Users/Lena/redlab/www/japanese/app/modules/Article/Models/Article.php on line 32

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS rowcount FROM article AS a LEFT JOIN article_comment AS ac ON `a' at line 1' in ...Article.php:32

Stack trace:
#0 [internal function]: PDOStatement->execute()
#1 [internal function]: Phalcon\Db\Adapter\Pdo->executePrepared(Object(PDOStatement), Array, NULL) #2 [internal function]: Phalcon\Db\Adapter\Pdo->query('SELECT COUNT() ...', Array, NULL)
#3 [internal function]: Phalcon\Mvc\Model\Query->_executeSelect(Array, Array, NULL)
#4 [internal function]: Phalcon\Mvc\Model\Query->execute()
#5 /Users/Lena/redlab/www/japanese/app/modules/Article/Models/Article.php(32): Phalcon\Paginator\Adapter\QueryBuilder->getPaginate()
#6 ...IndexController.php(23) in ...Article.php on line 32

sergeyklay · 2016-02-26T00:07:22Z

@hailie-rei Right now I tested this on Phalcon Forum DB schema: https://github.com/phalcon/forum/blob/2.0.x/schemas/forum.sql

/** @var \Phalcon\Mvc\Model\Query\Builder $builder */
$builder = $this->modelsManager->createBuilder()
    ->from(['p' => 'Phosphorum\Models\Posts'])
    ->columns([
        'p.*',
        'COUNT(p.id) AS c',
    ])
    ->leftJoin('Phosphorum\Models\PostsReplies', "p.id = pr.posts_id", 'pr')
    ->where('p.status = :status:', ['status' => 'A'])
    ->orderBy(['p.created_at DESC', 'p.edited_at DESC'])
    ->groupBy('p.id');

$paginator = new PaginatorModel([
    'builder' => $builder,
    'limit'   => 10,
    'page'    => 1
]);


$paginate = $paginator->getPaginate();
var_dump($paginate->total_items);

/** @var \Phalcon\Mvc\Model\Query $query */
$query = $builder->getQuery();
print_r($query->getSql()['sql']); die;

Result:

Total items: 415
SQL:

SELECT
  `p`.`id` AS `_p_id`,
  `p`.`users_id` AS `_p_users_id`,
  `p`.`categories_id` AS `_p_categories_id`,
  `p`.`title` AS `_p_title`,
  `p`.`slug` AS `_p_slug`,
  `p`.`content` AS `_p_content`,
  `p`.`number_views` AS `_p_number_views`,
  `p`.`number_replies` AS `_p_number_replies`,
  `p`.`votes_up` AS `_p_votes_up`,
  `p`.`votes_down` AS `_p_votes_down`,
  `p`.`sticked` AS `_p_sticked`,
  `p`.`created_at` AS `_p_created_at`,
  `p`.`modified_at` AS `_p_modified_at`,
  `p`.`edited_at` AS `_p_edited_at`,
  `p`.`status` AS `_p_status`,
  `p`.`locked` AS `_p_locked`,
  `p`.`deleted` AS `_p_deleted`,
  `p`.`accepted_answer` AS `_p_accepted_answer`,
  COUNT(`p`.`id`) AS `c`
FROM
  `posts` AS `p`
LEFT JOIN
  `posts_replies` AS `pr`
ON
  `p`.`id` = `pr`.`posts_id`
WHERE
  `p`.`status` = :status
GROUP BY
  `p`.`id`
ORDER BY
  `p`.`created_at` DESC,
  `p`.`edited_at` DESC

$ php --ri phalcon

phalcon


Web framework delivered as a C-extension for PHP
phalcon => enabled
Author => Phalcon Team and contributors
Version => 2.0.11
Build Date => Feb 26 2016 01:40:59
Powered by Zephir => Version 0.9.2a-dev

Directive => Local Value => Master Value
phalcon.db.escape_identifiers => On => On
phalcon.db.force_casting => Off => Off
phalcon.orm.events => On => On
phalcon.orm.virtual_foreign_keys => On => On
phalcon.orm.column_renaming => On => On
phalcon.orm.not_null_validations => On => On
phalcon.orm.exception_on_failed_save => Off => Off
phalcon.orm.enable_literals => On => On
phalcon.orm.late_state_binding => Off => Off
phalcon.orm.enable_implicit_joins => On => On
phalcon.orm.cast_on_hydrate => Off => Off
phalcon.orm.ignore_unknown_columns => Off => Off

Latest commit: 932eae0

Gigabiter · 2016-03-18T08:56:33Z

I have same problem with my forum treeview schema

CREATE TABLE IF NOT EXISTS `forum` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parent` int(11) unsigned DEFAULT NULL,
  `user_id` int(11) unsigned DEFAULT NULL,
  `user_name` varchar(50) DEFAULT NULL,
  `title` varchar(500) DEFAULT NULL,
  `message` text,
  `create_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `FK_forunt_forunt` (`parent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query builder

$builder = $this->modelsManager->createBuilder()
            ->from(['t' => 'Forum'])
            ->columns(['t.*', 'COUNT(p.id) AS count'])
            ->leftJoin('Forum', 't.id = p.parent', 'p')
            ->where('t.parent = :id:', ['id' => $id])
            ->orderBy('t.update_at desc, t.create_at desc')
            ->groupBy('t.id');

Result total_items = 0

SELECT `t`.`id` AS `_t_id`, `t`.`parent` AS `_t_parent`, `t`.`user_id` AS `_t_user_id`, `t`.`user_name` AS `_t_user_name`, `t`.`title` AS `_t_title`, `t`.`message` AS `_t_message`, `t`.`create_at` AS `_t_create_at`, `t`.`update_at` AS `_t_update_at`, COUNT(`p`.`id`) AS `count` FROM `forum` AS `t` LEFT JOIN `forum` AS `p` ON `t`.`id` = `p`.`parent` WHERE `t`.`parent` = :id GROUP BY `t`.`id` ORDER BY `t`.`update_at` DESC, `t`.`create_at` DESC

version - 2.1.0 RC 1

sergeyklay · 2016-03-18T08:58:07Z

@hailie-rei @Gigabiter Could you please test 2.1.x branch?

Gigabiter · 2016-03-18T09:36:10Z

Where I can download phalcon 2.1.x windows dll for my local Openserver PHP 5.6?

Gigabiter · 2016-03-23T12:35:17Z

@sergeyklay
Update to:
Phalcon 2.1.0.RC1 - Windows x64 for PHP 5.6.0 (vc11)
March 22 2016 22:25:39 EDT / sha1: 6a6cc9a580c670979fb971f8441c6653920bc778
Has same result with errror:

$result->total_items = 0
$query->getSql()['sql'] = 'SELECT `t`.`id` AS `_t_id`, `t`.`parent` AS `_t_parent`, `t`.`user_id` AS 
`_t_user_id`, `t`.`user_name` AS `_t_user_name`, `t`.`title` AS `_t_title`, `t`.`message` AS 
`_t_message`, `t`.`create_at` AS `_t_create_at`, `t`.`update_at` AS `_t_update_at`, COUNT(`p`.`id`) 
AS `count` FROM `forum` AS `t`  LEFT JOIN `forum` AS `p` ON `t`.`id` = `p`.`parent` WHERE 
`t`.`parent` = :id GROUP BY `t`.`id` ORDER BY `t`.`update_at` DESC, `t`.`create_at` DESC'

2.1.0 RC1 did not include your fixes?

sergeyklay · 2016-03-23T20:15:27Z

Windows DLLs are here https://phalconphp.com/en/download/windows

Gigabiter · 2016-03-24T08:52:45Z

@sergeyklay Code well-earned! Дякую! )

hailie-rei changed the title ~~QueryBuilderPaginator + GroupBy returns total items count = 1~~ 2.0.10: Fatal error with Query Builder + Count + Group By + Paginator Feb 10, 2016

sergeyklay mentioned this issue Feb 23, 2016

Fix issue #11395 #11390 #11406

Merged

sergeyklay added a commit that referenced this issue Feb 24, 2016

Merge pull request #11406 from DestinyMKas/2.0.x

3c144c2

Fix issue #11395 #11390

sergeyklay added the Bug - Low label Feb 25, 2016

sergeyklay removed the Bug - Low label Feb 26, 2016

sergeyklay closed this as completed Mar 24, 2016

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

2.0.10: Fatal error with Query Builder + Count + Group By + Paginator #11395

2.0.10: Fatal error with Query Builder + Count + Group By + Paginator #11395

hailie-rei commented Feb 10, 2016

hailie-rei commented Feb 10, 2016

tobasar commented Feb 11, 2016

sergeyklay commented Feb 24, 2016

hailie-rei commented Feb 24, 2016

sergeyklay commented Feb 26, 2016

Gigabiter commented Mar 18, 2016

sergeyklay commented Mar 18, 2016

Gigabiter commented Mar 18, 2016

Gigabiter commented Mar 23, 2016

sergeyklay commented Mar 23, 2016

Gigabiter commented Mar 24, 2016

2.0.10: Fatal error with Query Builder + Count + Group By + Paginator #11395

2.0.10: Fatal error with Query Builder + Count + Group By + Paginator #11395

Comments

hailie-rei commented Feb 10, 2016

hailie-rei commented Feb 10, 2016

tobasar commented Feb 11, 2016

sergeyklay commented Feb 24, 2016

hailie-rei commented Feb 24, 2016

sergeyklay commented Feb 26, 2016

Gigabiter commented Mar 18, 2016

sergeyklay commented Mar 18, 2016

Gigabiter commented Mar 18, 2016

Gigabiter commented Mar 23, 2016

sergeyklay commented Mar 23, 2016

Gigabiter commented Mar 24, 2016