How to use multiple on clause in joining in zend framework 2

Question

I am doing like this sql into zend framework sql pattern.

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 

For this sql I already write this code for 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);

but does not work properly and its give a message like below.

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

Answer 1

The issue is this part:

app.applyStatus = 1

The framework is escaping 1 as if it were a column name, 1.

You need to enclose this part in an Expression too

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

I think the use of Expressions in the 'ON' parameter of the join method may depend on the version of ZF2 you are using, I think it was added 2.1+

Answer 2

Building on this answer. If you also want your table & column identifiers to be escaped, use this syntax:

use Zend\Db\Sql\Expression;

...

$onExpression = new Expression('? = ? AND ? = ?', 
    ['jobs.id', 'app.jobId', 'app.applyStatus', 1], 
    [Expression::TYPE_IDENTIFIER, Expression::TYPE_IDENTIFIER, 
     Expression::TYPE_IDENTIFIER, Expression::TYPE_LITERAL]
);

$select->from('jobs')
    ->join(array('app' => 'applicants'), $onExpression, array('t_app' => new Expression('count(app.userId)'), 'applyStatus', 'appUserId' => 'userId'), 'left');

The Expression constructor accepts the string, then arguments, then argument types.

public function __construct($expression = '', $parameters = null, array $types = [])