Magento 2 add Sort By Best Sellers Option on category products litsing page

Question

I am trying to add new custom sort by option on categories products listing page along with other existing options like Name , Position and Price.

To test my functionality i am directory doing changes to Magento core files Just for testing purpose , so that once it will work , i will create a separate custom module for that.

I have modified

vendor/magento/module-catalog/Block/Product/ListProduct.php

and i have added

$collection->getSelect()->joinLeft( array("test" => 'sales_order_item'), 'main_table.entity_id = test.product_id', array('qty_ordered'=>'SUM(test.qty_ordered)')) ->group('main_table.entity_id') ->order('qty_ordered ASC');

Just after this line.

$collection = $this->_getProductCollection();

In below function

protected function _beforeToHtml()

But i am getting error as

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'main_table.entity_id' in 'on clause'

Can anybody please help , where i am wrong and is there any better method to complimenting sort by best sellers option in Magento 2.

Thanks

Answer 1

Here is an easy clean way to achieve this without overriding Magento core. Create NameSpace\ModuleName\etc\frontend\di.xml file with current configuration:

<!-- Adding Sort by Bestsellers functionality -->
<type name="Magento\Catalog\Block\Product\ProductList\Toolbar">
    <plugin name="before_set_collection" type="NameSpace\ModuleName\Plugin\Catalog\Product\Sorting\Bestsellers" />
</type>
<type name="Magento\Catalog\Model\Config">
    <plugin name="after_get_available_orders" type="NameSpace\ModuleName\Plugin\Catalog\Product\Sorting\Bestsellers" />
</type>

Then create plugin class NameSpace\ModuleName\Plugin\Catalog\Product\Sorting\Bestsellers.php:

namespace NameSpace\ModuleName\Plugin\Catalog\Product\Sorting;

use Magento\Catalog\Block\Product\ProductList\Toolbar;
use Magento\Catalog\Model\Config;
use Magento\Framework\Data\Collection;

class Bestsellers 
{
/**
* Bestsellers sorting attribute
*/
const BESTSELLERS_SORT_BY = 'bestsellers';

/**
 * @param Config $subject
 * @param $result
 * @return array
 */
public function afterGetAttributeUsedForSortByArray(Config $subject, $result)
{
    return array_merge($result, [self::BESTSELLERS_SORT_BY => __('Bestsellers')]);
}

/**
 * @param Toolbar $subject
 * @param Collection $collection
 */
public function beforeSetCollection(Toolbar $subject, Collection $collection)
{
    if ($subject->getCurrentOrder() == self::BESTSELLERS_SORT_BY) {
        $collection->getSelect()->joinLeft(
            'sales_order_item',
            'e.entity_id = sales_order_item.product_id',
            array('qty_ordered'=>'SUM(sales_order_item.qty_ordered)'))
            ->group('e.entity_id')
            ->order('qty_ordered '.$subject->getCurrentDirection());
    }
}
}

And enjoy =)

Answer 2

Finally i have completed it like this , may be it will help someone else

First i have added these 2 lines in Company\Module\etc\frontend\di.xml to update the core blocks

<preference for="Magento\Catalog\Block\Product\ProductList\Toolbar" type="Company\Module\Block\Product\ProductList\Toolbar"/>
<preference for="Magento\Catalog\Model\Config" type="Company\Module\Model\Config"/>

Then created Config Model

Company\Module\Model\Config with below script

class Config extends \Magento\Catalog\Model\Config
{
    public function getAttributeUsedForSortByArray()
    {
       $options = ['bestseller' => __('Best seller')];
        foreach ($this->getAttributesUsedForSortBy() as $attribute) {
            /* @var $attribute \Magento\Eav\Model\Entity\Attribute\AbstractAttribute */
            $options[$attribute->getAttributeCode()] = $attribute->getStoreLabel();
        }

       return $options;
    }
}

The Created a block

Company\Module\Block\Product\ProductList\Toolbar with below script

class Toolbar extends \Magento\Catalog\Block\Product\ProductList\Toolbar
{
    public function setCollection($collection)
    {
        if($this->getCurrentOrder()=="bestseller")
        {
              $collection->getSelect()->joinLeft( 
                'sales_order_item', 
                'e.entity_id = sales_order_item.product_id', 
                array('qty_ordered'=>'SUM(sales_order_item.qty_ordered)')) 
                ->group('e.entity_id') 
                ->order('qty_ordered '.$this->getCurrentDirectionReverse());
        }

        $this->_collection = $collection;

        $this->_collection->setCurPage($this->getCurrentPage());

        $limit = (int)$this->getLimit();
        if ($limit) {
            $this->_collection->setPageSize($limit);
        }
        if ($this->getCurrentOrder()) {
            $this->_collection->setOrder($this->getCurrentOrder(), $this->getCurrentDirection());
        }
        return $this;
    }

    public function getCurrentDirectionReverse() {
            if ($this->getCurrentDirection() == 'asc') {
                return 'desc';
            } elseif ($this->getCurrentDirection() == 'desc') {
                return 'asc';
            } else {
                return $this->getCurrentDirection();
            }
        }

}

Answer 3

The drawback of using sales_order_item.qty_ordered is lifetime sales, i.e. products with high recent sales will be in bottom of page.

I suggest create new index table, with product_id, SUM(qty_ordered) during last 30 days and sort by these values. Plus add second sorting factor by adding date.

Similar solution was implemented in Intelligent Sorting Extension