Magento2 Product Collection To Get Total Quantity Of All Simples And Minimum Price From All Simples of Configurable Product

Hello Guys,
This post explains about the Magento configurable product collection to retrieve the total sum of all quantity and minimum price from all the simples child of parent configurable product.
We generally come across this situation for configurable products prices and quantity display for visual merchandiser products in catalog category edit page. To resolve this price and quantity issue, we can use the following as per your requirement as following:

1) If you want to get the quantity sum of all the simples of an configurable product, you can use the following code snippet of Magento2 collection.

$configProductId = 10;
$collection->getSelect()->from(
            ['cpe' => 'catalog_product_entity'],
            ['entity_id', 'row_id']
        )->joinInner(
            ['cpr' => 'catalog_product_relation'],
            'cpe.entity_id = cpr.parent_id',
            ['cpr.parent_id']
        )->joinInner(
            ['csi' => 'cataloginventory_stock_item'],
            'csi.product_id = cpr.child_id',
            ['qtySum' => new \Zend_Db_Expr('SUM(csi.qty)')]
        )->where('cpe.type_id = "configurable" AND cpe.row_id = "' . $configProductId . '"');
return $collection->getConnection()->fetchAll($collection->getSelect());

2) If you want to get the minimum price from all the simples of an configurable product, you can use the following code snippet of Magento2 collection.

$configProductId = 10;
$attributeId = '67'; // attribute id for price
$storeId = 0;
$collection->getSelect()->from(
            ['cpe' => 'catalog_product_entity'],
            ['entity_id', 'row_id']
        )->joinInner(
            ['cpr' => 'catalog_product_relation'],
            'cpe.entity_id = cpr.parent_id',
            ['cpr.parent_id']
        )->joinInner(
            ['cped' => 'catalog_product_entity_decimal'],
            'cped.row_id = cpr.child_id AND (cped.attribute_id = ' . $attributeId . ')
                AND (cped.store_id = ' . $storeId . ')',
            ['priceMin' => new \Zend_Db_Expr('MIN(cped.value)')]
        )->where('cpe.type_id = "configurable" AND cpe.row_id = "' . $configProductId . '"');
return $collection->getConnection()->fetchAll($collection->getSelect());

3) If you want to get both the quantity sum of all the simples and minimum price from all the simples of an configurable product, you can use the following code snippet of Magento2 collection.

$configProductId = 10;
$attributeId = '67'; // attribute id for price
$storeId = 0;
$collection->getSelect()->from(
            ['cpe' => 'catalog_product_entity'],
            ['entity_id', 'row_id']
        )->joinInner(
            ['cpr' => 'catalog_product_relation'],
            'cpe.entity_id = cpr.parent_id',
            ['cpr.parent_id']
        )->joinInner(
            ['csi' => 'cataloginventory_stock_item'],
            'csi.product_id = cpr.child_id',
            ['qtySum' => new \Zend_Db_Expr('SUM(csi.qty)')]
        )->joinInner(
            ['cped' => 'catalog_product_entity_decimal'],
            'cped.row_id = cpr.child_id AND (cped.attribute_id = ' . $attributeId . ')
                AND (cped.store_id = ' . $storeId . ')',
            ['priceMin' => new \Zend_Db_Expr('MIN(cped.value)')]
        )->where('cpe.type_id = "configurable" AND cpe.row_id = "' . $configProductId . '"');
return $collection->getConnection()->fetchAll($collection->getSelect());

Hope this helps you guys!

 

Leave a Reply