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