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

        $this->sortingOptions[26]= $lowStockBottom;
    }

    /**
     * @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\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->moduleManager->isEnabled('Magento_CatalogInventory')) {
            return $collection;
        }

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

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

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

        $resultIds = [];

        $collection->load();

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

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

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

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

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

\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 __("Date to Top");
    }
}

\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 __("Best Selling");
    }
}

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->eavConfig = $eavConfig;
        $this->metadata  = $metadataPool->getMetadata(ProductInterface::class);
        $this->timezone  = $timezone;
        $this->locale    = $localeResolver->getLocale();

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

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

        $connection = $collection->getConnection();
        $storeDate  = $this->timezone->date(
            new \DateTime('now', new \DateTimeZone('UTC')),
            $this->locale
        )->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 < %1$s)'
                . ' AND (t_special_to_date.value IS NULL OR t_special_to_date.value > %1$s)'
                . ' AND (t_special_price.value > 0 AND t_special_price.value < t_price.value)',
                $connection->quote($storeDate)
            )
        );

        $specialPrice = $connection->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->getSelect()
            ->reset(\Zend_Db_Select::ORDER)
            ->order($discountOrderExpr);
    }

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

        $collection->getSelect()->joinLeft(
            [$tableAlias => $attribute->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->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->timezone = $timezone;
        $this->locale   = $localeResolver->getLocale();

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

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

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

    /**
     * @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