zend-framework2 - 在Zend Framework 2中如何在连接中使用多个on子句

Question

我正在像这个sql到zend framework sql模式做。

SELECT 
    jobs . *,
    c.id AS cid,
    c.name AS name,
    c.companyImage AS companyImage,
    c.logo AS logo,
    count(app.userId) AS t_app,
    app.applyStatus AS applyStatus,
    app.userId AS appUserId
FROM
    jobs
        LEFT JOIN
    companies AS c ON jobs.companyName = c.id
        LEFT JOIN
    applicants AS app ON jobs.id = app.jobId AND app.applyStatus = 1
WHERE
    jobs.ownerId = 16 AND jobs.draftId != 0 
GROUP BY jobs.id
ORDER BY jobs.id DESC
LIMIT 3 

对于此sql，我已经为zend framework 2编写了此代码

$adapter = $this->tableGateway->getAdapter();
        $sql     = new Sql($adapter);

        $select = $sql->select();
        $select->from('jobs')
                ->join(array('c' => 'companies'), 'jobs.companyName = c.id', array('cid' => 'id', 'name', 'companyImage', 'logo'), 'left')
                ->join(array('app' => 'applicants'), ' jobs.id = app.jobId AND app.applyStatus = 1', array('t_app'     => new Expression('count(app.userId)'), 'applyStatus', 'appUserId' => 'userId'), 'left')
                ->where("jobs.ownerId ={$userId} AND jobs.draftId != 0")
                ->group('jobs.id')
                ->order('jobs.id DESC')
                ->limit(3);

        $statement = $sql->getSqlStringForSqlObject($select);
        $results   = $adapter->query($statement, $adapter::QUERY_MODE_EXECUTE);

但无法正常工作，它会发出如下消息。

SQLSTATE[42S22]: Column not found: 1054 Unknown column '1' in 'on clause'

Answer 1

问题是这部分:

app.applyStatus = 1

该框架转义为1，就好像它是列名 1一样。

您也需要将此部分包含在表达式中

new Expression('jobs.id = app.jobId AND app.applyStatus = 1')

我认为join方法的'ON'参数中使用Expressions可能取决于您使用的ZF2版本，我认为它已添加2.1+