Zend Framework 2中的JOIN子句的ON条件中IS，NULL，NOT 、!和其他保留字符串的引用问题

问题描述

我有一条SQL语句，它通过关联表(courses_trainers)分离运动类/课程(courses)和其教练(trainers).由于某些课程有多个培训师，因此我使用GROUP_CONCAT(...)函数将培训师姓名集中到一个字段中.一些培训师行为空或NULL，因此我在培训师JOIN的ON子句中添加了trainers.name IS NOT NULL和trainers.name != ""条件:

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语句

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变体

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

我得到的生成的JOIN代码如下:

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.

如何解释" ZF，以不引用IS，NOT，"等?

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

推荐答案

join方法接受一个表达式作为ON子句的第二个参数

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 != ""'),

这篇关于Zend Framework 2中的JOIN子句的ON条件中IS，NULL，NOT 、!和其他保留字符串的引用问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！