How to add IN statement with JOIN ON clause in tablegateway ZF2

database, in-clause, join, zend-framework2

Solution

I think you can pass an expression instead of the join string:

// $this->table = 'category';
$sql = $this->getSql();
$select = $sql->select();

$join = new Expression('user_category_subscriptions.category_id = category.id AND user_category_subscriptions.status IN (1,2,3,4)');

$select->join('user_category_subscriptions', $join, array(), 'left');
$select->where(array('category.user_id = 2 OR (user_category_subscriptions.status IN (2,4))'));

Problem

Getting all result so far until I added a 'IN' statement with join ON clause My code is something like this: ``` // $this->table = 'category'; $sql = $this->getSql(); $select = $sql->select(); $select->join('user_category_subscriptions', 'user_category_subscriptions.category_id = category.id AND user_category_subscriptions.status IN (1,2,3,4)', array(), 'left'); $select->where(array('category.user_id = 2 OR (user_category_subscriptions.status IN (2,4))')); ``` Error ``` ZEND\DB\ADAPTER\EXCEPTION\INVALIDQUERYEXCEPTION File:E:\htdocs\myproj\vendor\zendframework\zendframework\library\Zend\Db\Adapter\Driver\Pdo\Statement.php:245 Message:Statement could not be executed ``` Used 'echo $select->getSqlString();' to print the query: ``` SELECT "categories".* FROM "categories" LEFT JOIN "user_category_subscriptions" ON "user_category_subscriptions"."category_id" = "categories"."category_id" AND "user_category_subscriptions"."status" IN ("1""," "2""," "3""," "4") WHERE category.user_id = 2 OR (user_category_subscriptions.status IN (2,4)) ``` So the problem is zend's auto converting (1,2,3,4) into ("1""," "2""," "3""," "4") Any idea to solve this? Thanks

Original source