1

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

enter image description here

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?
5
  • Please clarify what are you mean under item 1 and 2, because it's not clear. 3 - do you mean special price or? Jul 23, 2022 at 23:06
  • Item 1 : The products have different sizes, like small, medium , large or extra large. Which means sort products based on which has all size options. You can say filters products which has more size options, products with 5 size options will be on top and products with no size options will be on bottom Jul 24, 2022 at 7:29
  • Item 2: means sort products Based on only 1 size options available means the product who has only 1 size options will be on top and products who has all size options will be on bottom Jul 24, 2022 at 7:35
  • Item 3: i mean is if a product is given a special price it's get discount ( price - special price) , so sort products based on who has most discount, or there's a attribute with name " Discount Percent " on product edit form, you can also sort products based on which products has more discount percentage Jul 24, 2022 at 7:39
  • @VictorTihonchuk Jul 24, 2022 at 17:16

2 Answers 2

1
+50

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');
    }
}

1
  • i have add screenshot for configurable products in my answers Jul 25, 2022 at 13:00
-1

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

1
  • Thanks, but this is not what i was looking Jul 24, 2022 at 19:56

Your Answer

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

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