Issue with quoting of IS, NULL, NOT, !, and other

问题:

I have an SQL statement, that selets sport classes/courses (courses) with their trainers (trainers) over an association table (courses_trainers). Since some courses have multiple trainers, I use the GROUP_CONCAT(...) function to get the trainer names into one field. Some trainers rows are empty or NULL, so I add a trainers.name IS NOT NULL and a trainers.name != "" condition to the ON clause of the trainers JOIN:

SQL statement

SELECT
    courses.id AS id,
    GROUP_CONCAT(DISTINCT trainers.name SEPARATOR "|||") AS trainers
    ...
FROM
    courses
    ...
LEFT JOIN
    courses_trainers ON courses.id = courses_trainers.course_id
LEFT JOIN
    trainers ON trainer_id = trainers.id
    AND trainers.name IS NOT NULL
    AND trainers.name != ""
    ...
...
WHERE `courses`.`id` = '898'
GROUP BY
    courses.id
;

OO variant in the CourseTable class

public function findOnceByID($id) { 
    $concatDelimiter = self::CONCAT_DELIMITER;
    $select = new Select();
    ...
    $select->columns(array(
        'id', ...
    ));
    $select->from($this->tableGateway->getTable());
    $select
        ...
        ->join('courses_trainers', 'courses.id = courses_trainers.course_id', array(), Select::JOIN_LEFT)
        ->join('trainers', 'trainer_id = trainers.id AND trainers.name IS NOT NULL AND trainers.name != ""', array(
            'trainers' => new Expression('GROUP_CONCAT(DISTINCT trainers.name SEPARATOR "' . $concatDelimiter . '")')
            ), Select::JOIN_LEFT)
        ...
    ;
    $where
        ->equalTo('courses.id', $id)
    ;
    $select->where($where, Predicate::OP_AND);
    $select->group('courses.id');
    $resultSet = $this->tableGateway->selectWith($select);
    return $resultSet;
}

The generated JOIN code I get looks like this:

LEFT JOIN
    `courses_trainers` ON `courses`.`id` = `courses_trainers`.`course_id`
LEFT JOIN
    `trainers` ON `trainer_id` = `trainers`.`id`
    AND `trainers`.`name` `IS` `NOT` `NULL`
    AND `trainers`.`name` `!`= `"``"`

So, here is to much quoted.

How to "explain" to the ZF, that IS, NOT, " etc. should not be quoted?

回答1:

The join method accepts an expression as its second parameter for the ON clause

->join('trainers', new Expression('trainer_id = trainers.id AND trainers.name IS NOT NULL AND trainers.name != ""'),

回答2:

Responsible for quoting is Zend\Db\Adapter\Platform\PlatformInterface#quoteIdentifierInFragment(...) (or more precise its implementations, in this case in Zend\Db\Adapter\Platform\Mysql), that gets as second argument an array of "safe words". Zend\Db\Sql\Select#processJoins(...) passes to it the array('=', 'AND', 'OR', '(', ')', 'BETWEEN', '<', '>'). As IS, NOT, ! etc. are not in the list, they are quoted.

The solution is to extend the Zend\Db\Sql\Select and overwrire its processJoins(...), adding the additional "safe words" needed to the list of the second argument in the quoteIdentifierInFragment(...) call:

<?php
namespace MyNamespace\Db\Sql;

use Zend\Db\Adapter\Driver\DriverInterface;
use Zend\Db\Adapter\StatementContainerInterface;
use Zend\Db\Adapter\ParameterContainer;
use Zend\Db\Adapter\Platform\PlatformInterface;
use Zend\Db\Sql\Select as ZendSelect;

class Select extends ZendSelect
{

    ...

    protected function processJoins(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
    {
        ...
        // process joins
        $joinSpecArgArray = array();
        foreach ($this->joins as $j => $join) {
            ...
            $joinSpecArgArray[$j][] = ($join['on'] instanceof ExpressionInterface)
                ? $this->processExpression($join['on'], $platform, $driver, $this->processInfo['paramPrefix'] . 'join' . ($j+1) . 'part')
                : $platform->quoteIdentifierInFragment($join['on'], array('=', 'AND', 'OR', '(', ')', 'BETWEEN', '<', '>', '!', 'IS', 'NULL', 'NOT', '"')); // on
            ...
        }

        return array($joinSpecArgArray);
    }

    ...

}