0

I am using custom code to implement sort by Best selling on the category page, I have created an around plugin for

<type name="Magento\Catalog\Block\Product\ProductList\Toolbar">
    <plugin name="Vnu_Catalog::addPriceDecendingFilterInToolbar" type="Vnu\Catalog\Plugin\Product\ProductList\Toolbar"/>
</type>

Toolbar.php

   /**
     * Plugin
     *
     * @param \Magento\Catalog\Block\Product\ProductList\Toolbar $subject
     * @param \Closure $proceed
     * @param \Magento\Framework\Data\Collection $collection
     * @return \Magento\Catalog\Block\Product\ProductList\Toolbar
     */
    public function aroundSetCollection(
        \Magento\Catalog\Block\Product\ProductList\Toolbar $subject,
        \Closure $proceed,
        $collection
    ) {
        $currentOrder = $subject->getCurrentOrder();
        $currentDirection = $subject->getCurrentDirection();
        $result = $proceed($collection);
        $subject->getCollection()->addAttributeToSelect('manufacturer');

        if ($currentOrder) {
            switch ($currentOrder) {
                case 'best_selling':
                    $subject->getCollection()->getSelect()->joinLeft(
                        'sales_order_item',
                        'e.entity_id = sales_order_item.product_id',
                        ['qty_ordered'=>'SUM(sales_order_item.qty_ordered)'])
                        ->group('e.entity_id')
                        ->order('qty_ordered desc');
                    //echo $subject->getCollection()->getSelect();die;


                    break;
                case 'price_desc':
                    $subject->getCollection()
                        ->setOrder('price', 'desc');
                    break;
                case 'price':
                    $subject->getCollection()
                        ->setOrder('price', 'asc');
                    break;
                case 'brand_asc':
                    $subject->getCollection()
                        ->addAttributeToFilter(
                            'manufacturer_value',
                            ['neq' => 'NULL']
                        )
                        ->setOrder('manufacturer_value', 'asc');
                    break;
                case 'brand_desc':
                    $subject->getCollection()
                        ->addAttributeToFilter(
                            'manufacturer_value',
                            ['neq' => 'NULL']
                        )
                        ->setOrder('manufacturer_value', 'desc');
                    break;
                case 'on_sale';
                    $subject->getCollection()
                        ->setOrder('special_price', 'desc');
                    break;

                default:
                    $subject->getCollection()->setOrder($currentOrder, $currentDirection);
                    break;

            }
        }
        //echo $subject->getCollection()->getSelect();die;
        return $result;
    }

When I print query I am able to see the correct results, but while rendering on category page, I am seeing some irrelevant results without pagination. Can anyone help on this issue?

0

You can get it by creating before plugin (following the below steps)

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\Catalog\Block\Product\ProductList\Toolbar">
        <plugin name="bestseller_before_set_collection"
                type="Vendor\Module\Plugin\Catalog\Product\Sorting\Bestsellers" />
    </type>
</config>

Bestsellers.php

<?php

namespace Vendor\Magento\Plugin\Catalog\Product\Sorting;

use Magento\Framework\Data\Collection;

class Bestsellers
{
    /**
     * Bestsellers sorting attribute
     */
    const 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(['soi' => $collection->getTable('sales_order_item')],
                'e.entity_id = soi.product_id',
                array('qty_ordered' => 'SUM(soi.qty_ordered)'))
                ->group('e.entity_id')
                ->order('qty_ordered DESC');
        }
    }
}

Hope it helps!

| improve this answer | |
  • Still i am facing the issue – Nagamani Aug 30 '19 at 10:23
  • The collection is loading properly on the page or you're facing issues with the collection as well. – Sumit Aug 30 '19 at 10:30
  • Collection is proper but when it renders that time issue is coming, let me check if any other custom extension is having problem – Nagamani Aug 30 '19 at 10:49
  • 1
    Yes, this is the issue with Magento 2.3 with the custom collection. This extension is working fine in Magento 2.2.X. – Sumit Aug 30 '19 at 10:58
  • 1
    I suspect that, because it was working in other project which was running in lower versions. Thank you very much @Sumit – Nagamani Aug 30 '19 at 11:02
0

I have found solution with below query,

$subject->getCollection()->getSelect()->joinLeft(
'sales_order_item',
'e.entity_id = sales_order_item.product_id',
array('qty_ordered'=>'(select sum(soi.qty_ordered) from sales_order_item soi where soi.product_id = e.entity_id)'))->distinct(true)
->order('qty_ordered '.$currentDirection);
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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