CODEWITHSUNDEEP
mysqlmagento
Marcin Kozak
Marcin Kozak

Reputation: 49

Join used products for products collection

I have been searching a solution for my problem without effects.

I want to list simple and configurable products and used products of configurable. The problem is with performance because to get used products I must use this method:

Mage::getModel('catalog/product_type_configurable')->getUsedProducts(null, $product)

which belongs only to one item. You can imagine that for a lots of products there are a huge number of SQL queries. How to make query which adds used_products attribute to colection?

Upvotes: 2

Views: 259

Answers (3)

Fabian Schmengler
Fabian Schmengler

Reputation: 24551

The getUsedProductCollection() that @b.enoit.be suggests is a good starting point.

Original code:

public function getUsedProductCollection($product = null)
{
    $collection = Mage::getResourceModel('catalog/product_type_configurable_product_collection')
        ->setFlag('require_stock_items', true)
        ->setFlag('product_children', true)
        ->setProductFilter($this->getProduct($product));
    if (!is_null($this->getStoreFilter($product))) {
        $collection->addStoreFilter($this->getStoreFilter($product));
    }

    return $collection;
}

What you need:

Copied and adjusted to find used products for multiple configurable products:

$collection = Mage::getResourceModel('catalog/product_type_configurable_product_collection')
        ->setFlag('require_stock_items', true)
        ->setFlag('product_children', true);

$collection->getSelect()->where('link_table.parent_id in ?', $productIds);
$collection->getSelect()->group('e.entity_id');

$productIds must be an array which contains all IDs of your configurable products. It doesn't matter if it also contains IDs of simple products. You could build a JOIN instead but since you need these anyway I'd suggest you load the original collection first and the used associated products second. The alternative would probably be a huge query with UNION and JOIN that's hard to understand without significant performance gain.

group('e.entity_id') makes sure every product gets only selected once to avoid exceptions due to duplicate items in the collection.

Upvotes: 2

imso077
imso077

Reputation: 311

If you have performance issues, better to use a direct sql query as it costs less memory, and much faster. Something like this;

$coreResource = Mage::getSingleton('core/resource');
$connect = $coreResource->getConnection('core_write');

$prid = 12535;// Product entity id
$result = $connect->query("SELECT product_id FROM catalog_product_super_link WHERE parent_id=$prid");

while ($row = $result->fetch()):
        $sprid = $row['product_id'];
    // Now sprid contain the simple product id what is associated with that parent
    endwhile;

Upvotes: 0

β.εηοιτ.βε
β.εηοιτ.βε

Reputation: 39194

There is another function in the same model you are using in the file app/code/core/Mage/Catalog/Model/Product/Type/Configurable.php :

public function getUsedProductCollection($product = null)
{
    $collection = Mage::getResourceModel('catalog/product_type_configurable_product_collection')
        ->setFlag('require_stock_items', true)
        ->setFlag('product_children', true)
        ->setProductFilter($this->getProduct($product));
    if (!is_null($this->getStoreFilter($product))) {
        $collection->addStoreFilter($this->getStoreFilter($product));
    }

    return $collection;
}

So you may want to try doing this and look what it return to you :

$collection = Mage::getResourceModel('catalog/product_type_configurable_product_collection')
    ->setFlag('require_stock_items', true)
    ->setFlag('product_children', true)
    ->load();

But due to the way Varien_Collection work, if you have the same simple product in two configurable one, you may end up with an error like that :

Uncaught exception 'Exception' with message 'Item (Mage_Catalog_Model_Product) with the same id "some_id_here" already exist'

Upvotes: 1

Related Questions