Magento: Combine eq and 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 that will not work.

Answer 1

To use addAttributeToFilter() with an OR clause, you can pass an array of arrays as follows:

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

Defining filtering by NULL keywords may look somewhat confusing at first glance, but it's easy if you get used to it.

Magento supports two types of string filters, namely 'null' and 'notnull' , for comparison with NULL keywords.

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

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 dump output may vary depending on the configuration of your store (attribute identifiers, number of stores, etc.), but the OR logic in the WHERE should always remain unchanged:

 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 a product attribute is_expired with a value of Yes and No, and I want only this product to have no value. I need a query that receives a record that has neq 1 and not null.

  • Combine the non-equal and null WITH OR clause.

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

• You can change neq to eq .

Answer 3

To use addAttributeToFilter with an OR clause and with a left join, you can do something 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)) ) '', 'left' );