10

I am building a product collection, which will find results from a multiselect attribute.

(relevant code that adds finset to collection - adjusted to display real id values)

$attribute = Mage::getModel('eav/config')->getAttribute('catalog_product', 'measurement');
    $value = array('finset' => array('237',
                '236',
                '235',
                '234',
                '233',));
    $collection->addAttributeToFilter($attribute, $value);

The resulting sql (with visiblity filter added) is as such:

SELECT DISTINCT
    e . *,
    at_measurement.value AS measurement,
    at_visibility.value AS visibility
FROM
    catalog_product_entity AS e
        INNER JOIN
    catalog_product_entity_varchar AS at_measurement ON (at_measurement.entity_id = e.entity_id) AND (at_measurement.attribute_id = '983') AND (at_measurement.store_id = 0)
        INNER JOIN
    catalog_product_entity_int AS at_visibility ON (at_visibility.entity_id = e.entity_id) AND (at_visibility.attribute_id = '526') AND (at_visibility.store_id = 0)
WHERE
    (e.attribute_set_id IN ('74')) AND (FIND_IN_SET('237',
            '236',
            '235',
            '234',
            '233',
            at_measurement.value)) AND (at_visibility.value IN ('2' , '4'))
GROUP BY e.entity_id

The issue is that I get a sql error:

"SQLSTATE[42000]: Syntax error or access violation: 1582 Incorrect parameter count in the call to native function 'FIND_IN_SET'"

and I (think) I can see why: Some of the multiselect values have only one option, thus there is no comma separated values to qualify for FINSET

Am I correct in why this error is produced? How can I write this collection object to account for this?

If not the above, what am I missing?

The results from running the sql in mysql workbench, minus the find_in_set clause:

results checked in query editor

1

1 Answer 1

31

Try to use addAttributeToFilter with or condition

$collection->addAttributeToFilter($attribute,
    array(
        array('finset'=> array('237')),
        array('finset'=> array('238')),
        array('finset'=> array('239')),
    )
);

Or

$collection->addAttributeToFilter(
    array(
        array('attribute'=> 'attributecode','finset' => array('237')),
        array('attribute'=> 'attributecode','finset' => array('237')),
        array('attribute'=> 'attributecode','finset' => array('237')),
    )
);
3
  • 1
    How can we do NOT FIND_IN_SET ? Jun 20, 2014 at 12:56
  • How to use 'AND' condition Apr 12, 2016 at 7:12
  • 1
    For "AND", just repeat the $collection->addAttributeToFilter() for each value
    – Wouter
    Jan 31, 2019 at 12:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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