Magento: Adding filterable attribute list to template file

Question

I have a Men and Women store setup under the same website in Magento. The Men website runs off of the Men category and the Women site off of the Women category. I have a filterable custom attribute setup entitled 'designer'. Currently, the layered navigation on a category page shows the Designer attribute for the products in that store along with any designers that are related to a product. I would like to show this list on another page.

I would like to take the Designer list that shows up in the layered navigation and put it into a template file. The idea is that a user will come to my site and want to see a list of all the designers I have in stock depending on which store they are viewing. For the Men store they would see a list of all men designers in stock and the same for the Women store. From this designer page they can choose there favorite designer and shop all products for that designer.

I'm having trouble getting this accomplished. I can get a list of all the designers, however I cannot seem to filter it by the store category. Here is the relevant code I am using currently - is there a better way of doing this? Any help is appreciated.

//load the current category
$store_category = Mage::app()->getStore()->getRootCategoryId();

//get all product designers
$product = Mage::getModel('catalog/product');
$attributes = Mage::getResourceModel('eav/entity_attribute_collection')
   ->setEntityTypeFilter($product->getResource()->getTypeId())
   ->addFieldToFilter('attribute_code', 'designer') // This can be changed to any attribute code
   ->load(false);
$attribute = $attributes->getFirstItem()->setEntity($product->getResource());
$designers = $attribute->getSource()->getAllOptions(false);

//get all products
$collection = Mage::getModel('catalog/product')->getCollection();
$new_collection = Mage::getModel('catalog/category')->load($store_category)->getProductCollection();


//filter to only get visible products
$collection->addAttributeToFilter('visibility', Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH);

//filter by category - not working correctly
$collection->addCategoryFilter(Mage::getModel('catalog/category')->load($store_category));

//get products in stock
$collection->joinField('stock_status','cataloginventory/stock_status','stock_status',
      'product_id=entity_id', array(
      'stock_status' => Mage_CatalogInventory_Model_Stock_Status::STATUS_IN_STOCK
   ));

//get all products with the designer attribute
$collection->addAttributeToSelect('designer');

//count the number of designers
$collection->addExpressionAttributeToSelect('designers_count', 'COUNT({{attribute}})', 'designer');

//group by the designer - limits the collection by products that have a designer setup
$collection->groupByAttribute('designer');
echo 'collection count->'.$collection->count();
//loop through collection and add the number of designers and designer id to an array
foreach($collection as $item)
{
   //get the designer id and the designers count
   $designer_id = $item->getDesigner();
   $designers_count = $item->getData('designers_count');

   //skip if the designers count is 0
   if($designers_count == 0)
   {
      continue;
   }// if

   //skip if the designer_id is empty
   if(empty($designer_id))
   {
      continue;
   }// if

   //add information to array
   $designers_in_use[$designer_id] = $designers_count;
}// foreach

Answer 1

Magento uses index tables to generate the layered navigation: in magento version 1.3 you have the catalogindex_eav table (store_id,entity_id, attribute_id,value) in magento version 1.4 you have several tables but I think you only need catalog_product_index_eav (entity_id, attribute_id, store_id,value)

For magento 1.4 take a look at the Mage_Catalog_Model_Resource_Eav_Mysql4_Layer_Filter_Attribute class - getCount method:

the basic idea is to make a join between the product collection and the catalog_product_index_eav table:

// excerpt from Mage_Catalog_Model_Resource_Eav_Mysql4_Layer_Filter_Attribute getCount()
$conditions = array(
            "{$tableAlias}.entity_id = e.entity_id",
            $connection->quoteInto("{$tableAlias}.attribute_id = ?", $attribute->getAttributeId()),
            $connection->quoteInto("{$tableAlias}.store_id = ?", $filter->getStoreId()),
        );

        $select
            ->join(
                array($tableAlias => $this->getMainTable()),
                join(' AND ', $conditions),
                array('value', 'count' => "COUNT({$tableAlias}.entity_id)"))
            ->group("{$tableAlias}.value");  

Answer 2

OK - So I figured out how to do this. First, I created a designers/view.phtml directory and file within the app/design/frontend/my-layout/default/template directory. The I found the CMS page I wanted to add the Designers to and called this block in the content: {{block type="catalog/layer_view" template="designers/view.phtml"}}

From here it was pretty simple. I programmed code similar to this and placed it in the above template file - so much easier then doing it the way I was before.

$filters = $this->getFilters();

foreach($filters as $filter)
{
   if($filter->getName()=='Designer')
   {
      echo '<ul>';
      foreach ($filter->getItems() as $_item)
      {
         echo '<li><a href="'.$filter->urlEscape($_item->getUrl()).'">'.$_item->getLabel().'</a></li>';
      }// foreach
      echo '</ul>';

      //stop foreach execution
      break;
   }//if
}// foreach