How to set sql_mode in Zend Framework 2?

Question

I'm currently having an issue with pagination in Zend Framework 2.

This code

public function findAllByCriteria(CourseSearchInput $input) {
    $concatDelimiter = self::CONCAT_DELIMITER;
    $select = new Select();
    $where = new Where();
    $having = new Having();
    $select->columns(array(
        'id', 'title', 'description'
    ));
    $select->from($this->tableGateway->getTable());
    $select
        ->join('coursedata', 'courses.id = coursedata.id', array(
            'relevance' => $this->buildRelevanceExpressionFromCriteria($input)
        ))
    ;
    $having
        ->greaterThanOrEqualTo('relevance', self::RELEVANCE_MIN);
    ;
    $select->where($where, Predicate::OP_AND);
    $select->having($having);
    $select->group(array('courses.id'));

    $dbAdapter = $this->tableGateway->getAdapter();
    // $dbAdapter->getDriver()->getConnection()->execute('SET sql_mode = "";');
    $adapter = new \Zend\Paginator\Adapter\DbSelect($select, $dbAdapter);
    $paginator = new \Zend\Paginator\Paginator($adapter);
    return $paginator;
}

create this SQL:

SELECT
    `courses`.`id` AS `id`,
    `courses`.`title` AS `title`,
    `courses`.`description` AS `description`,
    MATCH (coursedata.title) AGAINST ('Salsa') * 5 + MATCH (coursedata.description) AGAINST ('Salsa') * 2 AS `relevance`
FROM `courses`
INNER JOIN `coursedata` ON `courses`.`id` = `coursedata`.`id`
GROUP BY `courses`.`id`
HAVING `relevance` >= '3'

It ueses the MySQL Extensions to GROUP BY and cannot be executed, if the sql_mode is set to ONLY_FULL_GROUP_BY. So, I tried to reset the sql_mode before the statement is executed (see the commented out line above: $dbAdapter->getDriver()->getConnection()->execute('SET sql_mode = "";');). But it didn't worked. So, how can I set the sql_mode in order to execute my non-standard SQL?

This is only an assumption, as my sql knowledge is minimal, but maybe you could execute that command on initiation? Inside your `driver_options` add `PDO::MYSQL_ATTR_INIT_COMMAND => 'set sql_mode = ""'`. — Sam, Apr 16 '13 at 05:45
There should be (also) another way to do it, since so I can use either `SET NAMES` or `SET sql_mode`. Anyway. I've tried these both out: `'driver_options' => array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\'', 'SET SESSION sql_mode = \'\''),` and `'driver_options' => array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET SESSION sql_mode = \'\'',),`. The SQL error, I intend to eliminate with `sql_mode = ''` is still here. But perhaps the mode was not set? How can I check the current PDO settings / `sql_mode`? — automatix, Apr 16 '13 at 09:13

Andrew · Accepted Answer · 2013-04-16T09:54:04.813

This may not be the answer to the question you are asking, but I can see you are going to have an issue with your query regardless when using Paginator.

The DbSelect Adapter for the Paginator doesn't like the aggregate function in there (Group By)

The Paginator will try and use your query to build it's own query to calculate the "count" for items in the collection. This is broken due to you using an aggregate in your query, any groups etc will break the adapter.

if you check the default implementation you will see:

/**
 * Returns the total number of rows in the result set.
 *
 * @return integer
 */
public function count()
{
    if ($this->rowCount !== null) {
        return $this->rowCount;
    }

    $select = clone $this->select;
    $select->reset(Select::COLUMNS);
    $select->reset(Select::LIMIT);
    $select->reset(Select::OFFSET);

    // This won't work if you've got a Group By in your query
    $select->columns(array('c' => new Expression('COUNT(1)')));

    $statement = $this->sql->prepareStatementForSqlObject($select);
    $result    = $statement->execute();
    $row       = $result->current();

    $this->rowCount = $row['c'];

    return $this->rowCount;
}

this doesn't like when you are using Group BY and will give back incorrect results.

You can create your own adataper, and extend the existing DbSelect and override the count method when you are planning to use Group BY;

Off the top of my head something like this should work, but may not be the most efficient way of doing it

/**
 * Returns the total number of rows in the result set.
 * 
 * @return integer
 */
public function count()
{
    if ($this->rowCount !== null) {
        return $this->rowCount;
    }

    /**
     * If the query hasn't got 'GROUP BY' just try and use the old method
     */
    $stateGroup = $this->select->getRawState('group');
    if( ! isset($stateGroup) || empty($stateGroup)) {
        return parent::count();
    }

    $select = clone $this->select;
    $select->reset(Select::LIMIT);
    $select->reset(Select::OFFSET);

    $statement = $this->sql->prepareStatementForSqlObject($select);
    $result    = $statement->execute();

    $this->rowCount = $result->count();

    return $this->rowCount;
}

Thanks a million! It works (in my case)! But `GROUP BY` can sometimes affect/reduce the total number of the selected rows. That means, when we just remove it out from the statement, we risk to get a count number greater than we are actually selecting. Right? Or have I misunderstood/overlooked something? — automatix, Apr 16 '13 at 09:41
Thats why there's a check in there, if there's no GROUP in the select object it just uses the old method of counting. I am sure this isn't perfect and could do with some work but it's an example to get you started ;-) — Andrew, Apr 16 '13 at 09:53
It's even a great example! Thank you very much once again for it! :) — automatix, Apr 16 '13 at 11:17

How to set sql_mode in Zend Framework 2?

1 Answers1