If you remove the extra semi-colon from after joinUrlRewrite then this code will work.
But be aware by calling the function joinUrlRewrite you are limiting your function so it will only work when you are not using flat category catalogue as this function is missing from the class
Mage_Catalog_Model_Resource_Category_Flat_Collection
It does however have the function addUrlRewriteToResult which would appear to give you the same results and when this is called on non flat categories it simply calls joinUrlRewrite so this would be the safer function to us.
Normal Category Collection
Mage_Catalog_Model_Resource_Category_Collection
public function addUrlRewriteToResult()
{
$this->joinUrlRewrite();
return $this;
}
Flat Category Collection
Mage_Catalog_Model_Resource_Category_Flat_Collection
public function addUrlRewriteToResult()
{
$storeId = Mage::app()->getStore()->getId();
$this->getSelect()->joinLeft(
array('url_rewrite' => $this->getTable('core/url_rewrite')),
'url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 '.
'AND url_rewrite.product_id IS NULL'.
' AND ' . $this->getConnection()->quoteInto('url_rewrite.store_id=?', $storeId).
' AND ' . $this->getConnection()->quoteInto('url_rewrite.id_path LIKE ?','category/%'),
array('request_path')
);
return $this;
}
Perhaps consider the following setup:
Applying normal discounts (20-30% off some products)
Either do this manually on each product or create a category called "Sale Items" and under that a subcategory for each discount bracket, e.g "20 percent", "30 percent", etc. You can then use catalog price rules on each of these categories to get the desired discounts.
Flash sales
Again, create a category for the flash sale and then create a catalog price rule discounting products in this category. You can set a "to" and "from" date on this price rule.
Showing sale products on the homepage
To show products that have a special price applied to them I usually create a new block called Namespace_Module_Block_Product_List_Special
(Special.php). It needs to extend Mage_Catalog_Block_Product_List
. You can then overwrite the _getProductCollection()
. Here's the whole block file:
<?php
class Namespace_Module_Block_Product_List_Special extends Mage_Catalog_Block_Product_List
{
//default item limit
protected $_defaultItemLimit = 4;
public function _construct() {
parent::_construct();
}
/**
* Retrieve product collection
*
* @return Mage_Eav_Model_Entity_Collection_Abstract
*/
protected function _getProductCollection()
{
$todayStartOfDayDate = Mage::app()->getLocale()->date()
->setTime('00:00:00')
->toString(Varien_Date::DATETIME_INTERNAL_FORMAT);
$todayEndOfDayDate = Mage::app()->getLocale()->date()
->setTime('23:59:59')
->toString(Varien_Date::DATETIME_INTERNAL_FORMAT);
$collection = Mage::getResourceModel('catalog/product_collection');
$collection->setVisibility(Mage::getSingleton('catalog/product_visibility')->getVisibleInCatalogIds());
$collection = $this->_addProductAttributesAndPrices($collection)
->addStoreFilter()
->addAttributeToFilter('special_from_date', array('date' => true, 'to' => $todayStartOfDayDate))
->addAttributeToFilter('special_to_date', array('or'=> array(
0 => array('date' => true, 'from' => $todayStartOfDayDate),
1 => array('is' => new Zend_Db_Expr('null')))
), 'left')
->addAttributeToSort('special_from_date', 'desc')
;
$itemLimit = $this->getItemLimit();
$collection->setPageSize($itemLimit);
return $collection;
}
public function getItemLimit() {
if($this->hasData('item_limit')) {
return $this->getData('item_limit');
}
return $this->_defaultItemLimit;
}
}
To use it you'll need to add this to your config.xml
under the <global>
tag:
<blocks>
<modulename>
<class>Namespace_Module_Block</class>
</modulename>
<catalog>
<rewrite>
<product_list_special>Namespace_Module_Block_Product_List_Special</product_list_special>
</rewrite>
</catalog>
</blocks>
And then display it on the homepage like so:
{{block type="catalog/product_list_special" name="homepage.products.special" template="catalog/product/list.phtml"}}
Or via layout XML:
<block type="catalog/product_list_special" name="hometabs.products.special" template="catalog/product/list.phtml"/>
You could use the same method to display the special products in each category, but that would require a change to the _getProductCollection()
method that checks for the current category. Have a look at Mage_Catalog_Block_Product_list::_getProductCollection()
for some ideas on the code to use for this.
Best Answer
Have a look on the query to fix this issue.
and check whether the ORDER BY clause is there. The code is correct from my perspective.
Works
I have a
ORDER BY
clause. Did you refresh indexes and caches? I don't think this is the problem, but might be. I have no attribute flash here, so I just tried with name and this works.