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
  • Category:

      Magento

  • Comments:

      0

One of the most used method when developing Magento (both 1 and 2) is to filter collections, by attributes or fields. But sometimes, when working with multiple models and complex queries, we need to identify how to make in 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.

Magento Left Join on Attribute filters with EAV tables. INNER JOIN
Magento Left Join on Attribute filters with EAV tables. 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.

Magento Left Join on Attribute filters with EAV tables. LEFT JOIN
Magento Left Join on Attribute filters with EAV tables. LEFT JOIN

The Author

Joaquín Ruiz (aka Joki)
Joki is a Computer Engineer, Senior Full-Stack Developer and Lead Developer, specialized in web and e-commerce. Joki has also a wide international experience as freelance and working in agencies.