4

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

5

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

}
  • its works fine but, its removed position sort by option. – Rajkumar .E Apr 11 '17 at 13:34
  • You don't need to create config.php just create a new attribute and set yes for used in sorting at product listing. It will not remove position sort by option. – Ramkishan Suthar Oct 20 '17 at 15:06
  • @Dinesh Saini , I have overrided toolbar block like you said / suggested but sort by option is missing in search page . apart from this anything still missing? – Pramod Kharade Dec 7 '17 at 9:13
  • Hi, I am getting error "Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'qty_ordered ' in 'order clause'" while using this code. Please help with the solution. – Udit Gupta Jul 5 '18 at 9:19
2

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

0

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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.