Magento : Filter Collection by Attribute that Doesn't Exist or Null or another condition

Question

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'.

Answer 1

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');

Answer 2

This code is getting product collection. Suppose you have 100 product. You update 10 product after attribute newfield created. 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');

Answer 3

try to filter collection with attribute value in_array

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

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

Answer 4

 $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

Answer 5

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');