Get color values used in products

Question

Goal:

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

Description:

• 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:

2. products that are NOT out of stock?

3. products from the current store view?

4. products from selected category?

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

Answer 1

$prods = Mage::getResourceModel('catalog/product_collection')
                ->addAttributeToSelect('*')
                ->joinTable('cataloginventory/stock_item','product_id=entity_id', array("stock_status" => "is_in_stock") )
                ->addAttributeToSelect('stock_status')
                ->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

->addStoreFilter(Mage::app()->getStore()->getId())

in there.

Hope this helps