Magento – Filter collection by yes/no attribute

collection-filtering

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(). ?

Best Answer

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!

Related Topic