Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Adding Best seller or most viewed option to sort by option in list page in magento
 
pradeep_kumar67
Sr. Member
 
Avatar
Total Posts:  160
Joined:  2010-06-24
Bangalore
 

Hi,

To add Best seller or most viewed option to sort by option in list page in magento

installed attached module and clear cache

if you get magento error

Can't prepare expression when tableName is instance of Zend_Db_Expr "

then follow below steps

in

app\code\core\Mage\Core\Model\Resource\Helper\Mysql4.php

in line 292 you have

throw new Zend_Db_Exception("Can’t prepare expression when tableName is instance of Zend_Db_Expr");

comment that line

grin

File Attachments
 
Magento Community Magento Community
Magento Community
Magento Community
 
pradeep_kumar67
Sr. Member
 
Avatar
Total Posts:  160
Joined:  2010-06-24
Bangalore
 

Hi,

if you get error

Cant prepare expression when tableName is instance of Zend_Db_Expr

you can follow above code or else you can also follow this steps

edit it in core file or else override

app\code\core\Mage\Catalog\Model\Resource\Product\Collection.php
to local folder

and then around line 1576(magento1.6.2 and in 1.7 around 1783) or search for this code you will get this code

$helper->prepareColumnsList($select);

comment this line

grin

File Attachments
Collection.php  (File Size: 60KB - Downloads: 159)
 
Magento Community Magento Community
Magento Community
Magento Community
 
ratnesh_dixit
Jr. Member
 
Total Posts:  6
Joined:  2012-04-15
 

Superb…

g

 
Magento Community Magento Community
Magento Community
Magento Community
 
duzenz
Jr. Member
 
Total Posts:  2
Joined:  2012-10-10
 

Is this the right way I don’t know but it saved my day.

Thanks,

 
Magento Community Magento Community
Magento Community
Magento Community
 
makotokoganicom
Jr. Member
 
Total Posts:  26
Joined:  2009-11-16
 

I get this error even after uploading your Collection.php file.  Can you help me?

There has been an error processing your request
SELECT `e`.* FROM (SELECT 1 AS `status`, `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `cat_index`.`position` AS `cat_index_position`, `e`.`name`, `e`.`short_description`, `e`.`sku`, `e`.`price`, `e`.`special_price`, `e`.`special_from_date`, `e`.`special_to_date`, `e`.`weight`, `e`.`manufacturer`, `e`.`manufacturer_value`, `e`.`small_image`, `e`.`thumbnail`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`tax_class_id`, `e`.`url_key`, `e`.`required_options`, `e`.`image_label`, `e`.`small_image_label`, `e`.`thumbnail_label`, `e`.`price_type`, `e`.`weight_type`, `e`.`price_view`, `e`.`shipment_type`, `e`.`links_purchased_separately`, `e`.`links_exist`, `e`.`tamanho`, `e`.`is_imported`, `e`.`msrp_enabled`, `e`.`msrp_display_actual_price_type`, `e`.`msrp`, `e`.`is_facebook`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, IF((IF(cisi.use_config_manage_stock = 1, 1, cisi.manage_stock) = 1), cisi.is_in_stock, 1) AS `is_saleable`, `cisi`.`is_in_stock` AS `inventory_in_stock`, `e`.`fretegratis`, SUM(order_item.qty_ordered) AS `ordered_qty` FROM `catalog_product_flat_1` 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.visibility IN(2, 4) AND cat_index.category_id=’20’ AND cat_index.is_parent=1
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = ‘1’ AND price_index.customer_group_id = 0
LEFT JOIN `cataloginventory_stock_item` AS `cisi` ON (cisi.product_id=e.entity_id)
LEFT JOIN `sales_flat_order_item` AS `order_item` ON e.entity_id = order_item.product_id GROUP BY `e`.`entity_id` ORDER BY `is_in_stock` desc, `ordered_qty` DESC) AS `e` LIMIT 32

 
Magento Community Magento Community
Magento Community
Magento Community
 
cycology77
Member
 
Avatar
Total Posts:  32
Joined:  2009-11-11
Macon, Georgia
 

I’m having a similar issue:

SELECT `e`.* FROM (SELECT 1 AS `status`, `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `cat_index`.`position` AS `cat_index_position`, `e`.`name`, `e`.`short_description`, `e`.`price`, `e`.`special_price`, `e`.`special_from_date`, `e`.`special_to_date`, `e`.`small_image`, `e`.`thumbnail`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`tax_class_id`, `e`.`url_key`, `e`.`required_options`, `e`.`price_type`, `e`.`weight_type`, `e`.`price_view`, `e`.`shipment_type`, `e`.`size`, `e`.`size_value`, `e`.`image_label`, `e`.`small_image_label`, `e`.`thumbnail_label`, `e`.`links_purchased_separately`, `e`.`film_color`, `e`.`film_color_value`, `e`.`film_width`, `e`.`film_width_value`, `e`.`callforprice`, `e`.`flavors`, `e`.`flavors_value`, `e`.`upc`, `e`.`gr_valid_through`, `e`.`film_type`, `e`.`film_type_value`, `e`.`film_length`, `e`.`film_length_value`, `e`.`film_thickness`, `e`.`film_thickness_value`, `e`.`material`, `e`.`material_value`, `e`.`usa`, `e`.`msds`, `e`.`links_exist`, `e`.`is_imported`, `e`.`msrp_enabled`, `e`.`msrp_display_actual_price_type`, `e`.`msrp`, `e`.`ship_separately`, `e`.`ship_box`, `e`.`brand`, `e`.`brand_value`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, SUM(order_item.qty_ordered) AS `ordered_qty` FROM `catalog_product_flat_1` AS `e`

 
Magento Community Magento Community
Magento Community
Magento Community
 
pradeep_kumar67
Sr. Member
 
Avatar
Total Posts:  160
Joined:  2010-06-24
Bangalore
 

please use below code which will solve this issue
here i am not using Zend exp

<?php
/**
pradeep.kumarrcs67@gmail.com

*/
class Sugarcode_Bestsellersort_Block_Product_List_Toolbar extends Mage_Catalog_Block_Product_List_Toolbar
{
   
        
public function getAvailableOrders()
    
{
        $this
->addOrderToAvailableOrders('popularity''Popularity');
        
//$this->setDefaultOrder('bestseller');
        
$this->setDefaultDirection('asc');
        return 
$this->_availableOrder;
    
}
        
    
        
public function setCollection($collection)
    
{
       $this
->_collection $collection;
       
$this->_collection->setCurPage($this->getCurrentPage());

        
// we need to set pagination only if passed value integer and more that 0
        
$limit = (int)$this->getLimit();
        if (
$limit{
            $this
->_collection->setPageSize($limit);
        
}
        $this
->_collection->getSize();
        if (
$this->getCurrentOrder()) {
            $this
->_collection->setOrder($this->getCurrentOrder(), $this->getCurrentDirection());
        
}             
        
if($this->getCurrentOrder()=='popularity'{
            $this
->_collection->getSelect()->
            
joinLeft('report_event AS _table_views',
            
' _table_views.object_id = e.entity_id AND _table_views.store_id='Mage::app()->getStore()->getId(),
            
'COUNT(_table_views.event_id) AS views')->
            
group('e.entity_id')->order('views DESC');
        
}

                 
        $limit
$this->getDefaultPerPageValue();
        
$page =  $this->getRequest()->getParam('p');

        if(isset(
$page))
            if((
$page 1) == 0)
                
$this->_collection->getSelect()->limit($limit);
            else
                
$this->_collection->getSelect()->limit($limit, ($page 1) * $limit);
        else
        
$this->_collection->getSelect()->limit($limit);
        
$this->_collection->load();

        
//$this->_collection->printlogquery(true);
        
return $this;
    
}
    

    
/**
    * Get grit products sort order field
    *
    * @return string
    */
    
public function getCurrentOrder()
    
{
        $order 
$this->_getData('_current_grid_order');
        if (
$order{
            
return $order;
        
}
        $orders 
$this->getAvailableOrders();
        
$defaultOrder $this->_orderField;
        if (!isset(
$orders[$defaultOrder])) {
            $keys 
array_keys($orders);
            
$defaultOrder $keys[0];
        
}
        $order 
$this->getRequest()->getParam($this->getOrderVarName());
        if (
$order && isset($orders[$order])) {
            
if ($order == $defaultOrder{
                Mage
::getSingleton('catalog/session')->unsSortOrder();
            
else {
                $this
->_memorizeParam('sort_order'$order);
            
}
        } 
else {
            $order 
Mage::getSingleton('catalog/session')->getSortOrder();
        
}
        
// validate session value
        
if (!$order || !isset($orders[$order])) {
            $order 
$defaultOrder;
        
}
        $this
->setData('_current_grid_order'$order);
        return 
$order;
    
}
    
/**
    * Retrieve current direction
    *
    * @return string
    */
    
public function getCurrentDirection()
    
{
        $dir 
$this->_getData('_current_grid_direction');
        if (
$dir{
            
return $dir;
        
}
        $directions 
= array('asc''desc');
        
$dir strtolower($this->getRequest()->getParam($this->getDirectionVarName()));
        if (
$dir && in_array($dir$directions)) {
            
if ($dir == $this->_direction{
                Mage
::getSingleton('catalog/session')->unsSortDirection();
                
else {
                $this
->_memorizeParam('sort_direction'$dir);
            
}
        } 
else {
            $dir 
Mage::getSingleton('catalog/session')->getSortDirection();
        
}
        
// validate direction
        
if (!$dir || !in_array($dir$directions)) {
            $dir 
$this->_direction;
        
}
        $this
->setData('_current_grid_direction'$dir);
        return 
$dir;
    
}
    
}
File Attachments
Toolbar.php  (File Size: 4KB - Downloads: 46)
 
Magento Community Magento Community
Magento Community
Magento Community
 
Amasty
Mentor
 
Avatar
Total Posts:  3988
Joined:  2009-11-10
 

Hello,

I believe our Improved Sorting extension can simplify your site sorting. It does not also adds \"sort by best sellers\” and \"most viewed\” options, it also gives an opportunity to

- sort products by newest
- sort products by the number of reviews
- by savings
- by presence in wishlists, etc.
- create product blocks using the sorting options
- show out-of-stock products at the end of the lists

The extension is fully compatible with layered navigation. You can find out more at http://amasty.com/improved-sorting.html

Image Attachments
frontend.pngblocks1.pngblocks.png
 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top