Group and/or product collection select

Question

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.

Answer 1

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!!

Answer 2

Standard magento functionality doesn't allow to do that. But you can always getSelect() and add Zend_Statment using where() method.

Also I take a look inside Mage_Eav_Model_Entity_Collection_Abstract::addAttributeToFilter() and investigated how it works.

So I can advice you to rewrite Mage_Eav_Model_Entity_Collection_Abstract and write your own method:

public function addAndInOrCondition($andInOrConditions)
{
    $sqlArr = array();
    foreach ($andInOrConditions as $andConditions) {
        $sqlAndArr = array();
        foreach ($andConditions as $condition) {
            $sqlAndArr[] = $this->_getAttributeConditionSql($condition['attribute'], $condition);
        }
        $sqlArr[] = '('.implode(') AND (', $sqlAndArr).')';
    }
    $conditionSql = '('.implode(') OR (', $sqlArr).')';

    if (!empty($conditionSql)) {
        $this->getSelect()->where($conditionSql, null, Varien_Db_Select::TYPE_CONDITION);
    } else {
        Mage::throwException('Error message');
    }

    return $this;
}

Now your collection will have additional method.

For this condition

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

you should call addAndInOrCondition() like this

$collection = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAndInOrCondition(
        array(
            array(
                array('attribute' => 'size', 'eq' => '10'),
                array('attribute' => 'color', 'eq' => 'red'),
            ),
            array(
                array('attribute' => 'size', 'eq' => '12'),
                array('attribute' => 'price', 'eq' => 'blue'),
            ),
            array(
                array('attribute' => 'size', 'eq' => '14'),
                array('attribute' => 'price', 'eq' => 'green'),
            )
        )
    );

Advantages of this solution:

• compatibility with other collection methods
• you can use all "Filter Condition Codes"
• lazy loading
• can be used in grids with filtering, and ordering
• you can still access collection object after using this solution
• one request without sub-requests to database

Disadvantages

• Rewriting a model

Answer 3

If you are not afraid to build your own where clause (which I would not always suggest). Then what you could do is to use if statements in your where.

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->getSelect()
    ->where('if(entity_id = 16 AND type_id = "simple", TRUE, FALSE) = true')
    ->orWhere('if(entity_id = 17 AND type_id = "simple", TRUE, FALSE) = true')
    ->orWhere('if(entity_id = 18 AND type_id = "simple", TRUE, FALSE) = true');

One final note from me is that there looks like you could also use a mixture of the having function and addExpressionAttributeToSelect. This was taken from another stackexchange answer How to add dynamic field in magento collection?

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addExpressionAttributeToSelect('first_where', 'if({{entity_id}} = 16 AND {{type_id}} = "simple", TRUE, FALSE)', array('entity_id', 'type_id'));
$collection->addExpressionAttributeToSelect('second_where', 'if({{entity_id}} = 17 AND {{type_id}} = "simple", TRUE, FALSE)', array('entity_id', 'type_id'));
$collection->addExpressionAttributeToSelect('third_where', 'if({{entity_id}} = 18 AND {{type_id}} = "simple", TRUE, FALSE)', array('entity_id', 'type_id'));
$collection->getSelect()->having('first_where')->orHaving('second_where')->orHaving('third_where');

But using this I was getting the error Unknown column 'first_where' in 'having clause''