2

This is either a simple fix, or something that magento does natively and I'll have to do a workaround (which I still need to know what it is).

Here is my code:

$products = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToSelect('*')
    ->addAttributeToSelect('type')
    ->addFieldToFilter(
        array(
            array('attribute'=>'type_id','eq'=>'simple'),
        )
    )
    ->addFieldToFilter(
        array(
            array('attribute'=>'student_grades', 'eq'=> ''),
            array('attribute'=>'student_grades', 'eq'=> '68,69'),
        )
    )
    ->addCategoryFilter($category)
    ->load(1);

And it generates this SQL (I have <<bracketed>> the join that is the problem):

SELECT `e`.*,
IF(_table_student_grades.value_id>0, _table_student_grades.value, _table_student_grades_default.value) AS `student_grades`,
`cat_index`.`position` AS `cat_index_position`
FROM `catalog_product_entity` AS `e`
-- problem is here!!
<<INNER JOIN>> `catalog_product_entity_varchar` AS `_table_student_grades_default` ON
    (_table_student_grades_default.entity_id = e.entity_id) AND
    (_table_student_grades_default.attribute_id='177') AND
    _table_student_grades_default.store_id=0
LEFT JOIN `catalog_product_entity_varchar` AS `_table_student_grades` ON
    (_table_student_grades.entity_id = e.entity_id) AND
    (_table_student_grades.attribute_id='177') AND
    (_table_student_grades.store_id='264830')
INNER JOIN `catalog_category_product_index` AS `cat_index` ON
    cat_index.product_id=e.entity_id AND
    cat_index.store_id='264830' AND cat_index.category_id='312798' AND
    cat_index.is_parent=1 
WHERE ((e.type_id = 'simple')) AND
    (
    (IF(_table_student_grades.value_id>0, _table_student_grades.value, _table_student_grades_default.value) = '') 
    OR 
    (IF(_table_student_grades.value_id>0, _table_student_grades.value, _table_student_grades_default.value) = '68,69')
    )

THE PROBLEM what is happening is the INNER JOIN is excluding products that don't have this attribute at all - and I don't want to add this attribute globally and unnecessarily fill the tables - that's what EAV is supposed to be all about.

How do I force a left join for this attribute?

4

In EAV models like the product, addFieldToFilter is just there for compatibility with the collection interface, it calls addAttributeToFilter which has an additional third parameter $joinType that defaults to "inner".

public function addAttributeToFilter($attribute, $condition = null, $joinType = 'inner')

So instead of addFieldToFilter($conditions), you can use addAttributeToFilter($conditions, null, 'left') to force a left join.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.