Use of sub query within collection in Magento

Question

I am using a subquery in Magento collection,

Here is my code.

$websiteId = $this->storeManager->getStore()->getWebsiteId();

$collection = $this->_productCollectionFactory->create();
$collection->setVisibility($this->_productVisibility->getVisibleInSiteIds());
$collection->addAttributeToSelect('*');
$collection->addAttributeToSelect(array('entity_id','sku'));
$collection->addWebsiteFilter($websiteId);
$collection->addStoreFilter();
$collection->addAttributeToFilter('visibility', \Magento\Catalog\Model\Product\Visibility::VISIBILITY_BOTH);
$this->_stockFilter->addInStockFilterToCollection($collection);
$collection = $collection
    ->joinField('qty',
        'cataloginventory_stock_item',
        'qty',
        'product_id=entity_id',
        '{{table}}.stock_id=1',
        'left'
    )
    ->addAttributeToSelect('stock_status')
    ->addAttributeToSort ( 'entity_id', 'DESC' )
    ->addAttributeToFilter('status',\Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED);

$collection->addAttributeToFilter(array(
    array(
            'attribute' => 'qty',
            'gt'=>0),
        array(
            'attribute' => 'type_id',
            'eq' => 'grouped'),
        array(
        'attribute' => 'type_id',
        'eq' => 'configurable')
))->load();
$subquery = new \Zend_Db_Expr('(SELECT b.parent_id FROM cataloginventory_stock_item AS a JOIN catalog_product_relation AS b ON a.product_id = b.child_id WHERE a.qty <= 0)');

$collection->addFieldToFilter('e.entity_id', array('nin' => $subquery));

But I am getting error after using the above code, Can anyone help me on how to use a subquery in magneto 2 collections.

Thank you for your help.

Answer 1

$collection->getSelect()->where('e.entity_id NOT IN(?)', new \Zend_Db_Expr('(SELECT b.parent_id FROM cataloginventory_stock_item AS a JOIN catalog_product_relation AS b ON a.product_id = b.child_id WHERE a.qty <= 0)') );