Magento addFieldToFilter allow NULLs

Question

When using the Magento collection method addFieldToFilter is it possible to allow filtering by NULL values? I want to select all the products in a collection that have a custom attribute even if no value is assigned to the attribute.

Answer 1

You don't need to use addFieldToFilter.

now the solution:
attributes name is known as code in magento, you just need to use the code below to get all of the products which have a specific attribute as an array

$prodsArray=Mage::getModel('catalog/product')->getCollection()
                                  ->addAttributeToFilter('custom_attribute_code')
                                  ->getItems();

you can also specify certain conditions for attribute's value in addAttributeToFilter in the second parameter of addAttributeToFilter.

you can find this method in this file (for further study):

app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php

Answer 2

Filtering a product collection by null/empty attributes has two possible solutions. Magento uses an INNER JOIN to grab the values of the attributes to filter. BUT if the product attribute is not assigned a value the join will fail, as a database table / relationship is missing.

Solution #1: Use addAttributeToFilter() and change the join type from "inner" (the default) to "left":

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addAttributeToFilter('custom_attribute', array( ... condition options ..), 'left');

Solution #2: Make sure your custom attribute has a default value. Magento is conservative in this regard. Magento will only create the relationship between an attribute and a product if a value is given for the attribute. So in the absence of user-specified value or a default value the attribute will not be accessible for filtering a product even if the attribute appears in the product detail view under the admin panel.

Answer 3

Hi, I see you already found a solution, but there is also this option:

$collection->addFieldToFilter('parent_item_id', array('null' => true));

But if you want to use "NULL" => false, which DOESN'T WORK. (and I noticed you can use elements such as "in", "nin", "eq", "neq", "gt"), you can do this:

$collection->addFieldToFilter('parent_item_id', array('neq' => 'NULL' ));

Hope this is still helpful...