Product Collection – Group and/or Select

attributescollection;product

Is it possible to group and/or together when creating a product collection

I have 2 attributes size and color.

I want to find products that have the following:

Size = 10 and Color = red
or
Size = 12 and Color = blue
or
Size = 14 and Color = green

It doesnt appear that the addAttributeToFilter method provides the possibility of doing these groups and instead allows only and or or.

Best Answer

You can't do that in Magento! What were you thinking!?!

Just kidding. It helps to think about this outside of the bounds of Magento and instead in terms of Magento's underlying ORM, Zend_Db.

Because Zend_Db only supports the orWhere method, and has no means of grouping those OR statements, the only way I can think of doing this is by performing a UNION of 3 queries all with the varying clauses.

So, thinking outside of Zend even, you would then rewrite your query as separate SELECT statements rather than OR clauses:

Statement 1

...WHERE size = '10' and color = 'red';

Statement 2

...WHERE size = '12' and color = 'blue';

Statement 3

...WHERE size = '14' and color = 'green';

And assuming we had the raw SQL from each of these we could probably do a UNION ALL on them:

join(' UNION ALL ', $statements);

If we execute this query against the DB natively we'll get what we're looking for. So that's how we'll do it - utilizing getSelect:

$collection[] = Mage::getModel('catalog/product')->getCollection()
->addAttributeToFilter('size','10')
->addAttributeToFilter('color','red')
->getSelect();

$collection[] = Mage::getModel('catalog/product')->getCollection()
->addAttributeToFilter('size','12')
->addAttributeToFilter('color','blue')
->getSelect();

$collection[] = Mage::getModel('catalog/product')->getCollection()
->addAttributeTofilter('size','14')
->addAttributeTofilter('color','green')
->getSelect();


$query = join(' UNION ALL ',$collection);

$result = Mage::getSingleton('core/resource')->getConnection('core_read')->fetchAll($query);

Which yields the query we're looking for:

SELECT `e`.*, `at_size`.`value` AS `size`, `at_color`.`value` AS `color` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_entity_int` AS `at_size` ON (`at_size`.`entity_id` = `e`.`entity_id`) AND (`at_size`.`attribute_id` = '198') AND (`at_size`.`store_id` = 0)
 INNER JOIN `catalog_product_entity_int` AS `at_color` ON (`at_color`.`entity_id` = `e`.`entity_id`) AND (`at_color`.`attribute_id` = '92') AND (`at_color`.`store_id` = 0) WHERE (at_size.value = '10') AND (at_color.value = 'red')
UNION ALL
SELECT `e`.*, `at_size`.`value` AS `size`, `at_color`.`value` AS `color` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_entity_int` AS `at_size` ON (`at_size`.`entity_id` = `e`.`entity_id`) AND (`at_size`.`attribute_id` = '198') AND (`at_size`.`store_id` = 0)
 INNER JOIN `catalog_product_entity_int` AS `at_color` ON (`at_color`.`entity_id` = `e`.`entity_id`) AND (`at_color`.`attribute_id` = '92') AND (`at_color`.`store_id` = 0) WHERE (at_size.value = '12') AND (at_color.value = 'blue')
UNION ALL
SELECT `e`.*, `at_size`.`value` AS `size`, `at_color`.`value` AS `color` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_entity_int` AS `at_size` ON (`at_size`.`entity_id` = `e`.`entity_id`) AND (`at_size`.`attribute_id` = '198') AND (`at_size`.`store_id` = 0)
 INNER JOIN `catalog_product_entity_int` AS `at_color` ON (`at_color`.`entity_id` = `e`.`entity_id`) AND (`at_color`.`attribute_id` = '92') AND (`at_color`.`store_id` = 0) WHERE (at_size.value = '14') AND (at_color.value = 'green')

Hope that helps!!

Related Solutions

Magento – addAttributeToFilter with dropdown values

by default you cannot filter by color (or any dropdown attribute) like that. The cleanest way to do it is to get the option id for the red color.

$collection = Mage::getModel('catalog/product')->getCollection();
$options = Mage::getModel('eav/config')->getAttribute('catalog_product', 'color')->getSource()->getAllOptions(); //get all options
$optionId = false;
foreach ($options as $option) {
    if (strtolower($option['label']) == 'red'){ //find the Red option
        $optionId = $option['value']; //get it's id
        break;
    }
}
if ($optionId) { //if there is an id...
    $collection->addAttributeToFilter('color', $optionId);
}

But this could backfire if you have 2 options with the same name.

There is also this option:

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->getSelect()->where('color_value = ?', 'Red');

But it works only when you have the flat catalog enabled and when the color attribute is set to be "Used in product listing".
I don't recommend this approach. It can get you into trouble.

Magento – Get available option values for product collection

Have a look at how it's been done in the layered navigation:

the responsible method is Mage_Catalog_Model_Resource_Layer_Filter_Attribute::getCount():

/**
 * Retrieve array with products counts per attribute option
 *
 * @param Mage_Catalog_Model_Layer_Filter_Attribute $filter
 * @return array
 */
public function getCount($filter)
{
    // clone select from collection with filters
    $select = clone $filter->getLayer()->getProductCollection()->getSelect();
    // reset columns, order and limitation conditions
    $select->reset(Zend_Db_Select::COLUMNS);
    $select->reset(Zend_Db_Select::ORDER);
    $select->reset(Zend_Db_Select::LIMIT_COUNT);
    $select->reset(Zend_Db_Select::LIMIT_OFFSET);

    $connection = $this->_getReadAdapter();
    $attribute  = $filter->getAttributeModel();
    $tableAlias = sprintf('%s_idx', $attribute->getAttributeCode());
    $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' => new Zend_Db_Expr("COUNT({$tableAlias}.entity_id)")))
        ->group("{$tableAlias}.value");

    return $connection->fetchPairs($select);
}

Unfortunately you can't use that straight away with any collection because it's too tightly coupled to the filter model, which again doesn't handle arbitrary product collections.

So your best option is probably to copy what you need from there into an own resource model like this (untested):

class Your_Extension_Model_Resource_Attribute extends Mage_Core_Model_Resource_Db_Abstract
{
    public function getCount($productCollection, $attributeCode, $storeId)
    {
        // clone select from collection with filters

        //----------------------------------------------------------------
        $select = clone $productCollection->getSelect();
        //----------------------------------------------------------------

        // reset columns, order and limitation conditions
        $select->reset(Zend_Db_Select::COLUMNS);
        $select->reset(Zend_Db_Select::ORDER);
        $select->reset(Zend_Db_Select::LIMIT_COUNT);
        $select->reset(Zend_Db_Select::LIMIT_OFFSET);

        $connection = $this->_getReadAdapter();

        //----------------------------------------------------------------
        $attribute  = Mage::getModel('eav/entity_attribute')->loadByCode($attributeCode);
        //----------------------------------------------------------------

        $tableAlias = sprintf('%s_idx', $attribute->getAttributeCode());
        $conditions = array(
            "{$tableAlias}.entity_id = e.entity_id",
            $connection->quoteInto("{$tableAlias}.attribute_id = ?", $attribute->getAttributeId()),

        //----------------------------------------------------------------
            $connection->quoteInto("{$tableAlias}.store_id = ?", $storeId),
        //----------------------------------------------------------------

        );

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

        return $connection->fetchPairs($select);
    }

}

And then call it like

$options = Mage::getResourceModel('your_extension/attribute')
    ->getCount($collection, 'color', Mage::app()->getStore()->getId());

$options will be an array with the values as key and number of occurances as value. Filtering out the 0's should not be a problem from there.

Best Answer

Related Solutions

Magento – addAttributeToFilter with dropdown values

Magento – Get available option values for product collection

Related Topic