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

i hope you we get pagination issue due to getsize count wrong. whenever you use group by query then call getSize function means it will give first record data so it will give wrong count. for this before embed group by query. you should call getSize function. so it will set before execute group by query.

code should like this

if($this->getCurrentOrder()=="bestseller")
        {
getSize()//called before group by function.
              $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());
        }

Answer 4

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.