Mrthdrb

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

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

First i have added these 2 lines in CompanyModuleetcfrontenddi.xml to update the core blocks

<preference for="MagentoCatalogBlockProductProductListToolbar" type="CompanyModuleBlockProductProductListToolbar"/>
<preference for="MagentoCatalogModelConfig" type="CompanyModuleModelConfig"/>

Then created Config Model

CompanyModuleModelConfig with below script

class Config extends MagentoCatalogModelConfig

 public function getAttributeUsedForSortByArray()
 
 $options = ['bestseller' => __('Best seller')];
 foreach ($this->getAttributesUsedForSortBy() as $attribute) 
 /* @var $attribute MagentoEavModelEntityAttributeAbstractAttribute */
 $options[$attribute->getAttributeCode()] = $attribute->getStoreLabel();
 

 return $options;
 

The Created a block

CompanyModuleBlockProductProductListToolbar with below script

class Toolbar extends MagentoCatalogBlockProductProductListToolbar

 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();
 
 

Here is an easy clean way to achieve this without overriding Magento core.
Create NameSpaceModuleNameetcfrontenddi.xml file with current configuration:

<!-- Adding Sort by Bestsellers functionality -->
<type name="MagentoCatalogBlockProductProductListToolbar">
 <plugin name="before_set_collection" type="NameSpaceModuleNamePluginCatalogProductSortingBestsellers" />
</type>
<type name="MagentoCatalogModelConfig">
 <plugin name="after_get_available_orders" type="NameSpaceModuleNamePluginCatalogProductSortingBestsellers" />
</type>

Then create plugin class NameSpaceModuleNamePluginCatalogProductSortingBestsellers.php:

namespace NameSpaceModuleNamePluginCatalogProductSorting;

use MagentoCatalogBlockProductProductListToolbar;
use MagentoCatalogModelConfig;
use MagentoFrameworkDataCollection;

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

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