Filter collection by yes/no attribute

Question

I'm using the following to filter my collection:

                $collection = Mage::getModel('catalog/product')
                ->getCollection()
                ->setStoreId($storeId)
                ->addAttributeToSelect('sepdate')
                ->addStoreFilter($storeId)
                ->addAttributeToFilter('status', array('eq' => 1))
                ->addAttributeToFilter('sepdate', array('eq' => 'Yes'))
                ->getAllIds();

The attribute 'sepdate' is a yes/no attribute and it is added to the product list eav.

My problem is that the above doesn't work. When removing the:

->addAttributeToFilter('sepdate', array('eq' => 'Yes'))

The collection works fine, but when that is added nothing gets selected.

I have checked that there actually are products with the attribute set for 'Yes' and I have also tested with 1 instead of 'Yes'.

Can anyone help me with what i'm doing wrong here?

EDIT:

Okay, looked at the sql it made and think I found the culprit. The sql looks like this:

SELECT `e`.*, `at_sepdate`.`value` AS `sepdate` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_entity_int` AS `at_sepdate` ON (`at_sepdate`.`entity_id` = `e`.`entity_id`) AND (`at_sepdate`.`attribute_id` = '144') AND (`at_sepdate`.`store_id` = 0) INNER JOIN `catalog_product_website` AS `product_website` ON product_website.product_id = e.entity_id AND product_website.website_id = '1' WHERE (at_sepdate.value = 1)

Where this is causing the trouble:

AND (`at_sepdate`.`store_id` = 0)

The store_id should be 1 and I can't quite understand why it isn't since i'm setting both setStoreID() and addStoreFilter(). ?

Answer 1

The culprit is probably the ->addStoreFilter($storeId) because it adds the website_id as a filtering condition, instead of the store_id. See Mage_Catalog_Model_Resource_Product_Collection->addStoreFilter() and trace through ->_applyProductLimitations() and finally ->_productLimitationJoinWebsite() where it decides to join the website around line 1652 (in version 1.8):

$joinWebsite = true;
$websiteId = Mage::app()->getStore($filters['store_id'])->getWebsiteId();
$conditions[] = $this->getConnection()
    ->quoteInto('product_website.website_id = ?', $websiteId);

I encountered this problem when trying to use addStoreFilter() on a Resource Model Product Collection instead of the full Model. My solution was to change the store before instantiating the collection.
Try:

Mage::app()->setCurrentStore($storeId);
$collection = Mage::getModel('catalog/product')
                ->getCollection()
                ->addAttributeToSelect('sepdate')  /* I think this line is not necessary because the ->addAttributeToFilter('sepdate', 1) will add the attribute as well. */
                ->addAttributeToFilter('status', array('eq' => 1))
                ->addAttributeToFilter('sepdate', array('eq' => 'Yes'))
                ->getAllIds();

I hope this helps. Good luck!

Answer 2

You might try something like this...

$collection->addFieldToFilter(array(
   array('attribute'=>'sepdate','eq'=>'yes'),
));

Sometimes addFieldToFilter works when addattributetofilter is not working as you expect it to.

Answer 3

Try this code.

        $productModel=Mage::getModel('catalog/product');

        $attr = $productModel->getResource()->getAttribute('sepdate');
            if ($attr) {

         $filter_id = $attr->getSource()->getOptionId('yes');

             }
            $collection = Mage::getModel('catalog/product')
            ->getCollection()
            ->setStoreId($storeId)
            ->addAttributeToSelect('sepdate')
            ->addStoreFilter($storeId)
            ->addAttributeToFilter('status', array('eq' => 1))
            ->addAttributeToFilter('sepdate',$filter_id)
            ->getAllIds();

Answer 4

You Can use this code

$productAttrCode = 'show_on_home';
$productAttrValue = 'yes';
$products = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToSelect('*')
    ->addFieldToFilter(
        $productAttrCode,
        array(
            'eq' => Mage::getResourceModel('catalog/product')
                ->getAttribute($productAttrCode)
                ->getSource()
                ->getOptionId($productAttrValue)
        )
    );

foreach ($products as $product) {
    echo $product->getName() . "<br>";
}