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

magento-2.0magento-2.1

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

Best Answer

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 =)

Related Topic