Product collection based on "Set Product as New from Date"

Question

I want product collection based on "Set Product as New from Date".I have specify date for filter this. Below is code :

$todayDate  = Mage::app()->getLocale()->date()->toString(Varien_Date::DATETIME_INTERNAL_FORMAT);
$storeId    = Mage::app()->getStore()->getId();
$products = Mage::getModel('catalog/product_collection')
        ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
        ->addMinimalPrice()
        ->addUrlRewrite()
        ->addTaxPercents()
        ->addStoreFilter()
        ->addAttributeToFilter('news_from_date', array('date'=>true, 'to'=> $todayDate))
        ->addAttributeToFilter(array(array('attribute'=>'news_to_date', 'date'=>true, 'from'=>$todayDate), array('attribute'=>'news_to_date', 'is' => new Zend_Db_Expr('null'))),'','left')
        ->addAttributeToSort('news_from_date','desc');      
    Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($products);
    Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($products);

    $products->setPageSize($this->getConfig('qty'))->setCurPage(1);
    $this->setProductCollection($products);

Now issue : query is filtering a table "catalog_product_flat_2" but date (Set Product as New from Date) I set for product is saved in "catalog_product_entity_datetime" hence I am not gettnig proper data.

Answer 1

Try this code

$products= Mage::getModel('catalog/product_collection')
           ->clear()
           ->addAttributeToSort('created_at', 'DESC');

Answer 2

Please disable flat catalog for products from configuration and try again. You can do the same from System > Configuration then under Catalog, click Catalog. You can find Frontend section on right.

From Yes, set to No for products.

Answer 3

When you are using flat tables in that case you need to enable Used in Product Listing to Yes from product attributes frontend properties. After that only it will appear in flat table.