Pagination doesn't work with the HAVING clause in Zend Framework 2

Question

I've implemented a pagination similar to how it's shown here:

Search\Model\CourseTable

class CourseTable {

    const RELEVANCE_TITLE = 5;
    const RELEVANCE_DESCRIPTION = 2;
    const RELEVANCE_MIN = 3;

    protected $tableGateway;

    public function __construct(TableGateway $tableGateway) {
        $this->tableGateway = $tableGateway;
    }

    public function findAllByCriteria(CourseSearchInput $input) {
        $concatDelimiter = self::CONCAT_DELIMITER;
        $select = new Select();
        $where = $this->buildWhereFromCriteria($input);
        $having = new Having();
        $select->columns(array(
            ...
        ));
        $select->from($this->tableGateway->getTable());
        $select
            ->join('...)) // and some more JOINs
        ;
        $where
            // condition
        ;
        $having
            ->greaterThanOrEqualTo('relevance', self::RELEVANCE_MIN);
        ;
        $select->where($where, Predicate::OP_AND);
        $select->having($having);
        $select->group(array('courses.id'));

        $adapter = new \Zend\Paginator\Adapter\DbSelect($select, $this->tableGateway->getAdapter());
        $paginator = new \Zend\Paginator\Paginator($adapter);
        return $paginator;

//      $resultSet = $this->tableGateway->selectWith($select);
//      return $resultSet;
    }

    public function buildRelevanceExpressionFromCriteria(CourseSearchInput $input) {
        $criteria = $input->getArrayCopy();
        $relevanceTitle = self::RELEVANCE_TITLE;
        $relevanceDescription = self::RELEVANCE_DESCRIPTION;
        $expressionSQL = <<<SQL
MATCH (coursedata.title) AGAINST ('{$criteria['keyword']}') * $relevanceTitle +
MATCH (coursedata.description) AGAINST ('{$criteria['keyword']}') * $relevanceDescription
SQL;
        return new Expression($expressionSQL);
    }

    function buildWhereFromCriteria(CourseSearchInput $input) {
        ...
    }

}

Search\Controller\SearchController

class SearchController extends AbstractActionController {

    protected $courseTable;

    public function searchAction() {
        return $this->redirect()->toRoute('search-courses');
    }

    public function searchCoursesAction() {
        $form = $this->getServiceLocator()->get('Search\Form\CourseSearchForm');
        $request = $this->getRequest();
        if ($request->isPost()) {
            $courseSearchInput = new CourseSearchInput();
            $form->setInputFilter($courseSearchInput->getInputFilter());
            $form->setData($request->getPost());
            if ($form->isValid()) {
                $courseSearchInput->exchangeArray($form->getData());
                // $courses = $this->getCourseTable()->findAllByCriteria($courseSearchInput);
                $courses = $this->getCourseTable()->findAllByCriteria($courseSearchInput);
                $test = $courses->count();
            } else {
                $courses = null;
            }
        } else {
            $courses = null;
        }
        return new ViewModel(array(
            'form' => $form,
            'courses' => $courses,
            'cities' => $this->getServiceLocator()->get('Cache\Model\CityStorage')->getCities(),
        ));
    }

    function getCourseTable() {
        ...
    }

}

Now an Exception is thrown:

File:

    /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Db/Adapter/Driver/Pdo/Statement.php:245

Message:

    Statement could not be executed

Stapelüberwachung:

    #0 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Paginator/Adapter/DbSelect.php(122): Zend\Db\Adapter\Driver\Pdo\Statement->execute()
    #1 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Paginator/Paginator.php(881): Zend\Paginator\Adapter\DbSelect->count()
    #2 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Paginator/Paginator.php(343): Zend\Paginator\Paginator->_calculatePageCount()
    #3 /var/www/path/to/project/module/Search/src/Search/Controller/SearchController.php(38): Zend\Paginator\Paginator->count()
    #4 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Mvc/Controller/AbstractActionController.php(83): Search\Controller\SearchController->searchCoursesAction()
    #5 [internal function]: Zend\Mvc\Controller\AbstractActionController->onDispatch(Object(Zend\Mvc\MvcEvent))
    #6 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(460): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
    #7 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(204): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
    #8 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Mvc/Controller/AbstractController.php(117): Zend\EventManager\EventManager->trigger('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
    #9 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Mvc/DispatchListener.php(114): Zend\Mvc\Controller\AbstractController->dispatch(Object(Zend\Http\PhpEnvironment\Request), Object(Zend\Http\PhpEnvironment\Response))
    #10 [internal function]: Zend\Mvc\DispatchListener->onDispatch(Object(Zend\Mvc\MvcEvent))
    #11 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(460): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
    #12 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(204): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
    #13 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Mvc/Application.php(294): Zend\EventManager\EventManager->trigger('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
    #14 /var/www/path/to/project/public/index.php(12): Zend\Mvc\Application->run()
    #15 {main}

And the previous one is:

Previous Exception:

    PDOException

    File:

        /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Db/Adapter/Driver/Pdo/Statement.php:240

    Message:

        SQLSTATE[42S22]: Column not found: 1054 Unknown column 'relevance' in 'having clause'

    Stapelüberwachung:

        #0 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Db/Adapter/Driver/Pdo/Statement.php(240): PDOStatement->execute()
        #1 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Paginator/Adapter/DbSelect.php(122): Zend\Db\Adapter\Driver\Pdo\Statement->execute()
        #2 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Paginator/Paginator.php(881): Zend\Paginator\Adapter\DbSelect->count()
        #3 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Paginator/Paginator.php(343): Zend\Paginator\Paginator->_calculatePageCount()
        #4 /var/www/path/to/project/module/Search/src/Search/Controller/SearchController.php(38): Zend\Paginator\Paginator->count()
        #5 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Mvc/Controller/AbstractActionController.php(83): Search\Controller\SearchController->searchCoursesAction()
        #6 [internal function]: Zend\Mvc\Controller\AbstractActionController->onDispatch(Object(Zend\Mvc\MvcEvent))
        #7 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(460): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
        #8 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(204): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
        #9 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Mvc/Controller/AbstractController.php(117): Zend\EventManager\EventManager->trigger('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
        #10 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Mvc/DispatchListener.php(114): Zend\Mvc\Controller\AbstractController->dispatch(Object(Zend\Http\PhpEnvironment\Request), Object(Zend\Http\PhpEnvironment\Response))
        #11 [internal function]: Zend\Mvc\DispatchListener->onDispatch(Object(Zend\Mvc\MvcEvent))
        #12 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(460): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
        #13 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(204): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
        #14 /var/www/path/to/project/vendor/zendframework/zendframework/library/Zend/Mvc/Application.php(294): Zend\EventManager\EventManager->trigger('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
        #15 /var/www/path/to/project/public/index.php(12): Zend\Mvc\Application->run()
        #16 {main}

Is it a framework bug?

EDIT

The generated query I get with $test = $select->getSqlString($this->tableGateway->getAdapter()->getPlatform()); in the line before $adapter = new \Zend\Paginator\Adapter\DbSelect($select, $this->tableGateway->getAdapter());:

SELECT `courses`.`id` AS `id`,
       `courses`.`title` AS `title`,
       `courses`.`description` AS `description`,
       DATE_FORMAT(courses.startdate, "%e\.%m\.%Y") AS `startDate`,
       DATE_FORMAT(courses.enddate, "%e\.%m\.%Y") AS `endDate`,
       DATE_FORMAT(courses.starttime, "%H\:%i") AS `startTime`,
       DATE_FORMAT(courses.endtime, "%H\:%i") AS `endTime`,
       `allproviders`.`displayedname` AS `providerName`,
       `allproviders`.`providertype` AS `providerType`,
       `cities`.`name` AS `city`,
       `weekdays`.`labelde` AS `weekday`,
       `levelsmin`.`usrlevel` AS `usrLevelMin`,
       `levelsmin`.`unilevel` AS `uniLevelMin`,
       `levelsmax`.`usrlevel` AS `usrLevelMax`,
       `levelsmax`.`unilevel` AS `uniLevelMax`,
       MATCH (coursedata.title) AGAINST ('Salsa') * 5 + MATCH (coursedata.description) AGAINST ('Salsa') * 2 AS `relevance`,
       GROUP_CONCAT(trainers.name SEPARATOR '|||') AS `trainers`
FROM `courses`
INNER JOIN `allproviders` ON `courses`.`provider_id` = `allproviders`.`providerid`
INNER JOIN `cities` ON `allproviders`.`city_id` = `cities`.`id`
INNER JOIN `weekdays` ON `courses`.`weekday_id` = `weekdays`.`id`
LEFT JOIN `levels` AS `levelsmin` ON `courses`.`levelmin_id` = `levelsmin`.`id`
LEFT JOIN `levels` AS `levelsmax` ON `courses`.`levelmax_id` = `levelsmax`.`id`
INNER JOIN `coursedata` ON `courses`.`id` = `coursedata`.`id`
INNER JOIN `courses_trainers` ON `courses`.`id` = `courses_trainers`.`course_id`
INNER JOIN `trainers` ON `trainer_id` = `trainers`.`id`
WHERE `cities`.`id` = '23'

  AND `weekdays`.`id` IN ('1',
                          '2',
                          '3',
                          '4',
                          '5',
                          '6',
                          '7')
  AND `courses`.`enddate` > NOW()
GROUP BY `courses`.`id` HAVING `relevance` >= '3'

I deleted my answer, because I'm unsure as to what your query comes down to exactly. I suspect it has something to do with the query itself and it's not an actual bug. Can you show what columns you are selecting (you left that out)? And also what query you are trying to do, written out in normal SQL would be helpful. — Ruben, Apr 15 '13 at 19:56
It's defently not the query, because: 1. It works without pagination; 2. when I extract the query and execute it over a MySQL client, it also works. The query is too long for a comment, I updated the question. — automatix, Apr 15 '13 at 20:04
I still think it is, because it looks like you group by courses.id (seems weird to me already but ok) and in your having you're using 'relevance', which isn't a group column. Depending on the MySQL mode (i.e. if ONLY_FULL_GROUP_BY is NOT set) then MySQL may allow this in your session, but it doesn't make it any less wrong. What you could do is test if the query still works in your client when you set ONLY_FULL_GROUP_BY mode: SET sql_mode='ONLY_FULL_GROUP_BY'; — Ruben, Apr 15 '13 at 20:11
Yes, I see now. When I disable the [MySQL Extensions to GROUP BY](http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html), the query actually returns an error (but another one than in the Exception): `Error Code: 1055 'unisportr_zf.courses.title' isn't in GROUP BY`. I'll try to rewrite the code, in order ro reduce the `GROUP BY` using to standard SQL. — automatix, Apr 15 '13 at 20:29
Yes, it's pretty simple: you just have to make sure that all the columns you are using in your HAVING clause are also listed in your GROUP BY clause. This makes perfect sense, because your HAVING contains conditions about each group and not about each row. — Ruben, Apr 16 '13 at 02:46
In this case I cannot have alls columns from `HAVING` in `GROUP BY`, since the column I need in `HAVING` is an aggregated one. When I try `SELECT ... FROM ... JOIN .. JOIN ... GROUP BY courses.id, courses.id, courses.title, courses.description, coursedata.title, coursedata.description, relevance HAVING relevance >= '3'` (in `ONLY_FULL_GROUP_BY` `sql_mode`), I get an error: `Error Code: 1463 Non-grouping field 'relevance' is used in HAVING clause`. — automatix, Apr 16 '13 at 08:41
Where does relevance come from? You said it's an aggregated field? — Ruben, Apr 16 '13 at 18:39

Pagination doesn't work with the HAVING clause in Zend Framework 2

0 Answers0