Zf2 NOT IN expression in Where clause

Question

I am trying to get a results from a table that is not already present on another table. For this I am using a subQuery and a NOT IN expression in where clause. The normal sql that works looks like:

SELECT `products`.* FROM `products` WHERE `products`.`pro_id` **NOT IN** (
SELECT `product_collection`.`pro_id` AS `pro_id` FROM `product_collection` WHERE `coll_id` = '6' ) AND products.pro_id IN (55,56,57,62)

I am using zf2 for the project. The problem is I dont know how to use NOT IN expression inside the where clause. We can use where->in() for the IN expression in Where clause. for eg.

//$productIds is an array
//$collectionId is an id

$subSelect = $this->getRawSql()->select('product_collection');
$subSelect -> columns(array('pro_id'));
$subSelect -> where(array('coll_id'=>$collectionId));

$select = $this->getRawSql()->select('products');
$select -> **where->in**('products.pro_id', $subSelect)
        -> where->in('products.pro_id',$productIds);

but I have no idea how to use NOT IN expression.

Answer 1

Use notIn() like you do with In().
See : http://framework.zend.com/apidoc/2.1/classes/Zend.Db.Sql.Predicate.NotIn.html

$subSelect = $this->getRawSql()->select('product_collection');
$subSelect -> columns(array('pro_id'));
$subSelect -> where(array('coll_id'=>$collectionId));

$select = $this->getRawSql()->select('products');
$select ->where->notIn('products.pro_id', $subSelect)    
        ->where->in('products.pro_id',$productIds);

Edit :

Or use this instead

$select ->where
        ->addPredicate(new Zend\Db\Sql\Predicate\Expression('products.pro_id NOT IN (?)',
                            array($subSelect)))

Answer 2

I figured it out, we couldnot use the -> operator for NotIn predicate. I had to create a new instance of NotIn Predicate and pass it to the where clause like below to make it work.

$select -> where(new NotIn('products.pro_id',$subSelect))

Thanks Fouad for the help. or as Fouad hinted, I tried below and it worked too!.

$select -> where->addPredicate(new \Zend\Db\Sql\Predicate\Expression('products.pro_id NOT IN (?)', array($subSelect)))