Magento 1.9 – How to Filter Products by Category and Attributes

attributescategory-productsdatabasefiltermagento-1.9

I have searched about how to filter and I have tested about 20 codes but always return "".
The situation is the following:
– For the current category, I have 8 attributes, 7 are multiselect checkbox and the last one is radio button.
For example:
Attribute code: eniso20345_2011. The selected checkbox value for this one is "S1" and "S3". The current category is "shoes".

So I want to get all products which have the attribute eniso20345_2011 = ("S1" and "S3") into the category "shoes". How can I do that?

This code run but I do not want information about one specific sku, I want all SKU which have specific attributes values and specific category.

$productsS1 = Mage::getModel('catalog/product')
            ->getCollection()
            ->addAttributeToSelect('sku')
            ->addAttributeToFilter('sku',"CUMADE000036")
            // ->addAttributeToFilter('eniso20345_2011', 'S1')
            ->load();

The idea is the commented line but this line return empty.

Thanks,

SQL Return:
SELECT e.*, at_category_id.category_id, at_eniso20345_2011.value AS eniso20345_2011 FROM catalog_product_entity AS e LEFT JOIN catalog_category_product AS at_category_id ON (at_category_id.product_id=e.entity_id) INNER JOIN catalog_product_entity_varchar AS at_eniso20345_2011 ON (at_eniso20345_2011.entity_id = e.entity_id) AND (at_eniso20345_2011.attribute_id = '207') AND (at_eniso20345_2011.store_id = 0) WHERE (at_category_id.category_id IN(525)) AND (((FIND_IN_SET('S1', at_eniso20345_2011.value))))

SOLUTION

Well, this is the way for get all products which belongs to determined category and have determined value or values for determined attribute or attributes:

    $products = Mage::getModel('catalog/category')
    ->load($categoryId)
    ->getProductCollection()
    ->addAttributeToSelect('*')
    ->addAttributeToFilter(
    array(array('attribute'=>'eniso20345_2011', 'finset'=>1933)))
    ->joinField(
        'category_id',
        'catalog/category_product',
        'category_id',
        'product_id=entity_id',
        null,
        'left'
    );
    echo '<br>SIZE : '. $products->getSize() . PHP_EOL;

* The finset value is the Id of the option, if the option has like value 'S1', you have to use the ID associated to 'S1'.

I hope that this can help everyone with doubts about this.

Best Answer

The following code will filter all the products/skus with value S1 for attribute eniso20345_2011 under the shoes category

    $yourShoesCategoryId = 22; // replace it with your category id

    $_productCollection =   Mage::getResourceModel('catalog/product_collection')
    ->joinField('category_id','catalog/category_product','category_id','product_id=entity_id',null,'left')
    ->addAttributeToFilter('category_id', array('in' => $yourShoesCategoryId))
    ->addAttributeToFilter(
        array(
              array('attribute'=>'eniso20345_2011', 'finset'=>array('S1')),
              array('attribute'=>'eniso20345_2011', 'finset'=>array('S2')),
        )
    )
    ->addAttributeToSelect('*');

    $_productCollection->load();

     echo 'SIZE : '. $_productCollection->getSize() . PHP_EOL; 

     echo 'SQL : '. $_productCollection->getSelect()->__toString() . PHP_EOL;

Update #2

$someAttribute = Mage::getModel('eav/config')->getAttribute('catalog_product', 'eniso20345_2011');

$_productCollection =   Mage::getResourceModel('catalog/product_collection')
    ->joinField('category_id','catalog/category_product','category_id','product_id=entity_id',null,'left')
        ->addAttributeToFilter('category_id', array('in' => $yourShoesCategoryId))
        ->addAttributeToFilter($someAttribute,
            array(
                array('finset'=> array('S1')),
                array('finset'=> array('S2')),
            )
        )
        ->addAttributeToSelect('*');

Related Solutions

Magento – How to add attributes set column to category products grid

In the same module that you used for special price (explained here) you can add you attribute set also.
Just add this to the collection init ->addAttributeToSelect('attribute_set_id') and the corresponding column in _prepareColumns().

$sets = Mage::getResourceModel('eav/entity_attribute_set_collection')
            ->setEntityTypeFilter(Mage::getModel('catalog/product')->getResource()->getTypeId())
            ->load()
            ->toOptionHash();
    $this->addColumnAfter('set_name',
        array(
            'header'=> Mage::helper('catalog')->__('Attrib. Set Name'),
            'width' => '100px',
            'index' => 'attribute_set_id',
            'type'  => 'options',
            'options' => $sets,
    ), 'name');

Magento – Adding attributes as a dropdown in Main Category Navigation

Based on this question and this one. I've put up the following "module" that should do what you need.
The main idea is to get the categories menu, identify the attributes used in layered navigation and display a link to the filtered category for each option in the attribute.
The extension is called Easylife_Nav. The extension files:

app/etc/modules/Easylife_Nav.xml - the declaration file

<?xml version="1.0"?>
<config>
    <modules>
        <Easylife_Nav>
            <codePool>local</codePool>
            <active>true</active>
            <depends>
                <Mage_Catalog />
            </depends>
        </Easylife_Nav>
    </modules>
</config>

app/code/local/Easylife/Nav/etc/config.xml - the configuration file

<?xml version="1.0"?>
<config>
    <modules>
        <Easylife_Nav>
            <version>0.0.1</version>
        </Easylife_Nav>
    </modules>
    <global>
        <helpers>
            <easylife_nav>
                <class>Easylife_Nav_Helper</class>
            </easylife_nav>
        </helpers>
        <models>
            <easylife_nav>
                <class>Easylife_Nav_Model</class>
            </easylife_nav>
        </models>
    </global>
    <frontend>
        <events>
            <page_block_html_topmenu_gethtml_before> <!-- observe the menu generation event -->
                <observers>
                    <easylife_nav>
                        <class>easylife_nav/observer</class>
                        <method>addAttributes</method>
                    </easylife_nav>
                </observers>
            </page_block_html_topmenu_gethtml_before>
        </events>
    </frontend>
</config>

app/code/local/Easylife/Nav/Helper/Data.php - the module main helper

<?php
class Easylife_Nav_Helper_Data extends Mage_Core_Helper_Abstract {

}

app/code/local/Easylife/Nav/Model/Observer.php - the module observer

<?php
class Easylife_Nav_Model_Observer {
    public function addAttributes($observer){
        $menu = $observer->getMenu();
        $tree = $menu->getTree();
        //get all first level menu items
        foreach ($menu->getChildren() as $child){
            $nodeId = $child->getId();
            //check if the node id starts with `category-node-` - so it's a category
            if (substr($nodeId, 0, strlen('category-node-')) == 'category-node-') {
                //identify the category id
                $id = str_replace('category-node-', '', $nodeId);
                //remember the main category url
                $mainUrl = $child->getUrl();
                //get category filtrable attributes
                $attributes = $this->getAttributes($id);
                foreach ($attributes as $attribute) { 
                    //get the options for each attribute
                    $options = $attribute->getSource()->getAllOptions();
                    if (count($options) > 0) {
                        //add "By ..." menu item - non clickable
                        $attrNodeId = 'attribute-'.$id.'-'.$attribute->getId();
                        $data = array(
                            'name' => Mage::helper('easylife_nav')->__('By %s', $attribute->getFrontendLabel()),
                            'id' => $attrNodeId,
                            'url' => '#',
                            'is_active' => false
                        );
                        $attrNode = new Varien_Data_Tree_Node($data, 'id', $tree, $menu);
                        //for each option add a new sub menu
                        foreach ($options as $option) {
                            if ($option['value']) {
                                $optionNodeId = 'attribute-'.$id.'-'.$attribute->getId().'-'.$option['value'];
                                $data = array(
                                    'name' => $option['label'],
                                    'id' => $optionNodeId,
                                    'url' => $mainUrl.'?'.$attribute->getAttributeCode().'='.$option['value'],
                                    'is_active' => false
                                );
                                $optionNode = new Varien_Data_Tree_Node($data, 'id', $tree, $menu);
                                $attrNode->addChild($optionNode);
                            }
                        }
                        $child->addChild($attrNode);
                    }
                }
            }
        }
    }
    //get allowed attributes in that category
    public function getAttributes($categoryId) {
        $layer = Mage::getModel("catalog/layer");
        $layer->setCurrentCategory(Mage::getModel('catalog/category')->load($categoryId));
        $validAttributes = array();
        foreach ($layer->getFilterableAttributes() as $attribute) {
            //allow only select attributes - you can implement your additional filters here
            if ($attribute->getFrontendInput() == 'select'){
                $validAttributes[] = $attribute;
            }
        }
        return $validAttributes;
    }
}

Clear the cache and try it out.

Best Answer

Related Solutions

Magento – How to add attributes set column to category products grid

Magento – Adding attributes as a dropdown in Main Category Navigation

Related Topic