Add WHERE with IN and OR to a product collection

Question

I want to add something like WHERE (table1.attrib1 IN (1,2,3) OR table2.attrib2 IN (1,2,3)) to a product collection to which I have already joined a couple of tables.

I tried this, but it doesn't work because it took '1,2,3' as one string (that is, did not put them in brackets after IN). How can I use WHERE?

$productCollection->getSelect()->join(array('table1' => $table1), 'e.entity_id = table1.entity_id', array('my_attribute1' => 'table1.value'))->join(array('table2' => $table2), 'e.entity_id = table2.entity_id', array('my_attribute2' => 'table2.value'))->where('table1.attrib1 IN ? OR table2.attrib2 IN ? ', '1,2,3')

Answer 1

Just try it this way

// ...
->where('table1.attrib1 IN (?) OR table2.attrib2 IN (?) ', array(1,2,3));

Wrap the placeholders in parenthesis (?) and provide the values as an array array(1,2,3) instead of the former string.

Answer 2

How to use joinTable is described here: https://magento.stackexchange.com/a/13267/217

and in more details here: http://blog.fabian-blechschmidt.de/joining-a-flat-table-on-eav/

To use in in a filter you can use:

->addAttributeToFilter('attrib', array('in' => array(1,2,3))

and to use or you have to use the arrays recursive, like this:

$in = array('in' => array(1,2,3));
$in2 = array('in' => array(4,5,6));
->addAttributeToFilter('attrib', array($in, $in2)

should create:

WHERE attrib IN (1,2,3) OR attrib IN (4,5,6);

Answer 3

Here the solution ,if want to filter a collection by it field then use addFieldTofilter() function ,which is applicable for non-eav collection and eav collection.

if you filter a collection by in then used below logic and code

$collection->addFieldTofilter('field_name', array(
    'in' => array($val1, $val2, $val3),
    ));

If you filter product collection then used addAttribuetToFilter('')

for a exmaple

$collection=Mage::getModel('catalog/product')->getCollection();
$collection->addAttributeToFilter('field_name', array(
    'in' => array(1, 2, 3),
    ));

More details on http://www.magentocommerce.com/wiki/1_-_installation_and_configuration/using_collections_in_magento