Magento Left Join on Attribute filters with EAV tables

Posted by Joaquín Ruiz on 2nd August 2016

Magento Left Join on Attribute filters with EAV tables

addAttributeToFilter is a method that can be called on EAV collections in Magento. This includes product collections, category collections, and customer collections. But when you want to retrieve the non-corresponding entries (like with the neq, null, not null, …. statements) it doesn’t work.

 

For example, let’s think we need to get the products that don’t have media gallery, therefore, one of the conditions to check it is whether the image has a null value.

addAttributeToFilter('image', array('null', true))
INNER JOIN `catalog_product_entity_varchar` AS `at_image` 
ON (`at_image`.`entity_id` = `e`.`entity_id`) AND (`at_image`.`attribute_id` = '85') 
AND (`at_image`.`store_id` = 0)

Result: this condition does not work as we want because the INNER JOIN doesn’t capture the values on the left table if they don’t have a value on the right one.

INNER JOIN
INNER JOIN

 

That is the reason that we need to force the condition to LEFT JOIN.

addAttributeToFilter('image', array('null', true), 'left')
LEFT JOIN `catalog_product_entity_varchar` AS `at_image` 
ON (`at_image`.`entity_id` = `e`.`entity_id`) AND (`at_image`.`attribute_id` = '85') 
AND (`at_image`.`store_id` = 0)

Result: Now we have the desired rows so we can filter by the condition IS NULL.

LEFT JOIN
LEFT JOIN



About the author:

Joaquín Ruiz is a Computer Engineer, Senior PHP Developer and Magento Certified Developer. Joki has more than 7 years of experience working with multiple PHP frameworks. He knows Magento, WordPress, Laravel, Yii.. like the back of his hand ;)