Magento2.4: How to Sort Product by " Best Selling " and " Best reviewed " Product?

Question

I want to add custom sort options like " Best Selling " and " Best reviewed " in Catalog/Category sort order options Below is the picture.

and i am trying this approach in below code but its not working.

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\VisualMerchandiser\Model\Sorting">
        <plugin name="Vendor_Module_Plugin_VisualMerchandiser_Model_SortingPlugin"
                type="Vendor\Module\Plugin\VisualMerchandiser\Model\SortingPlugin" sortOrder="10"/>
    </type>
</config>

Vendor\Module\Plugin\VisualMerchandiser\Model\SortingPlugin.php

<?php
declare(strict_types=1);

namespace Vendor\Module\Plugin\VisualMerchandiser\Model;

use Vendor\Module\Model\VisualMerchandiser\Sorting\DateBottom;
use Vendor\Module\Model\VisualMerchandiser\Sorting\DateTop;
use Vendor\Module\Model\VisualMerchandiser\Sorting\BestSelling;
use Vendor\Module\Model\VisualMerchandiser\Sorting\MostPopular;
use Vendor\Module\Model\VisualMerchandiser\Sorting\DiscountTop;

use Magento\VisualMerchandiser\Model\Sorting;
use Magento\VisualMerchandiser\Model\Sorting\SortInterface;

class SortingPlugin
{
    /**
     * @var SortInterface[]
     */
    protected array $sortingOptions = [];

    /**
     * @param DateBottom $dateBottom
     * @param DateTop $dateTop
     * @param BestSelling $bestSelling
     * @param MostPopular $mostPopular
     * @param DiscountTop $discountTop
     */
    public function __construct(
        DateBottom $dateBottom,
        DateTop $dateTop,
        BestSelling $bestSelling,
        MostPopular $mostPopular,
        DiscountTop $discountTop
    ) {
        $this->sortingOptions[20] = $dateBottom;
        $this->sortingOptions[21] = $dateTop;
        $this->sortingOptions[22] = $bestSelling;
        $this->sortingOptions[23]= $mostPopular;
        $this->sortingOptions[24]= $discountTop;
    }

    /**
     * @param Sorting $subject
     * @param array $result
     * @return array
     */
    public function afterGetSortingOptions(Sorting $subject, array $result): array
    {
        foreach ($this->sortingOptions as $idx => $instance) {
            $result[$idx] = $instance->getLabel();
        }

        return $result;
    }

    /**
     * @param Sorting $subject
     * @param callable $callback
     * @param $sortOption
     * @return SortInterface
     */
    public function aroundGetSortingInstance(Sorting $subject, callable $callback, $sortOption): SortInterface
    {
        if (isset($this->sortingOptions[$sortOption])) {
            return $this->sortingOptions[$sortOption];
        }

        return $callback($sortOption);
    }
}

\Vendor\Module\Model\VisualMerchandiser\Sorting\BestSelling.php

<?php

namespace Vendor\Module\Model\VisualMerchandiser\Sorting;

use Magento\Catalog\Model\ResourceModel\Product\Collection;
use Magento\Framework\Data\Collection as CollectionAlias;
use Magento\VisualMerchandiser\Model\Sorting\SortAbstract;
use Magento\VisualMerchandiser\Model\Sorting\SortInterface;
use Zend_Db_Select;

class BestSelling extends SortAbstract implements SortInterface
{
    /**
     * @param Collection $collection
     * @return Collection
     */
    public function sort(
        Collection $collection
    ): Collection {
       $collection->getSelect()->joinLeft(
            'sales_order_item',
            'entity_id=sales_order_item.product_id',
            array('qty_ordered'=>'SUM(sales_order_item.qty_ordered)'))
            ->group('entity_id');
        $collection->getSelect()
            ->reset(Zend_Db_Select::ORDER)
            ->order('qty_ordered'.CollectionAlias::SORT_ORDER_DESC);
        return $collection;
    }

    /**
     * @return string
     */
    public function getLabel(): string
    {
        return __("Best Selling");
    }
}

\Vendor\Module\Model\VisualMerchandiser\Sorting\MostPopular.php

<?php

namespace Vendor\Module\Model\VisualMerchandiser\Sorting;

use Magento\Catalog\Model\ResourceModel\Product\Collection;
use Magento\Framework\Data\Collection as CollectionAlias;
use Magento\VisualMerchandiser\Model\Sorting\SortAbstract;
use Magento\VisualMerchandiser\Model\Sorting\SortInterface;
use Zend_Db_Select;

class MostPopular extends SortAbstract implements SortInterface
{

    /**
     * @param Collection $collection
     * @return Collection
     */
    public function sort(
        Collection $collection
    ): Collection {
        $collection->getSelect()->joinLeft(
            ['rating_vote'=>$collection->getTable('rating_option_vote')],
            'rating_vote.review_id=main_table.review_id',
           ['sum'=>'SUM(percent)','count'=>'COUNT(*)','average'=>'SUM(percent)']);
        $collection->getSelect()
            ->reset(Zend_Db_Select::ORDER)
            ->group('rating_vote.review_id')
            ->order('average'.CollectionAlias::SORT_ORDER_DESC);
        return $collection;
    }
        /**
         * @return string
         */
        public function getLabel(): string
        {
            return __("Most Populars");
        }
}

but the above logic doest sort grid in picture above, any idea what am i doing wrong? Thanks in advance

Answer 1

I don't have time to write full example and test, but play around with following logic:

Bestsellers

    // ...
    public function sort(\Magento\Catalog\Model\ResourceModel\Product\Collection $collection)
    {
        $connection = $collection->getConnection();
        $select = $connection->select()
            ->from($collection->getTable('sales_bestsellers_aggregated_yearly'), [
                'qty_ordered' => new \Zend_Db_Expr('SUM(qty_ordered)'),
                'product_id'
            ])
            ->where('store_id = 0')
            ->group('product_id');

        $collection->getSelect()
            ->joinLeft(
                ['bs' => $select],
                'bs.product_id = e.entity_id'
            )
            ->reset(\Zend_Db_Select::ORDER)
            ->order('bs.qty_ordered ' . \Zend_Db_Select::SQL_DESC);
    }
    // ...

Top Rated

    // ...
    public function sort(\Magento\Catalog\Model\ResourceModel\Product\Collection $collection)
    {
        $connection = $collection->getConnection();
        $select = $connection->select()
            ->from($collection->getTable('rating_option_vote_aggregated'), [
                'percent_approved' => new \Zend_Db_Expr('AVG(percent_approved)'),
                'product_id' => 'entity_pk_value'
            ])
            ->where('store_id > 0')
            ->group('entity_pk_value');

        $collection->getSelect()
            ->joinLeft(
                ['tr' => $select],
                'tr.product_id = e.entity_id'
            )
            ->reset(\Zend_Db_Select::ORDER)
            ->order('tr.percent_approved ' . \Zend_Db_Select::SQL_DESC);
    }
    // ...

Answer 2

Create NameSpace\ModuleName\etc\frontend\di.xml file with current configuration:

<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:

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