1

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

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

0

2 Answers 2

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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