Magento: Combine eq and is null in addAttributeToFilter

Question

I want to combine with OR "is null" and "eq" => array()

$collection->addAttributeToFilter('attributename', array('is' => null));
$collection->addAttributeToFilter('attributename', array(115,116));

But this does not work.

Answer 1

To use addAttributeToFilter() with an OR condition you can pass an array of arrays like this:

$collection->addAttributeToFilter(
    array(
        array(
            'attribute' => 'name_of_attribute_1',
            'null' => 'this_value_doesnt_matter'
        ),
        array(
            'attribute' => 'name_of_attribute_2',
            'in' => array(115, 116)
        )
    )
);

The definition of filtering against NULL keywords may look somewhat confusing at first glance, but it's plain simple, once you get used to it.

Magento supports two filter types of type string, namely 'null' and 'notnull', for comparisions against NULL keywords.

Be aware, that even though you need to pass key=>value pairs (because an associative array is used), the value will be always ignored, when filter type 'null' or 'notnull' is used.

Example

var_dump(
    Mage::getModel('catalog/product')
    ->getCollection()
    ->addFieldToFilter(
        array(
            array(
                'attribute' => 'description',
                'null' => 'this_value_doesnt_matter'
            ),
            array(
                'attribute' => 'sku',
                'in' => array(115, 116)
            )
         )
     )
     ->getSelectSql(true)
 );

The output of the dump may vary depending on your shop configuration (attribute ids, count of stores, etc.), but the OR logic in the WHERE clause should always remain the same:

SELECT 
    `e`.*,
    IFNULL(_table_description.value, _table_description_default.value) AS `description`
FROM
    `catalog_product_entity` AS `e`
INNER JOIN
    `catalog_product_entity_text` AS `_table_description_default` ON
    (_table_description_default.entity_id = e.entity_id) AND
    (_table_description_default.attribute_id='57') AND
    _table_description_default.store_id=0
LEFT JOIN
    `catalog_product_entity_text` AS `_table_description` ON
    (_table_description.entity_id = e.entity_id) AND
    (_table_description.attribute_id='57') AND
    (_table_description.store_id='1')
WHERE (
    (IFNULL(_table_description.value, _table_description_default.value) is NULL) OR
    (e.sku in (115, 116))
)

Answer 2

• I have product attribute is_expired with Yes and No value and i want only those product have NO value.below query work for me that get record that have neq 1 and not null.

  • Combine not equal to and null condition WITH OR.

     $collection->addAttributeToFilter('attribue_name', array('or'=> array(
         0 => array( 'neq' => '1'),
         1 => array('is' => new Zend_Db_Expr('null')))
     ), 'left');
    

• You can changed neq with eq.