I have created a new product eav attribute, assume it is created correctly. Now I write something in this attribute in some products (5 for example), and I delete it from some of them (4). So this attribute only exist in 5 products, one is written and other 4 are null, but it doesn't exist yet in rest of products.

How do I get all product which don't have that attribute set or have it null?

I can do it with ones that have this value set:

$_products = Mage::getModel('catalog/product')
      ->getCollection()
      ->addAttributeToSelect(array('sku', 'name', 'newfield'))
      ->addAttributeToFilter(
        array(
            array('attribute'=> 'newfield','null' => true),
            array('attribute'=> 'newfield','eq' => '')
        ));

But it returns me only the 4 products that I have written and erased 'newfield', and not rest of products that doesn't have it set yet.

Edit:

I found a half solution (Filter collection by existence of attribute).

$_products = Mage::getModel('catalog/product')
      ->getCollection()
      ->addAttributeToSelect(array('sku', 'name', 'gtin'))
      ->addAttributeToFilter('newfield', array('null' => true), 'left');

I do not understand the third argument 'left', but this one returns me all the products with NULL in the newfield or where it does not exist yet (more than 13000 products), but I want to make an OR condition, I also want the products that contains string(0) "" or string(8) "NO FIELD"

I tried

$_products = Mage::getModel('catalog/product')
      ->getCollection()
      ->addAttributeToSelect(array('sku', 'name', 'newfield'))
      ->addAttributeToFilter(
        array(
            array('attribute'=> 'newfield','null' => true, 'left'),
            array('attribute'=> 'newfield','eq' => ''),
            array('attribute'=> 'newfield','eq' => 'NO FIELD')
        ));

But it is not acting like an OR condition and I only get back 3 results instead of more than 13000, probably it is because i'm not using the third argument 'left' properly.

Edit2:

I finally found something about the third parameter 'left' here (http://blog.adin.pro/2013-03-07/left-join-on-addattributetofilter-with-eav-tables/). But I see that I'm using like this in my OR condition and still getting only 3 results instead of more than 13000. What am I doing wrong here?

Edit3: Seems a definitive solution

I finally found this (https://hannah.wf/snippet-magento-using-or-and-left-join-in-addattributetofilter/), and then I made it:

$_products = Mage::getModel('catalog/product')
      ->getCollection()
      ->addAttributeToSelect(array('sku', 'name', 'newfield'))
      ->addAttributeToFilter(
        array(
            array('attribute'=> 'newfield','null' => true),
            array('attribute'=> 'newfield','eq' => ''),
            array('attribute'=> 'newfield','eq' => 'NO FIELD')
        ),
        '',
        'left');

It seems to return all the products I'm looking for, but I don't understand why it has to end with:

[...]
,
'',
'left');

My own logic says it should be

[...]
,
'left', //for the first condition
'',     //for the second condition
'');    //for the thrid condition

but my logic is returning only 3 results, so it's not working. The first one seems to work fine, but I don't understand why '', 'left'.

  • check after removing array('attribute'=> 'newfield','eq' => '') from filter – Piyush May 26 '17 at 14:16
  • remove array('attribute'=> 'newfield','eq' => '') it's not working. Leaving array('attribute'=> 'newfield','null' => true) only only returns the products where the newfield is set and is NULL , the only difference is that the products where the fields contain a string(0) "" are not returned. – Alex May 28 '17 at 21:02
up vote 6 down vote accepted

After some tests, the code I wrote in the third edit of the question seems to work fine, so I think it is the correct answer:

$_products = Mage::getModel('catalog/product')
  ->getCollection()
  ->addAttributeToSelect(array('sku', 'name', 'newfield'))
  ->addAttributeToFilter(
    array(
        array('attribute'=> 'newfield','null' => true),
        array('attribute'=> 'newfield','eq' => ''),
        array('attribute'=> 'newfield','eq' => 'NO FIELD')
    ),
    '',
    'left');

I checked it and it returns me the products where the newfield is not set yet, and the products where newfield is NULL, string(0) or string(8) "NO FIELD".

PD: if only need one condition and do not need the OR, then it is enough with:

$_products = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToSelect(array('sku', 'name', 'newfield'))
    ->addAttributeToFilter('newfield', array('null' => true), 'left');
  • how to add attribute null filter in product list page i am not able how to solve it please help me . in which file add filter condition ?@Alex – Devidas Aug 28 at 10:08

try to filter collection with attribute value in_array

->addAttributeToFilter('newfield', array('in' => $array));

where $array = array(null,'');

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

It's working and get data not eq 1 and null value

// this code is getting product collection. // Suppose you have 100 product. // you update 10 product after attributenewfieldcreated. // 5 product update with 1 value and 5 product update with 0 value // So you got the 95 product in collection using this code. // 5 + 90(rest of all)


$_products = Mage::getModel('catalog/product') ->getCollection() ->addAttributeToSelect(array('sku', 'name', 'newfield')) ->addAttributeToFilter( array( array('attribute'=> 'newfield','null' => true), array('attribute'=> 'newfield','eq' => '1'), ), '', 'left');

For magento 2, use the following code:

use \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory;

$_products = $this->_productCollectionFactory->create()

 ->addAttributeToSelect(array('sku', 'name', 'newfield'))

 ->addAttributeToFilter(
  array(

    array('attribute'=> 'newfield','null' => true),

    array('attribute'=> 'newfield','eq' => ''),

    array('attribute'=> 'newfield','eq' => 'NULL')

),

'',
'left');

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.