Filter category collection with nested OR/AND statements

Question

So I want to filter a category collection with two attributes. The first attribute attribute_1 must be like a given value. The other attribute may either be like another given value or empty (NULL).

In SQL you would write:

[...] WHERE
    (
        attribute_1 LIKE '%foo%'
        AND
        attribute_2 LIKE '%bar%'    
    )
    OR
    (
        attribute_1 LIKE '%foo%'
        AND 
        attribute_2 is NULL
    )

Trying to replicate this in Magento after having investigated how nested filtering should work, I came up with this:

$categories = Mage::getModel('catalog/category')
    ->getCollection()
    ->addAttributeToSelect('*')
    ->addAttributeToFilter(
        array(
            array(
                array(
                    'attribute' => 'attribute_1',
                    'like' => '%foo%'
                ),
                array(
                    'attribute' => 'attribute_2',
                    'like' => '%bar%'
                ),
            ),
            array(
                array(
                    'attribute' => 'attribute_1',
                    'like' => '%foo%'
                ),
                array(
                    'attribute' => 'attribute_2',
                    'null' => true
                ),
            ),
        )
    );

But unfortunately this results in the following error:

PHP Fatal error: Call to a member function getBackend() on boolean in /path/app/code/core/Mage/Eav/Model/Entity/Abstract.php on line 816 Fatal error: Call to a member function getBackend() on boolean in /path/app/code/core/Mage/Eav/Model/Entity/Abstract.php on line 816

I'm hoping that anyone here may see my error. Many thanks!

Answer 1

Unfortunately you cannot nest conditions and combine AND and OR arbitrarily like this. I tried to explain the parameters for addFieldToFilter here: addFilter vs addFieldToFilter

addFieldToFilter() can take an array of fields with an array of conditions, or a single field with a single condition:

...

What we cannot do with this is the inner combinations with AND. Usually that would mean that you have to resort to Zend_Db_Expr or manipulating the Zend_Db_Select query directly.

But in your case, we can rearrange the query to make it fit:

(
    attribute_1 LIKE '%foo%'
    AND
    attribute_2 LIKE '%bar%'    
)
OR
(
    attribute_1 LIKE '%foo%'
    AND 
    attribute_2 is NULL
)

is equivalent to

attribute_1 LIKE '%foo%'
AND
(
    attribute_2 LIKE '%bar%'    
    OR 
    attribute_2 is NULL
)

And this works well with Magento, because separate calls to addAttributeToFilter are combined with AND, and conditions for a single attribute can be combined with OR:

$collection->addAttributeToFilter('attribute_1', ['like' => '%foo%']);
$collection->addAttributeToFilter('attribute_2', [['like' => '%bar%'], ['null' => true]]);

Answer 2

But if you have diffrent conditions, way to do this is directly via Zend_Db_Select::where function:

$collection->getSelect()->where(
    "(attribute_1 LIKE '%foo%' AND attribute_2 LIKE '%bar%') OR
    attribute_1 LIKE '%baz%' AND attribute_2 is NULL"
);

will add to WHERE clause

WHERE (...)
AND
(
    (
        attribute_1 LIKE '%foo%'
        AND
        attribute_2 LIKE '%bar%'    
    ) OR (
        attribute_1 LIKE '%baz%'
        AND 
        attribute_2 is NULL
    )
)