1

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.

2
  • Can you add the error details with your question to have more idea about the issue? Feb 27, 2020 at 8:29
  • Invalid argument passed in query, can you check am I using it correctly? Feb 27, 2020 at 8:45

1 Answer 1

3
$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)') );
1
  • hi @Shailesh, Thanks!!, Its working Great Feb 27, 2020 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.