Magento Left Join on Attribute filters with EAV tables

Joaquín Ruiz - 2nd August 2016

Magento Left Join on Attribute filters with EAV tables
  • Date:   2nd August 2016
  • Comments:

      0

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

The Author

Joaquín Ruiz
Computer Engineer, Senior Full-Stack Developer and Lead Developre. Joki has more than 8 years of experience working with multiple PHP frameworks. He knows Magento, WordPress, Laravel, Yii.. like the back of his hand ;)