9

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

23

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')),
    )
);
  • 1
    How can we do NOT FIND_IN_SET ? – Slimshadddyyy Jun 20 '14 at 12:56
  • How to use 'AND' condition – Deeban Babu Apr 12 '16 at 7:12
  • 1
    For "AND", just repeat the $collection->addAttributeToFilter() for each value – Wouter Jan 31 at 12:42

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.