QueryBuilder + GROUP BY + Pagination = wrong number of results

[slothentic]

I'm having an issue when doing pagination with a group by and query builder. If I do the following:

$builder = new \Phalcon\Mvc\Model\Query\Builder();
$builder->from(array('BigTable' => 'Model\BigTable'));
$builder->groupBy(array('BigTable.something')); 

$paginator = new \Phalcon\Paginator\Adapter\QueryBuilder(array(
    "builder" => $builder,
    "limit"=> 25,
    "page" => (int) $this->request->getQuery('page'),
)); 

It says 1073 results. In reality there are 17K+ results, causing the wrong number of pages. This is using 1.2.6.

If I do:

$builder->getQuery()->execute()

It has the correct number of rows.

[dreamsxin]

Can you print the $paginator result?

[slothentic]

$paginator

Phalcon\Paginator\Adapter\QueryBuilder Object
(
    [_config:protected] => Array
        (
            [builder] => Phalcon\Mvc\Model\Query\Builder Object
                (
                    [_dependencyInjector:protected] => 
                    [_columns:protected] => Array
                        (
                            [0] => id
                            [1] => name
                            [2] => created_at
                        )

                    [_models:protected] => Array
                        (
                            [BigTable] => Model\BigTable
                        )

                    [_joins:protected] => 
                    [_conditions:protected] => 
                    [_group:protected] => Array
                        (
                            [0] => BigTable.something
                        )

                    [_having:protected] => 
                    [_order:protected] => 
                    [_limit:protected] => 
                    [_offset:protected] => 
                    [_forUpdate:protected] => 
                    [_sharedLock:protected] => 
                    [_bindParams:protected] => 
                    [_bindTypes:protected] => 
                    [_hiddenParamNumber:protected] => 0
                )

            [limit] => 25
            [page] => 0
        )

    [_builder:protected] => Phalcon\Mvc\Model\Query\Builder Object
        (
            [_dependencyInjector:protected] => 
            [_columns:protected] => Array
                (
                    [0] => id
                    [1] => name
                    [2] => created_at
                )

            [_models:protected] => Array
                (
                    [BigTable] => Model\BigTable
                )

            [_joins:protected] => 
            [_conditions:protected] => 
            [_group:protected] => Array
                (
                    [0] => BigTable.something
                )

            [_having:protected] => 
            [_order:protected] => 
            [_limit:protected] => 
            [_offset:protected] => 
            [_forUpdate:protected] => 
            [_sharedLock:protected] => 
            [_bindParams:protected] => 
            [_bindTypes:protected] => 
            [_hiddenParamNumber:protected] => 0
        )

    [_limitRows:protected] => 25
    [_page:protected] => 0
)

After ->getPaginate();

stdClass Object
(
    [first] => 1
    [before] => 1
    [items] => Phalcon\Mvc\Model\Resultset\Simple Object
        (
            [_type:protected] => 0
            [_result:protected] => Phalcon\Db\Result\Pdo Object
                (
                    [_connection:protected] => Phalcon\Db\Adapter\Pdo\Mysql Object
                        (
                            [_eventsManager:protected] => 
                            [_descriptor:protected] => Array
                                (
                                    [host] => localhost
                                    [username] => user
                                    [password] => 
                                    [dbname] => something
                                    [options] => Array
                                        (
                                            [1002] => SET NAMES utf8
                                        )

                                )

                            [_dialect:protected] => Phalcon\Db\Dialect\Mysql Object
                                (
                                    [_escapeChar:protected] => `
                                )

                            [_connectionId:protected] => 1
                            [_sqlStatement:protected] => 
                            [_sqlVariables:protected] => 
                            [_sqlBindTypes:protected] => 
                            [_transactionsWithSavepoints:protected] => 0
                            [_pdo:protected] => PDO Object
                                (
                                )

                            [_affectedRows:protected] => 
                            [_transactionLevel:protected] => 0
                            [_type:protected] => mysql
                            [_dialectType:protected] => mysql
                        )

                    [_result:protected] => 
                    [_fetchMode:protected] => 2
                    [_pdoStatement:protected] => PDOStatement Object
                        (
                            [queryString] => SELECT `BigTable`.`id` AS `id`, `BigTable`.`name` AS `name`, `BigTable`.`created_at` AS `created_at` FROM `api_BigTables` AS `BigTable` GROUP BY `BigTable`.`something` LIMIT 25
                        )

                    [_sqlStatement:protected] => SELECT `BigTable`.`id` AS `id`, `BigTable`.`name` AS `name`, `BigTable`.`created_at` AS `created_at` FROM `api_BigTables` AS `BigTable` GROUP BY `BigTable`.`something` LIMIT 25
                    [_bindParams:protected] => 
                    [_bindTypes:protected] => 
                    [_rowCount:protected] => 25
                )

            [_cache:protected] => 
            [_isFresh:protected] => 1
            [_pointer:protected] => -1
            [_count:protected] => 25
            [_activeRow:protected] => 
            [_rows:protected] => 
            [_errorMessages:protected] => 
            [_hydrateMode:protected] => 0
            [_model:protected] => Phalcon\Mvc\Model\Row Object
                (
                )

            [_columnMap:protected] => Array
                (
                    [id] => id
                    [name] => name
                    [created_at] => created_at
                )

            [_keepSnapshots:protected] => 
        )

    [next] => 2
    [last] => 43
    [current] => 1
    [total_pages] => 43
    [total_items] => 1063
)

[xavier-rodet]

Indeed, I do have the same problem :(

[avra911]

Also have the same problem and I also noticed that is happening because of joins too, not only for group by

[dreamsxin]

Can you give a list structure, I simulate some data under test.

[avra911]

CREATE DATABASE  IF NOT EXISTS `craigslist` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `craigslist`;
-- MySQL dump 10.13  Distrib 5.5.35, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: craigslist
-- ------------------------------------------------------
-- Server version   5.5.35-0+wheezy1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `categories`
--

DROP TABLE IF EXISTS `categories`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `data`
--

DROP TABLE IF EXISTS `data`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `data` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `href` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `longitude` varchar(255) DEFAULT NULL,
  `latitude` varchar(255) DEFAULT NULL,
  `price` decimal(11,2) DEFAULT NULL,
  `area` varchar(255) DEFAULT NULL,
  `url_id` int(11) DEFAULT NULL,
  `created_at` date DEFAULT NULL,
  `good` smallint(2) DEFAULT NULL,
  `called` smallint(2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `URL_ID` (`url_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4390612285 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `sub_domains`
--

DROP TABLE IF EXISTS `sub_domains`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sub_domains` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=417 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `url`
--

DROP TABLE IF EXISTS `url`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `url` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sub_domain_id` int(11) NOT NULL,
  `items_count` int(11) DEFAULT '0',
  `last_item` int(20) DEFAULT NULL,
  `params` text,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `SUB_DOMAIN` (`sub_domain_id`),
  KEY `CATEGORY_ID` (`category_id`),
  CONSTRAINT `FK_URL_CATEGORY_ID` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_URL_SUB_DOMAIN_ID` FOREIGN KEY (`sub_domain_id`) REFERENCES `sub_domains` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=831 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2014-03-25  9:22:39

$currentPage = $this->request->getQuery('page', 'int');
$builder = $this->modelsManager->createBuilder();
$builder
    ->columns(array(
        'Url.*',
        'COUNT(gData.id) as data_count_total',
        'SUM(if(gData.good = 1, 1, 0)) AS data_count_good',
        'ROUND((SUM(if(gData.good = 1, 1, 0)) / COUNT(gData.id)) * 100, 2) AS data_good_percentage',
        'Categories.name as category_name',
        'Categories.id as category_id'
    ))
    ->from('Categories')
    ->groupBy('Categories.id, Url.params')
    ->join('Url', 'Url.category_id = Categories.id')
    ->leftJoin('Data', 'Url.id = gData.url_id', 'gData')
;

$paginator = new Phalcon\Paginator\Adapter\QueryBuilder(
    array(
        "builder" => $builder,
        "limit" => 100,
        "page" => $currentPage
    )
);

$page = $paginator->getPaginate();

[avra911]

i saw the breaking pagination on joins on magento where the solution was to reset the select as follow:

public function getSelectCountSql()
{   
    $this->_renderFilters();
    $countSelect = clone $this->getSelect();
    $countSelect->reset(Zend_Db_Select::ORDER);
    $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
    $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
    $countSelect->reset(Zend_Db_Select::COLUMNS);

    // Count doesn't work with group by columns keep the group by 
    if(count($this->getSelect()->getPart(Zend_Db_Select::GROUP)) > 0) {
        $countSelect->reset(Zend_Db_Select::GROUP);
        $countSelect->distinct(true);
        $group = $this->getSelect()->getPart(Zend_Db_Select::GROUP);
        $countSelect->columns("COUNT(DISTINCT ".implode(", ", $group).")");
    } else {
        $countSelect->columns('COUNT(*)');
    }
    return $countSelect;
}

[avra911]

a quick and dirty solution i think would be:

$dataCount = $builder->getQuery()->execute()->count();
$page->next = $page->current + 1;
$page->before = $page->current - 1 > 0 ? $page->current - 1 : 1;
$page->total_items = $dataCount;
$page->total_pages = ceil($dataCount / 100);
$page->last = $page->total_pages;

[hugoduraes]

Having the same issue... @phalcon when there will be a fix for this?

[avra911]

I am afraid is not an easy fix. I have reasons to believe is zend common issue.

You may avoid by creating another query collection clone count.

I am also intersting in an out of the box solution but i also think is not very easy.

Regards,
R

[hugoduraes]

I believe I've previously experienced a similar issue with Zend. The workaround was similar to the workaround I got to with Phalcon.

But, I would like to hear an official word from @phalcon team about this issue.

[kbtz]

I've wrapped the @avra911 solution in a child class of Phalcon\Paginator\Adapter\QueryBuilder.

Also I'm not sure if it will work in a database other than MySQL since the syntax may differ, but I've checked that PHQL parses COUNT(DISTINCT [...]) correctly.

[Skymidt]

This solution does not fix entire bug.
Phalcon\Paginator\Adapter\QueryBuilder still get this bug with group by and join in 1.3.2 version.
If we use Phalcon\Paginator\Adapter\Model as adapter this works, but is useless because bring all data to paginator, using a lot of memory in big queries.

[phalcon]

Actually the behaviour is:

• A query builder is passed
• We modify the query builder passing a offset/limit
• We clone the original query builder and change the selected columns to a count

You can see the code here, if you find a solution please submit a pull request to 2.0.0
https://github.com/phalcon/cphalcon/blob/2.0.0/phalcon/paginator/adapter/querybuilder.zep

[Skymidt]

This function:

public static function buscarNoticia($searchParams) {
        $builder = \Phalcon\DI::getDefault()->getModelsManager()->createBuilder()->from('Application\Models\Noticia');
        $builder->leftJoin('Application\Models\NoticiaVersao', 'Application\Models\Noticia.id = Application\Models\NoticiaVersao.noticia_id');
        if (is_array($searchParams)) {
            //verifica editoria
            if ($searchParams['editoria_id']) {
                $editoria_id = $searchParams['editoria_id'];
                $builder->andWhere('Application\Models\Noticia.editoria_id = '.$searchParams['editoria_id']);
            }

            if ($searchParams['search']) {
                $builder->leftJoin('Application\Models\AssuntosNoticia', 'Application\Models\Noticia.id = Application\Models\AssuntosNoticia.noticia_id');
                $builder->leftJoin('Application\Models\AssuntosEditoria', 'Application\Models\AssuntosEditoria.id = Application\Models\AssuntosNoticia.assunto_id');
                $builder->leftJoin('Application\Models\FonteNoticia', 'Application\Models\Noticia.id = Application\Models\FonteNoticia.noticia_id');
                $builder->leftJoin('Application\Models\Fonte', 'Application\Models\Fonte.id = Application\Models\FonteNoticia.fonte_id');
                $builder->andWhere('Application\Models\Noticia.titulo like :search: or Application\Models\AssuntosEditoria.titulo = :assunto: or Application\Models\Fonte.nome like :search:',array('search' => "%".$searchParams['search']."%",'assunto' => $searchParams['search']));
                $builder->groupBy('Application\Models\Noticia.id');
            }
        }
        $builder->orderBy('Application\Models\NoticiaVersao.data_inicio DESC');

        return $builder;
    }

Are used in this code:

use Phalcon\Paginator\Adapter\QueryBuilder as Paginator;
$paginator = new Paginator(array(
            'builder' => Noticia::buscarNoticia($searchParams),
            'limit' => $pageLimit,
            'page' => $currentPage
            ));

The limit still doesn't work on my paginator. Is this wrong?

[erickskrauch]

I do have the same problem :(((

[dreamsxin]

@erickskrauch what's version with you?

[erickskrauch]

1.3.2 TS x86

[kaakopek]

how the source code to set autoincrement in the framework Phalcon .. ??

[alkana]

Hello,

I have checked in all phalcon 2.0.x tags and in the master and commits of usernaem are not present.

So, using GROUP BY with QueryBuilder paginator adapter doesn't work at all.

[hailie-rei]

I have the same problem. I use query builder with group by and paginator. Total items count is equal to 1. When will it be fixed?

[sergeyklay]

@hailie-rei Create please separated issue by providing Phalcon version and small script to reproduce

[hailie-rei]

@sergeyklay Okay, I'll do that :)

[hisune]

i have the same problem. how to fix it? https://forum.phalconphp.com/discussion/18799/dateformat-group-bug-with-createbuilder-and-paginator

[rodribeze]

Criei um novo extendendo a classe original:

 namespace Library;

 class PaginatorBuilder extends \Phalcon\Paginator\Adapter\QueryBuilder{

   private $builder;

   public function __construct($options)
   {  
      $this->builder = $options['builder'];
      return parent::__construct($options);
   }

   public function getPaginate(){
      
      $page = parent::getPaginate();

      $dataCount = $this->builder->getQuery()->execute()->count();
      $page->next = $page->current + 1;
      $page->before = $page->current - 1 > 0 ? $page->current - 1 : 1;
      $page->total_items = $dataCount;
      $page->total_pages = ceil($dataCount / $page->limit);
      $page->last = $page->total_pages;

      return $page;

   }

 }