I need to display on homepage a list of colors of products available at the moment in the particular store view.


  • Products have color attribute (the default attribute which exists in Magento just after installation).
  • There are many colors but not all colors are associated with products yet.
  • There are a few store views and each has different products (so also the list of colors on homepage will be different in each store view).
  • If color is associated only with products that are out of stock, it shouldn't be on the list of colors on homepage.

Partial solution:

Based on this thread: https://stackoverflow.com/a/9562720/2461702 I was able to accomplish first part of the job: get the list of colors which are associated with products.

How to filter this list further so that it contains only the colors which are associated with:

  1. products that are NOT out of stock?

  2. products from the current store view?

  3. products from selected category?

Is there any easy way to speed up the code posten in: https://stackoverflow.com/a/9562720/2461702 ?

1 Answer 1

$prods = Mage::getResourceModel('catalog/product_collection')
                ->joinTable('cataloginventory/stock_item','product_id=entity_id', array("stock_status" => "is_in_stock") )
                ->addAttributeToFilter('visibility', Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH)
                ->addAttributeToFilter('color', array('in' => array('the color ids you picked separated by a comma')))
                ->addCategoryFilter($cat) //Mage_Catalog_Model_Category
                ->setOrder('position', 'desc')
                ->setPageSize(($i == 0) ? 12 : 6);

$prods->getSelect()->where("cataloginventory_stock_item.is_in_stock = '1'");

It'll get you the products associated with the colors you listed, and will only give you the products that are in stock for a given category. If you are working with flat catalog for products, then the store filter isn't needed because it will pick products from catalog_product_flat_X , but if you don't use it, then just throw a


in there.

Hope this helps

  • Thanks. 1) What does this line do exactly: ->joinTable('cataloginventory/stock_item','product_id=entity_id', array("stock_status" => "is_in_stock"))? 2) If there's already ->addAttributeToSelect('*') is it necessary to select another attribute in this line ->addAttributeToSelect('stock_status')?
    – zitix
    Commented Mar 17, 2014 at 11:24
  • The quoted line joins the inventory table to the product_entity or product_flat table as this data is not stored there, allowing you to select the "stock_status" which is an alias at this point, telling you wether or not the product is_in_stock. I'm pretty sure you have to explicitly add this to the select for the join happens after the ->addAttributeToSelect('*'). Feel free to accept the answer if it suits your need or to comment so as I can improve it. Commented Mar 17, 2014 at 14:11

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.