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.

  • Can you add the error details with your question to have more idea about the issue? – Jaimin Sutariya Feb 27 at 8:29
  • Invalid argument passed in query, can you check am I using it correctly? – jafar pinjar Feb 27 at 8:45
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)') );
| improve this answer | |
  • hi @Shailesh, Thanks!!, Its working Great – jafar pinjar Feb 27 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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