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-&gt;sortingOptions[20] = $dateBottom;
    $this-&gt;sortingOptions[21] = $dateTop;
    $this-&gt;sortingOptions[22] = $bestSelling;
    $this-&gt;sortingOptions[23]= $mostPopular;
    $this-&gt;sortingOptions[24]= $discountTop;
}

/**
 * @param Sorting $subject
 * @param array $result
 * @return array
 */
public function afterGetSortingOptions(Sorting $subject, array $result): array
{
    foreach ($this-&gt;sortingOptions as $idx =&gt; $instance) {
        $result[$idx] = $instance-&gt;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-&gt;sortingOptions[$sortOption])) {
        return $this-&gt;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 __(&quot;Best Selling&quot;);
}

}

\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-&gt;getSelect()-&gt;joinLeft(
        ['rating_vote'=&gt;$collection-&gt;getTable('rating_option_vote')],
        'rating_vote.review_id=main_table.review_id',
       ['sum'=&gt;'SUM(percent)','count'=&gt;'COUNT(*)','average'=&gt;'SUM(percent)']);
    $collection-&gt;getSelect()
        -&gt;reset(Zend_Db_Select::ORDER)
        -&gt;group('rating_vote.review_id')
        -&gt;order('average'.CollectionAlias::SORT_ORDER_DESC);
    return $collection;
}
    /**
     * @return string
     */
    public function getLabel(): string
    {
        return __(&quot;Most Populars&quot;);
    }

}

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

score 3 · Accepted Answer · answered Jul 20 '22 at 16:56

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-&gt;getSelect()
        -&gt;joinLeft(
            ['bs' =&gt; $select],
            'bs.product_id = e.entity_id'
        )
        -&gt;reset(\Zend_Db_Select::ORDER)
        -&gt;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-&gt;getSelect()
        -&gt;joinLeft(
            ['tr' =&gt; $select],
            'tr.product_id = e.entity_id'
        )
        -&gt;reset(\Zend_Db_Select::ORDER)
        -&gt;order('tr.percent_approved ' . \Zend_Db_Select::SQL_DESC);
}
// ...

Can you take a look, no one is answering https://magento.stackexchange.com/questions/358076/how-to-add-sort-options-in-back-end-grid-as-shown-in-pictures — Afzal Arshad, Jul 23 '22 at 18:01

score 2 · Answer 2 · answered Jul 21 '22 at 07:16

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 =&gt; __('Bestsellers')]);
}

/**
 * @param Toolbar $subject
 * @param Collection $collection
 */
public function beforeSetCollection(Toolbar $subject, Collection $collection)
{
    if ($subject-&gt;getCurrentOrder() == self::BESTSELLERS_SORT_BY) {
        $collection-&gt;getSelect()-&gt;joinLeft(
            'sales_order_item',
            'e.entity_id = sales_order_item.product_id',
            array('qty_ordered'=&gt;'SUM(sales_order_item.qty_ordered)'))
            -&gt;group('e.entity_id')
            -&gt;order('qty_ordered '.$subject-&gt;getCurrentDirection());
    }
}

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

2 Answers2

Linked