6

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

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.

5
+25

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;
        }
    }
|improve this answer|||||
2

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.

|improve this answer|||||
  • Thanks @Marius for your time.You are explained in detail and i appreciate your answer.Thanks once again for explain in detail. – Muhammad Ahmed Dec 12 '19 at 8:24
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');
    }

}
|improve this answer|||||
  • it is working great!!! – aravind Dec 12 '19 at 10:44
  • Thanks for your answer. I test it and its working fine.Thanks again!!! – Muhammad Ahmed Dec 13 '19 at 11:42
1

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.

|improve this answer|||||
  • Your answer have some sql dependencies due to which sql error occurs.Please first test it on your project then answer us in a correct way. @Thao Pham – Muhammad Ahmed Dec 13 '19 at 11:41

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.