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(
    ["test" => 'sales_order_item'],
    'main_table.entity_id = test.product_id',
    ['qty_ordered'=>'SUM(test.qty_ordered)'])
    ->group('main_table.entity_id')
    ->order('qty_ordered ASC');

Just after this line. $collection = $this->_getProductCollection();

In 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

This work for me. Create your di.xml file /app/code/your_vendor\your_module/etc/di.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Catalog\Model\Config">
        <plugin name="Vendor_module::addCustomSortOptions" type="your_vendor\your_module\Plugin\Model\Config" />
    </type>
    <type name="Magento\Catalog\Block\Product\ProductList\Toolbar">
        <plugin name="Vendor_module::implementCustomSortOptions" type="your_vendor\your_module\Plugin\Product\ProductList\Toolbar" />
    </type>
</config>

create the config.php file /app/code/your_vendor/your_module/Plugin/Model

<?php
namespace your_vendor\your_module\Plugin\Model;

class Config
{
/**
 * Adding custom options and changing labels
 *
 * @param \Magento\Catalog\Model\Config $catalogConfig
 * @param [] $options
 * @return []
 */
public function afterGetAttributeUsedForSortByArray(\Magento\Catalog\Model\Config $catalogConfig, $options)
{
    //New sorting options
    $options['best_selling'] = __('Best Seller');
   

    return $options;
}
}
?>

Add the toolbar.php file inside your plugin /app/code/your_vendor/your_module/Plugin/Product/ProductList

<?php
namespace your_vendor\your_module\Plugin\Product\ProductList;

class Toolbar
{
/**
* Plugin
*
* @param \Magento\Catalog\Block\Product\ProductList\Toolbar $subject
* @param \Closure $proceed
* @param \Magento\Framework\Data\Collection $collection
* @return \Magento\Catalog\Block\Product\ProductList\Toolbar
*/
public function aroundSetCollection(
    \Magento\Catalog\Block\Product\ProductList\Toolbar $toolbar,
    \Closure $proceed,
    $collection
) {
    $this->_collection = $collection;
    $currentOrder = $toolbar->getCurrentOrder();
    $currentDirection = $toolbar->getCurrentDirection();
    $result = $proceed($collection);

    if ($currentOrder) {
        switch ($currentOrder) {
        case 'best_selling':
              $this->_collection->getSelect()->joinLeft(
                'sales_order_item',
                'e.entity_id = sales_order_item.product_id',
                array('qty_ordered'=>'(select sum(soi.qty_ordered) from sales_order_item soi where soi.product_id = e.entity_id)'))->distinct(true)
                ->order('qty_ordered '.$currentDirection);
                //echo $subject->getCollection()->getSelect();die;
                break;


        default:        
            $this->_collection
                ->setOrder($currentOrder, $currentDirection);
        break;

        }
    }
    //var_dump((string) $this->_collection->getSelect()); You can use this to get a list of all the available sort fields
    return $result;
}
}
?>

Answer 5

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.