mysql - 如何在 Zend Framework 2 中设置 sql_mode？

Question

我目前在 Zend Framework 2 中遇到分页问题。

这段代码

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;
}

创建此 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'

它使用 MySQL Extensions to GROUP BY如果 sql_mode 设置为 ONLY_FULL_GROUP_BY，则无法执行。因此，我尝试在执行语句之前重置 sql_mode(请参阅上面注释掉的行:$dbAdapter->getDriver()->getConnection()->execute('SET sql_mode = "";');)。但它没有用。那么，如何设置 sql_mode 以执行我的非标准 SQL？

Answer 1

这可能不是您所问问题的答案，但我可以看出，无论您何时使用分页器，您的查询都会遇到问题。

Paginator 的 DbSelect 适配器不喜欢那里的聚合函数(Group By)

Paginator 将尝试使用您的查询来构建它自己的查询，以计算集合中项目的“计数”。由于您在查询中使用了聚合，所以这已被破坏，任何组等都会破坏适配器。

如果您检查默认实现，您将看到:

/**
 * 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;
}

当您使用 Group BY 时，这并不合适，并且会返回不正确的结果。

您可以创建自己的一个dataper，并在您计划使用Group BY 时扩展现有的DbSelect 并覆盖count 方法；

在我的脑海中，这样的事情应该可行，但可能不是最有效的方法

/**
 * 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;
}