title: Magento - Create a SALE product collection taking into account special_price & promo price rules
body: <p>I need a product collection consisting of products where the special_price is current and/or they match any promotional price rule.  I'm doing something similar elsewhere in my site at the moment using getFinalPrice() but I can't add that as a filter, can I? </p>
  
  <p>I've got two separate blocks of code that individually more or less do what I want and I guess they should fit together somehow:</p>
  
  <pre><code>$rule = Mage::getModel('catalogrule/rule')-&gt;load(9);
  $rule-&gt;setWebsiteIds("1"); 
  $productIdsArray = $rule-&gt;getMatchingProductIds(); 
  $collection = Mage::getModel('catalog/product')-&gt;getCollection()-&gt;addAttributeToSelect("*")-&gt;addAttributeToFilter("entity_id", array("in", $productIdsArray));
  </code></pre>
  
  <p>and</p>
  
  <pre><code>$todayDate = date('m/d/y');
  $tomorrow = mktime(0, 0, 0, date('m'), date('d')+1, date('y'));
  $tomorrowDate = date('m/d/y', $tomorrow);
  
  $collection = Mage::getResourceModel('catalogsearch/advanced_collection')
      -&gt;addAttributeToSelect(Mage::getSingleton('catalog/config')-&gt;getProductAttributes())
      -&gt;addMinimalPrice()
      -&gt;addStoreFilter();
  
  Mage::getSingleton('catalog/product_status')-&gt;addVisibleFilterToCollection($collection);
  Mage::getSingleton('catalog/product_visibility')-&gt;addVisibleInSearchFilterToCollection($collection);
  
  $collection-&gt;addAttributeToFilter('special_from_date', array('date' =&gt; true, 'to' =&gt; $todayDate))
      -&gt;addAttributeToFilter('special_to_date', array('or'=&gt; array(
      0 =&gt; array('date' =&gt; true, 'from' =&gt; $tomorrowDate),
      1 =&gt; array('is' =&gt; new Zend_Db_Expr('null')))
      ), 'left');
  </code></pre>
  
  <p>The code filtering promo rules requires a promo rule id but I'm really interested in ALL promo rules rather than one specific one.</p>
  
  <p>Can anyone help me fit these together into one please?</p>
  
  <p>Thanks,</p>
  
  <p>~gpcola</p>
  
  <p>Update 10/11/11:</p>
  
  <p>Ok so here is what I've ended up with:</p>
  
  <pre><code>// get catalogue price rule promotion product collection  
  $storeId = Mage::app()-&gt;getStore()-&gt;getId();
  $rules = Mage::getModel('catalogrule/rule')
                          -&gt;getResourceCollection()
                          -&gt;addWebsiteFilter(Mage::getModel('core/store')-&gt;load($storeId)-&gt;getWebsiteId());
  foreach($rules as $rule) {
      $promoids =  array_merge($promoids, $rule-&gt;getMatchingProductIds());
  }
  
  $todayDate  = Mage::app()-&gt;getLocale()-&gt;date()-&gt;toString(Varien_Date::DATETIME_INTERNAL_FORMAT);
  
  $pricecollection = Mage::getResourceModel('catalog/product_collection');
  $pricecollection-&gt;setVisibility(Mage::getSingleton('catalog/product_visibility')-&gt;getVisibleInCatalogIds());
  
  // get special price product collection  
  $todayDate = date('m/d/y');
  $tomorrow = mktime(0, 0, 0, date('m'), date('d')+1, date('y'));
  $tomorrowDate = date('m/d/y', $tomorrow);
  $pricecollection = Mage::getResourceModel('catalogsearch/advanced_collection')
                          -&gt;addAttributeToSelect(Mage::getSingleton('catalog/config')-&gt;getProductAttributes())
                          -&gt;addMinimalPrice()
                          -&gt;addStoreFilter();
  
  Mage::getSingleton('catalog/product_status')-&gt;addVisibleFilterToCollection($pricecollection);
  Mage::getSingleton('catalog/product_visibility')-&gt;addVisibleInSearchFilterToCollection($pricecollection);
  
  $pricecollection-&gt;addAttributeToFilter('special_from_date', array('date' =&gt; true, 'to' =&gt; $todayDate))
                          -&gt;addAttributeToFilter('special_to_date', array('or'=&gt; array(
                                  0 =&gt; array('date' =&gt; true, 'from' =&gt; $tomorrowDate),
                                  1 =&gt; array('is' =&gt; new Zend_Db_Expr('null')))
                                  ), 'left')
  ;
  
  // merge the collections
  $merged_ids = array_merge($pricecollection-&gt;getAllIds(), $promoids);
  $this-&gt;_productCollection = Mage::getResourceModel('catalog/product_collection')
          -&gt;addFieldToFilter('entity_id', $merged_ids)
          -&gt;setPageSize($this-&gt;getProductsCount())
          -&gt;setCurPage(1);
  
  return $this-&gt;_productCollection;
  </code></pre>
  
  <p>But this is returning no products... I have around 1000 products that match one promo price rule or another so I know that's incorrect.  Any ideas?</p>
  
  <p>oh by the way if I print_r($this->_productCollection) I get the following:</p>
  
  <pre><code>Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection Object
  </code></pre>
  
  <p>(
      [_flatEnabled:protected] => Array
          (
              [1] => 1
          )</p>
  
  <pre><code>[_productWebsiteTable:protected] =&gt; catalog_product_website
  [_productCategoryTable:protected] =&gt; catalog_category_product
  [_addUrlRewrite:protected] =&gt; 
  [_urlRewriteCategory:protected] =&gt; 
  [_addMinimalPrice:protected] =&gt; 
  [_addFinalPrice:protected] =&gt; 
  [_allIdsCache:protected] =&gt; 
  [_addTaxPercents:protected] =&gt; 
  [_productLimitationFilters:protected] =&gt; Array
      (
      )
  
  [_productCountSelect:protected] =&gt; 
  [_isWebsiteFilter:protected] =&gt; 
  [_priceDataFieldFilters:protected] =&gt; Array
      (
      )
  
  [_map:protected] =&gt; Array
      (
          [fields] =&gt; Array
              (
                  [price] =&gt; price_index.price
                  [final_price] =&gt; price_index.final_price
                  [min_price] =&gt; price_index.min_price
                  [max_price] =&gt; price_index.max_price
                  [tier_price] =&gt; price_index.tier_price
                  [special_price] =&gt; price_index.special_price
              )
  
      )
  
  [_storeId:protected] =&gt; 1
  [_itemsById:protected] =&gt; Array
      (
      )
  
  [_staticFields:protected] =&gt; Array
      (
          [entity_id] =&gt; entity_id
          [type_id] =&gt; type_id
          [attribute_set_id] =&gt; attribute_set_id
      )
  
  [_entity:protected] =&gt; Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Flat Object
      (
          [_storeId:protected] =&gt; 1
          [_resources:protected] =&gt; Mage_Core_Model_Resource Object
              (
                  [_connectionTypes:protected] =&gt; Array
                      (
                          [pdo_mysql] =&gt; Mage_Core_Model_Resource_Type_Db_Pdo_Mysql Object
                              (
                                  [_name:protected] =&gt; 
                                  [_entityClass:protected] =&gt; Mage_Core_Model_Resource_Entity_Table
                              )
  
                      )
  
                  [_connections:protected] =&gt; Array
                      (
                          [core_read] =&gt; Varien_Db_Adapter_Pdo_Mysql Object
                              (
                                  [_transactionLevel:protected] =&gt; 0
                                  [_connectionFlagsSet:protected] =&gt; 1
                                  [_ddlCache:protected] =&gt; Array
                                      (
                                          [1] =&gt; Array
                                              (
  ...
  </code></pre>
  
  <p>Does that look right?</p>