How to load products from a specific category based on special price availability?

Question

I have following code

$promoCatId = (int)Mage::getStoreConfig('promoprodsec/promoprodgroup/promocategoryid');

$products = Mage::getModel('catalog/category')->setStoreId($store)->load($promoCatId)
    ->getProductCollection();

$allProductsInPromoCategory = $products->getAllIds();

It gives me all the products of $promoCatId category.

Now I want to get all the products which are not having special price or the special price date range has expired from all the products of $allProductsInPromoCategory

How can I accomplish this without using foreach loop and again loading every product collection again?

foreach ($allProductsInPromoCategory as $promoprod) {
    Mage:getModel('catalog/product')->load( $promoprod);

    //do validation for the special price criteria
}

score 2 · Accepted Answer · answered Jan 08 '16 at 21:19

Once you state ->getProductCollection() on your category, you end up with a object of type Mage_Catalog_Model_Resource_Product_Collection, which is basically a product collection like all the other.

Since this collection is an EAV collection, you can filter it with the help of the function addAttributeToFilter

And since you actually want an OR (where special price is null or special to date <= now()), you want your two conditions to be in the same addAttributeToFilter

So I would say you can achieve this by doing :

$promoCatId  = (int)Mage::getStoreConfig('promoprodsec/promoprodgroup/promocategoryid');

$products = Mage::getModel('catalog/category')
                ->setStoreId($store)
                ->load(promoCatId)
                ->getProductCollection()
                ->addAttributeToFilter(
                    array(
                        array('attribute' => 'special_price', 'null' => true),
                        array('attribute' => 'special_from_date', 'lt' => Mage::getModel('core/date')->date(Varien_Date::DATETIME_PHP_FORMAT))
                    )
                );

$allProductsInPromoCategory = $products->getAllIds();

echo $products->getSelect();

Useful to know :

I am actually using Mage::getModel('core/date')->date(Varien_Date::DATETIME_PHP_FORMAT) to get the current date relative to the configured locale timezone of the store
echo $products->getSelect(); on a collection will display you the query done by Magento, really useful to actually see if the where is what you really want it to be. In my case it displays :

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `at_special_price`.`value` AS `special_price`, IF(at_special_from_date.value_id > 0, at_special_from_date.value, at_special_from_date_default.value) AS `special_from_date` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.category_id = '15' INNER JOIN `catalog_product_entity_decimal` AS `at_special_price` ON (`at_special_price`.`entity_id` = `e`.`entity_id`) AND (`at_special_price`.`attribute_id` = '76') AND (`at_special_price`.`store_id` = 0) INNER JOIN `catalog_product_entity_datetime` AS `at_special_from_date_default` ON (`at_special_from_date_default`.`entity_id` = `e`.`entity_id`) AND (`at_special_from_date_default`.`attribute_id` = '77') AND `at_special_from_date_default`.`store_id` = 0 LEFT JOIN `catalog_product_entity_datetime` AS `at_special_from_date` ON (`at_special_from_date`.`entity_id` = `e`.`entity_id`) AND (`at_special_from_date`.`attribute_id` = '77') AND (`at_special_from_date`.`store_id` = 1) WHERE ((at_special_price.value IS NULL) OR (IF(at_special_from_date.value_id > 0, at_special_from_date.value, at_special_from_date_default.value) < '2016-01-08 22:05:14'))

thanks for your reply, It gave me the idea to correct my approach, though I used a different way. — Mukesh, Jan 13 '16 at 06:46

How to load products from a specific category based on special price availability?

1 Answers1

Useful to know :