How to add Sort options in back-end grid as shown in pictures?

Question

I want to add extra sort options in backend these options are below

1. All Sizes on top
2. Minimum 1 sizes on top
3. Top Discounted Product (discount percent)
4. Recently bought on top

and i have used this code to add other sort options here is my code.

etc/di.xml

<type name="Magento\VisualMerchandiser\Model\Sorting">
        <plugin name="Vendor_Module_VisualMerchandiser_Plugin_VisualMerchandiser_Model_SortingPlugin"
                type="Vendor\Module\Plugin\VisualMerchandiser\Model\SortingPlugin" sortOrder="10"/>
    </type>

\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\DiscountBottom;
use Vendor\Module\Model\VisualMerchandiser\Sorting\DiscountTop;
use Vendor\Module\Model\VisualMerchandiser\Sorting\LowStockBottom;
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 DiscountBottom $discountTop
 * @param LowStockBottom $lowStockBottom
 */
public function __construct(
    DateBottom $dateBottom,
    DateTop $dateTop,
    BestSelling $bestSelling,
    MostPopular $mostPopular,
    DiscountBottom $discountBottom,
    DiscountTop $discountTop,
    LowStockBottom $lowStockBottom
) {
    $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;
    $this-&gt;sortingOptions[25]= $discountBottom;

    $this-&gt;sortingOptions[26]= $lowStockBottom;
}

/**
 * @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\LowStockBottom.php

<?php
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
namespace Vendor\Module\Model\VisualMerchandiser\Sorting;
use Magento\Framework\DB\Select;
use Magento\Catalog\Model\ResourceModel\Product\Collection;
use Magento\Framework\Data\Collection as CollectionAlias;
use Magento\Framework\Exception\LocalizedException;
use Magento\VisualMerchandiser\Model\Sorting\SortAbstract;
use Magento\VisualMerchandiser\Model\Sorting\SortInterface;
use Zend_Db_Select;
/**

Rearrange product positions in category grid/tile view based on the stock ascending order

*/
class LowStockBottom extends SortAbstract implements SortInterface
{
    const XML_PATH_MIN_STOCK_THRESHOLD = 'visualmerchandiser/options/minimum_stock_threshold';
/**
 * Sort low stock on top for products in category
 *
 * @param Collection $collection
 * @return Collection
 * @throws LocalizedException
 */
public function sort(
    Collection $collection
): Collection {
    if (!$this-&gt;moduleManager-&gt;isEnabled('Magento_CatalogInventory')) {
        return $collection;
    }

    $minStockThreshold = (int)$this-&gt;scopeConfig-&gt;getValue(self::XML_PATH_MIN_STOCK_THRESHOLD);

    $baseSet = clone $collection;
    $finalSet = clone $collection;

    $collection-&gt;getSelect()
        -&gt;having('stock &lt;= ?', $minStockThreshold)
        -&gt;reset(Zend_Db_Select::ORDER)
        -&gt;order('stock ' . $collection::SORT_ORDER_DESC);

    $resultIds = [];

    $collection-&gt;load();

    foreach ($collection as $item) {
        $resultIds[] = $item-&gt;getId();
    }

    $ids = array_unique(array_merge($resultIds, $baseSet-&gt;getAllIds()));

    $finalSet-&gt;getSelect()
        -&gt;reset(Zend_Db_Select::ORDER)
        -&gt;reset(Zend_Db_Select::WHERE);

    $finalSet-&gt;addAttributeToFilter('entity_id', ['in' =&gt; $ids]);
    if (count($ids)) {
        $finalSet-&gt;getSelect()-&gt;order(new \Zend_Db_Expr('FIELD(e.entity_id, ' . implode(',', $ids) . ')'));
    }
    $finalSet-&gt;getSelect()
        -&gt;reset(Zend_Db_Select::ORDER)
        -&gt;order('stock ' . $finalSet::SORT_ORDER_DESC);
    return $finalSet;
}

/**
 * Get label for the Filter
 *
 * @return string
 */
public function getLabel(): string
{
    return __(&quot;Move low stock to Bottom&quot;);
}

}

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

<?php
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
namespace Vendor\Module\Model\VisualMerchandiser\Sorting;
use Magento\Catalog\Model\ResourceModel\Product\Collection;
use Magento\Framework\Data\Collection as CollectionAlias;
use Magento\Framework\DB\Select;
use Magento\VisualMerchandiser\Model\Sorting\SortAbstract;
use Magento\VisualMerchandiser\Model\Sorting\SortInterface;
use Zend_Db_Select;
class DateTop extends SortAbstract implements SortInterface
{
    /**
     * @param Collection $collection
     * @return Collection
     */
    public function sort(
        Collection $collection
    ): Collection {
        $this->addPriceData($collection);
        $collection->getSelect()
            ->distinct('entity_id')
            ->reset(Zend_Db_Select::ORDER)
            ->order('created_at ' . CollectionAlias::SORT_ORDER_ASC);
    return $collection;
}

/**
 * @return string
 */
public function getLabel(): string
{
    return __(&quot;Date to Top&quot;);
}

}

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

<?php
namespace Vendor\Module\Model\VisualMerchandiser\Sorting;
use Magento\Catalog\Model\ResourceModel\Product\Collection;
use Magento\VisualMerchandiser\Model\Sorting\SortAbstract;
use Magento\VisualMerchandiser\Model\Sorting\SortInterface;
use Zend_Db_Expr;
use Zend_Db_Select;
class BestSelling extends SortAbstract implements SortInterface
{
    /**
     * @param Collection $collection
     * @return Collection
     */
    public function sort(
        Collection $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);
        return $collection;
    }
/**
 * @return string
 */
public function getLabel(): string
{
    return __(&quot;Best Selling&quot;);
}

}

These are some sorting options as example what i wants to ask

• By following this approch i want to add these sort options as you can see in picture above

1. All Sizes on top
2. Minimum 1 sizes on top
3. Top Discounted Product (discount percent)
4. Recently bought on top

• How to do this any idea?

Answer 1

For implement first 2 items needs to understand Magento version, Inventory extension and usage, and also possible implement only in website scope.

Without Inventory you can play with query such

SELECT sl.parent_id, COUNT(sl.product_id), SUM(IFNULL(ss.stock_status, 0)) FROM catalog_product_super_link as sl
  INNER JOIN catalog_product_entity as p ON sl.parent_id = p.entity_id
  INNER JOIN catalog_product_entity as c ON sl.product_id = c.entity_id
  LEFT JOIN cataloginventory_stock_status as ss ON ss.website_id = 0 AND c.entity_id = ss.product_id
GROUP BY sl.parent_id;

With Inventory needs to check actual inventory stock and relation between. I will not provide full solution, but you can play with suggestion depends on your project.

3. Top Discounted

app/code/Acme/StackExchange/Model/VisualMerchandiser/Sorting/TopDiscounted.php

<?php
declare(strict_types=1);
namespace Acme\StackExchange\Model\VisualMerchandiser\Sorting;
use Magento\Catalog\Api\Data\ProductInterface;
use Magento\Catalog\Model\Indexer\Product\Price\DimensionCollectionFactory;
use Magento\Catalog\Model\Product;
use Magento\Catalog\Model\ResourceModel\Product\Collection;
use Magento\Eav\Model\Config as EavConfig;
use Magento\Framework\App\Config\ScopeConfigInterface;
use Magento\Framework\EntityManager\EntityMetadata;
use Magento\Framework\EntityManager\EntityMetadataInterface;
use Magento\Framework\EntityManager\MetadataPool;
use Magento\Framework\Indexer\DimensionFactory;
use Magento\Framework\Indexer\ScopeResolver\IndexScopeResolver;
use Magento\Framework\Locale\ResolverInterface;
use Magento\Framework\Module\Manager;
use Magento\Framework\Stdlib\DateTime\TimezoneInterface;
use Magento\VisualMerchandiser\Model\Sorting\SortAbstract;
use Magento\VisualMerchandiser\Model\Sorting\SortInterface;
class TopDiscounted extends SortAbstract implements SortInterface
{
    /**
     * @var EntityMetadata|EntityMetadataInterface
     */
    protected EntityMetadataInterface $metadata;
    protected EavConfig               $eavConfig;
    protected TimezoneInterface       $timezone;
    protected string                  $locale;
public function __construct(
    Manager $moduleManager,
    ScopeConfigInterface $scopeConfig,
    EavConfig $eavConfig,
    MetadataPool $metadataPool,
    TimezoneInterface $timezone,
    ResolverInterface $localeResolver,
    DimensionCollectionFactory $dimensionCollectionFactory = null,
    IndexScopeResolver $indexScopeResolver = null,
    DimensionFactory $dimensionFactory = null
) {
    $this-&gt;eavConfig = $eavConfig;
    $this-&gt;metadata  = $metadataPool-&gt;getMetadata(ProductInterface::class);
    $this-&gt;timezone  = $timezone;
    $this-&gt;locale    = $localeResolver-&gt;getLocale();

    parent::__construct($moduleManager, $scopeConfig, $dimensionCollectionFactory, $indexScopeResolver, $dimensionFactory);
}

/**
 * @inheritDoc
 */
public function sort(Collection $collection)
{
    $this-&gt;joinProductAttribute($collection, 'price');
    $this-&gt;joinProductAttribute($collection, 'special_price');
    $this-&gt;joinProductAttribute($collection, 'special_from_date');
    $this-&gt;joinProductAttribute($collection, 'special_to_date');

    $connection = $collection-&gt;getConnection();
    $storeDate  = $this-&gt;timezone-&gt;date(
        new \DateTime('now', new \DateTimeZone('UTC')),
        $this-&gt;locale
    )-&gt;format('Y-m-d H:i:s');

    $useSpecialExpr = new \Zend_Db_Expr(
        sprintf(
            '(t_special_from_date.value IS NULL OR t_special_from_date.value &lt; %1$s)'
            . ' AND (t_special_to_date.value IS NULL OR t_special_to_date.value &gt; %1$s)'
            . ' AND (t_special_price.value &gt; 0 AND t_special_price.value &lt; t_price.value)',
            $connection-&gt;quote($storeDate)
        )
    );

    $specialPrice = $connection-&gt;getCheckSql(
        $useSpecialExpr,
        't_special_price.value',
        't_price.value'
    );

    $discountOrderExpr = new \Zend_Db_Expr('(1 - ' . $specialPrice . ' / t_price.value) ' . \Zend_Db_Select::SQL_DESC);

    $collection-&gt;getSelect()
        -&gt;reset(\Zend_Db_Select::ORDER)
        -&gt;order($discountOrderExpr);
}

protected function joinProductAttribute(Collection $collection, string $attributeCode)
{
    $attribute  = $this-&gt;eavConfig-&gt;getAttribute(Product::ENTITY, $attributeCode);
    $tableAlias = sprintf('t_%s', $attribute-&gt;getAttributeCode());
    $linkField  = $this-&gt;metadata-&gt;getLinkField();

    $collection-&gt;getSelect()-&gt;joinLeft(
        [$tableAlias =&gt; $attribute-&gt;getBackendTable()],
        sprintf(
            'e.%1$s = %2$s.%1$s AND %2$s.attribute_id = %3$d AND %2$s.store_id = 0',
            $linkField, $tableAlias, $attribute-&gt;getId()
        ),
        []
    );
}

/**
 * @inheritDoc
 */
public function getLabel()
{
    return __('Top Discounted');
}

}

4. Recently bought

app/code/Acme/StackExchange/Model/VisualMerchandiser/Sorting/RecentlyBought.php

<?php
declare(strict_types=1);
namespace Acme\StackExchange\Model\VisualMerchandiser\Sorting;
use Magento\Catalog\Model\Indexer\Product\Price\DimensionCollectionFactory;
use Magento\Catalog\Model\ResourceModel\Product\Collection;
use Magento\Framework\App\Config\ScopeConfigInterface;
use Magento\Framework\Indexer\DimensionFactory;
use Magento\Framework\Indexer\ScopeResolver\IndexScopeResolver;
use Magento\Framework\Locale\ResolverInterface;
use Magento\Framework\Module\Manager;
use Magento\Framework\Stdlib\DateTime\TimezoneInterface;
use Magento\VisualMerchandiser\Model\Sorting\SortAbstract;
use Magento\VisualMerchandiser\Model\Sorting\SortInterface;
class RecentlyBought extends SortAbstract implements SortInterface
{
    protected TimezoneInterface $timezone;
    protected string            $locale;
public function __construct(
    Manager $moduleManager,
    ScopeConfigInterface $scopeConfig,
    TimezoneInterface $timezone,
    ResolverInterface $localeResolver,
    DimensionCollectionFactory $dimensionCollectionFactory = null,
    IndexScopeResolver $indexScopeResolver = null,
    DimensionFactory $dimensionFactory = null
) {
    $this-&gt;timezone = $timezone;
    $this-&gt;locale   = $localeResolver-&gt;getLocale();

    parent::__construct($moduleManager, $scopeConfig, $dimensionCollectionFactory, $indexScopeResolver, $dimensionFactory);
}

/**
 * @inheritDoc
 */
public function sort(Collection $collection)
{
    $dateTime = new \DateTime('now', new \DateTimeZone('UTC'));
    $dateTime-&gt;sub(new \DateInterval('P30D'));
    $storeDate = $this-&gt;timezone-&gt;date($dateTime, $this-&gt;locale)-&gt;format('Y-m-d');

    $connection = $collection-&gt;getConnection();
    $select     = $connection-&gt;select()
        -&gt;from($collection-&gt;getTable('sales_bestsellers_aggregated_daily'), [
            'qty_ordered' =&gt; new \Zend_Db_Expr('SUM(qty_ordered)'),
            'product_id',
        ])
        -&gt;where('store_id = 0')
        -&gt;where('period &gt;= ?', $storeDate)
        -&gt;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);
}

/**
 * @inheritDoc
 */
public function getLabel()
{
    return __('Recently bought');
}

}

Answer 2

This may help you regarding this :

https://github.com/viveksh2021/Bestseller

Magento 2: How to display top rated products?

Magento 2: Get recently reviewed Products on Category Page

Magento 2 : Mass Product add review and ratings using script

How to add product reviews progmatically