Sort by Best Seller on Category Page occur issue on Paging(Shows 1 item)

Question

I have an issue from last three days.I make a sorting option by Best Seller on Category listing Page.

Here You See three Products but item shows 1 which occurs issue on paging.

I used aroundSetCollection for Best Seller Sorting.Here This:

public function aroundSetCollection(
    \Magento\Catalog\Block\Product\ProductList\Toolbar $subject,
    \Closure $proceed,
    $collection
) {
    $currentOrder = $subject->getCurrentOrder();
    $result = $proceed($collection);
    $collection = $subject->getCollection();

    if ($currentOrder == 'best_selling') {
        $year = date("Y-m-d H:i:s", strtotime('-1 year'));
        $now = date("Y-m-d H:i:s");
        $collection->getSelect()->joinInner(
            'sales_order_item',
            'e.entity_id = sales_order_item.product_id',
            array('total_orders' => 'SUM(sales_order_item.qty_ordered)')
            )->where(
                'sales_order_item.created_at >= ?',
                $year
            )->where(
                'sales_order_item.created_at <= ?',
                $now
            )->group('e.entity_id')
            ->order('total_orders '.$subject->getCurrentDirection());
    }
    return $result;
}

And di.xml :

<type name="Magento\Catalog\Model\Config">
        <plugin name="add_bestselling_option" type="NameSpace\ModuleName\Plugin\Model\Config" />
    </type>
    <type name="Magento\Catalog\Block\Product\ProductList\Toolbar">
        <plugin name="set_bestselling_collection" type="NameSpace\ModuleName\Plugin\Product\ProductList\Toolbar" />
    </type>

Please help me i am stuck here from last three days.

Answer 1

Please try following way in your around setCollection

class Toolbar
{
    public function aroundSetCollection(
        \Magento\Catalog\Block\Product\ProductList\Toolbar $subject,
        \Closure $proceed,
        $collection
    ) {
        $currentOrder = $subject->getCurrentOrder();
        $currentDirection = $subject->getCurrentDirection();
        $result = $proceed($collection);
        $collection->getSize();
        if ($currentOrder == "best_selling") {
            $result->getCollection()->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 '.$this->getCurrentDirectionReverse($currentDirection));
        }
        return $result;
    }

    public function getCurrentDirectionReverse($currentDirection)
    {
        if ($currentDirection == 'asc') {
            return 'desc';
        } elseif ($currentDirection == 'desc') {
            return 'asc';
        } else {
            return $currentDirection;
        }
    }

Answer 2

We have had the same issue before and we were able to fix it by adding the if($subject->getCollection()->getSize()) statement around the join query.

if ($currentOrder == 'best_sellers') {
    if($subject->getCollection()->getSize()) {
        $subject->getCollection()->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 '.'desc');
    }

}

Answer 3

for some reason that I'm missing the pagination does not play well with group statements.
But I think I have a solution.

The possible solution:
Create a plugin (frontend only) on the method Magento\Catalog\Model\ResourceModel\Product\Collection::getSelectCountSql.

public function afterGetSelectCountSql(
    \Magento\Catalog\Model\ResourceModel\Product\Collection $subject
    \Magento\Framework\DB\Select $select;
) {
    $select->reset('group');
    return $select;
}

Now some explanations:

The total size of a collection is calculated via the method getSize. Here is an explanation about getSize and count differences: https://magento.stackexchange.com/a/4039/146
Short version: This getSize method calls getSelectCountSql (the one that I suggested to pluginize).
For some reason this method does some "funky" stuff to the group statements.

    $part = $this->getSelect()->getPart(\Magento\Framework\DB\Select::GROUP);
    if (!is_array($part) || !count($part)) {
        $countSelect->columns(new \Zend_Db_Expr('COUNT(*)'));
        return $countSelect;
    }
    $countSelect->reset(\Magento\Framework\DB\Select::GROUP);
    $group = $this->getSelect()->getPart(\Magento\Framework\DB\Select::GROUP);
    $countSelect->columns(new \Zend_Db_Expr(("COUNT(DISTINCT ".implode(", ", $group).")")));

I don't really understand what happens there, but in my case, the default getSelectCountSql returned a query and running this query got me this (I should get the size 6):

+-----------------------------+
| COUNT(DISTINCT e.entity_id) |
+-----------------------------+
|                           1 |
|                           1 |
|                           1 |
|                           1 |
|                           1 |
|                           1 |
+-----------------------------+

Removing the group part resulted in the correct result:

+-----------------------------+
| COUNT(DISTINCT e.entity_id) |
+-----------------------------+
|                           6 |
+-----------------------------+

To summarize: This approach I described above seems to work for this particular case, but I have no idea what other implications it might have.

Answer 4

I faced with the issues before.

As @Marius said: you used group in the product collection, that's why getSize of collection to one.

Solution 1: You should be using report the bestseller product of default Magento2 in table sales_bestsellers_aggregated_yearly.

You can use it and change $year to correctly result.

public function aroundSetCollection(
    \Magento\Catalog\Block\Product\ProductList\Toolbar $subject,
    \Closure $proceed,
    $collection
) {
    $currentOrder = $subject->getCurrentOrder();
    $result = $proceed($collection);
    $collection = $subject->getCollection();

    if ($currentOrder == 'best_selling') {
        $year = date("Y-m-d", strtotime('-1 year'));
        $collection->getSelect()->joinInner(
            ['bestsellers_yearly' => $collection->getTable('sales_bestsellers_aggregated_yearly')],
            'e.entity_id = bestsellers_yearly.product_id',
            ['qty_ordered']
        )->where(
            'bestsellers_yearly.period >= ?',
            $year
        )->order('qty_ordered ' . $subject->getCurrentDirection());
    }
    return $result;
}

Solution 2: Grouped before joining with the product collection.

public function aroundSetCollection(
    \Magento\Catalog\Block\Product\ProductList\Toolbar $subject,
    \Closure $proceed,
    $collection
) {
    $currentOrder = $subject->getCurrentOrder();
    $result = $proceed($collection);
    $collection = $subject->getCollection();

    if ($currentOrder == 'best_selling') {
        $year = date("Y-m-d H:i:s", strtotime('-1 year'));
        $connection = $collection->getConnection();
        $select     = $connection->select()
            ->from($connection->getTableName('sales_order_item'),
                ['product_id', 'total_orders' => 'SUM(sales_order_item.qty_ordered)']
            )->group('product_id');
        $collection->getSelect()
            ->joinInner(
                ['bestseller' => $select],
                'e.entity_id = bestseller.product_id',
                ['total_orders']
            )->where(
                'sales_order_item.created_at >= ?',
                $year
            )->order('bestseller.total_orders ' . $subject->getCurrentDirection());
    }
    return $result;
}

I prefer to use solution 1.

I hope it helps you.