magento2: Paging magento 2 not correct when use group in collection

SOLUTION FOR WRONG COUNT WITH GROUPBY

After Spent my lot of time, finally I have find solution for wrong count with groupby Issue and it’s work for me. So, I have share this solution with you all. I hope it’ll help you to.

For solution of wrong count with group you just need to follow 2 step which I listed below.

1. Open Toolbar.php file from : magento_root/vendor/magento/module-catalog/Block/Product/ProductList/Toolbar.php And Find " public function getTotalNum() " Change return $this->getCollection()->getSize(); to this return $this->getCollection()->count();

2. Open Collection.php file from : magento_root/vendor/magento/module-catalog/Model/ResourceModel/Product/Collection.php And Add following function at end of the class.

//-------------------------------
public function getLastPageNumber()
    {
        $collectionSize = (int)$this->getAllIds();
        if (0 === $collectionSize) {
            return 1;
        } elseif ($this->_pageSize) {
            return ceil($collectionSize / $this->_pageSize);
        } else {
            return 1;
        }
    }
//-------------------------------

If this solution is working for you then I suggest you don’t direct change to core file just override class and block and add the solution on that.

I Hope this will help you. and incase if this solution is not working for you and you find another solution for that then I’ll request you all the guy’s please share to all.

And I Also thanx to my colleagues Paresh he also help me out to find the solution for this issues.

Thank you, Mayank Zalavadia

Add the following in di.xml

<preference for="Magento\Catalog\Block\Product\ProductList\Toolbar" type="Example\Sortingoptions\Block\Rewrite\Product\ProductList\Toolbar" />    
<preference for="Magento\CatalogSearch\Model\ResourceModel\Fulltext\Collection" type="Example\Sortingoptions\Model\ResourceModel\Product\Collection" />

We are overriding 2 core files.

1. Add the following code in Example\Sortingoptions\Block\Rewrite\Product\ProductList\Toolbar.php

namespace Example\Sortingoptions\Block\Rewrite\Product\ProductList;

class Toolbar extends \Magento\Catalog\Block\Product\ProductList\Toolbar
{
   public function getTotalNum() {
        return count($this->getCollection()->getAllIds());
    }
}

1. Add the following code in Example\Sortingoptions\Model\ResourceModel\Product\Collection.php

namespace Example\Sortingoptions\Model\ResourceModel\Product;

class Collection extends \Magento\CatalogSearch\Model\ResourceModel\Fulltext\Collection
{
    public function getLastPageNumber()
    {
        $collectionSize = count($this->getAllIds());
        if (0 === $collectionSize) {
            return 1;
        } elseif ($this->_pageSize) {
            return ceil($collectionSize / $this->_pageSize);
        } else {
            return 1;
        }
    }
}

It’s done. Run magento upgrade commands and clean cache.

I believe the issue is in \Magento\Catalog\Model\ResourceModel\Product\Collection::_getSelectCountSql as it always sets $countSelect->columns('COUNT(DISTINCT e.entity_id)'); regardless of presence of GROUP BY in the query.

I fixed it with the simple plugin as follows:

use Magento\Catalog\Model\ResourceModel\Product\Collection as Subject;
use Magento\Framework\DB\Select;

class Collection
{

    /**
     * Correctly sets select columns depending on the presence of GROUP BY in the query
     *
     * @param Subject $subject
     * @param callable $proceed
     *
     * @return Select
     *
     * @throws \Zend_Db_Select_Exception
     */
    public function aroundGetSelectCountSql(
        /* @noinspection PhpUnusedParameterInspection */
        Subject $subject,
        callable $proceed
    ) {
        /** @var Select $result */
        $result = $proceed();

        if (count($result->getPart(Select::GROUP))) {
            $group = $result->getPart(Select::GROUP);

            $result->reset(Select::GROUP);
            $result->reset(Select::COLUMNS);

            $result->columns(new \Zend_Db_Expr(("COUNT(DISTINCT " . implode(", ", $group) . ")")));
        }

        return $result;
    }
}

Hope this helps.

Hello, a similar problem. If i want filter product collection, create plugin for Layer (if use event the result will be the same).

<type name="Magento\Catalog\Model\Layer">
<plugin name="LayerPlugin" type="Company\Module\Model\Plugin\Layer"/>
</type>

Code LayerPlugin (filter product collection by type_id):

class Layer {
    public function afterGetProductCollection($subject, $collection) {
        $collection->addAttributeToFilter('type_id', array('eq' => 'simple'));
        return $collection;
    }
}

Then i view my product catalog if see 2 product (it’s true), but count 9.

If i look Magento\Catalog\Block\ProductListProduct::_beforeToHtml() $toolbar->setCollection($collection); after this getSize() return 9. then getSize() have $this->_totalRecords = 9, but i call getSize() early i get 2. Example: First:

Result:

Second:

Result:

If use count($collection) i get correct 2.

@mayankzalavadia’s answer is almost correct. In Magento/Catalog/Block/Product/ProductList/Toolbar.php getTotalNum() function must be public function getTotalNum() { return count($this->getCollection()->getAllIds()); }

public function getTotalNum() { return count($this->getCollection()->getAllIds()); }

and in your Magento\Catalog\Model\ResourceModel\Product\Collection override class the getLastPageNumber() function should be

public function getLastPageNumber() { $collectionSize = count($this->getAllIds()); if (0 === $collectionSize) { return 1; } elseif ($this->_pageSize) { return ceil($collectionSize / $this->_pageSize); } else { return 1; } }

Use overrides class and do not edit core files and if you have third party extensions that override the Magento\Catalog\Model\ResourceModel\Product\Collection you should override that for this to work.

Hope it helps guys.

Initial issue is fixed https://github.com/magento/magento2/commit/e012a481737ba124281581442f57231bee259aa4. Be free to open new one if that doesn’t help

We have more bugs all pointing to this but there just doesn’t seem to be any real solid fix.

• I get correct numbers but pagination is broken.
• Pagination working but numbers of unfiltered collections.
• A filtered collection of 11 products with the items per page of 8 will result in 4 products on the first and the rest on page 2 and 3.

Related: https://github.com/magento/magento2/pull/10246 https://github.com/magento/magento2/issues/7730

All the bug-reports are however closed with no clear proper fix. How is this possible?

@sintsov . There is no problem here. We couldn`t apply any filters to product collection, because from now we using product collection only as wrapper for search fulltext collection

So you need to add your filter here: Magento\CatalogSearch\Model\ResourceModel\Fulltext\Collection::_renderFiltersBefore

There you can find SearchCriteriaBuilder, in which you can add your own attribute to filter Note: that your attribute should be present in mysql fulltext index (in solr, elastic search indexes) and attribute should be set as filterable

Also I notice, that type_id is field not attribute