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

[vutrankien]

Version : Magento2-0.5
Steps to reproduce

I using code :

$collection = $this->_gridFactory->create()->getCollection();
$tableMessagesReply = $this->_resource->getTableName('magebay_messages_reply');
$collection->getSelect()->joinLeft( array('messages_reply'=>$tableMessagesReply),'main_table.messages_id = messages_reply.messages_id',array('reply_id'=>"MAX(reply_id)"));
$collection->getSelect()->group('main_table.messages_id');  
$collection->getSelect()->where('main_table.user_id=?', $this->_customerSession->getId());
$collection->setOrder('reply_id', 'DESC');

Expected result

Actual result

Note

The count total items not correct

Posible fix

I checked and see magento 2 using function getTotalNum to return number total items but if use group by in sql querry the function will return number total of first group by, mean return number total items of table 2 in first group by, this is result not correct , the result correct is return number items of table 1

I noticed that if I change the in method getTotalNum

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

In class

namespace Magento\Theme\Block\Html;

/**
 * Html pager block
 * @SuppressWarnings(PHPMD.ExcessivePublicCount)
 */
class Pager extends \Magento\Framework\View\Element\Template

Change

return $this->getCollection()->getSize();

to this

return count($this->getCollection());

The result will correct with all case query include group by

[slavvka]

@vutrankien, thak you for reporting the problem. The internal ticket MAGETWO-54044 has been created.

[skovalenk]

@vutrankien, can you provide more details. Can you show me from where you take this collection? And where this code is using?

[vutrankien]

@sereban, i using the code above in

function _prepareLayout() 

for get collection for paging page

protected function _prepareLayout()
    {
        parent::_prepareLayout();
        if ($this->getCollection()) {
            // create pager block for collection
            $pager = $this->getLayout()->createBlock('Magento\Theme\Block\Html\Pager','my.custom.pager');
            $pager->setAvailableLimit(array(10=>10,20=>20,30=>30,'all'=>'all')); 
            $pager->setCollection($this->getCollection());
            $this->setChild('pager', $pager);
            $this->getCollection()->load();
        }
        return $this;
    }

The collection for group by is

$this->getCollection()

[sintsov]

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.

[sintsov]

Magento ver. 2.0.7

PHP 7.0.4-7ubuntu2.1 (cli) ( NTS )
Copyright (c) 1997-2016 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2016 Zend Technologies
    with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2016, by Zend Technologies
    with Xdebug v2.4.0, Copyright (c) 2002-2016, by Derick Rethans

[skovalenk]

@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

[skovalenk]

@vutrankien Hello. The problem you are describing appears because getSize() method in AbstractDb is cache results, in order to improve performance.
This is the correct behavior: in order to avoid issue you faced with, I suggest you to move your code before parent::_prepareLayout() and remove $collection->load()

You can add plugin to Magento\Framework\Data\Collection\AbstractDd::getSize, where you can avoid caching

Let me know how you resolve this issue

[thanh-taro]

In my case, the solution is make a plugin for Magento\Framework\Data\Collection\AbstractDd::getSize

public function afterGetSize($subject, $result)
{
    $sql = $subject->getSelectCountSql();
    $addSql = "SELECT count(*) FROM ($sql) AS taro01";
    $addTotal = $subject->getConnection()->fetchOne($addSql, []);
    $result += intval($addTotal) - 1;

    return $result;
}

[sintsov]

@sereban thx for you answer!
if i try add (only for test) in method _renderFiltersBefore code, example

$this->filterBuilder->setField('price');
$this->filterBuilder->setValue(0);
$this->searchCriteriaBuilder->addFilter($this->filterBuilder->create());

result 15 records, filter not working

if i make $this->getSelect()->where('type_id = \'simple\''); or $this->getSelect()->where('price = 0');
result 4 records, it true, but count of elements 15, probably because $this->_totalRecords = $this->searchResult->getTotalCount();

How filter product in catalog by type_id = simple? Help please.

[skovalenk]

@sintsov Can you chow me an example you filter you have added. And are you sure this filter was applied. You can make sure, that your filter was applied by calling: $this->getSelect()->__toString() - and finding your filter there

[sintsov]

@sereban In method _renderFiltersBefore() add

$this->filterBuilder->setField('type_id');
$this->filterBuilder->setValue('simple');
$this->searchCriteriaBuilder->addFilter($this->filterBuilder->create());

In code:

protected function _renderFiltersBefore()
    {
       ...
        if ($priceRangeCalculation) {
            $this->filterBuilder->setField('price_dynamic_algorithm');
            $this->filterBuilder->setValue($priceRangeCalculation);
            $this->searchCriteriaBuilder->addFilter($this->filterBuilder->create());
        }
        $this->filterBuilder->setField('type_id');
        $this->filterBuilder->setValue('simple');
        $this->searchCriteriaBuilder->addFilter($this->filterBuilder->create());

        ....
        echo '<pre>'.$this->getSelect()->__toString().'</pre>';die;
        $this->_totalRecords = $this->searchResult->getTotalCount();

        if ($this->order && 'relevance' === $this->order['field']) {
            $this->getSelect()->order('search_result.'. TemporaryStorage::FIELD_SCORE . ' ' . $this->order['dir']);
        }
        return parent::_renderFiltersBefore();
    }

Result:
SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `stock_status_index`.`stock_status` AS `is_salable`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=3 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='41'
 INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '3' AND price_index.customer_group_id = 0
 INNER JOIN `search_tmp_5798b4c914ba62_61460660` AS `search_result` ON e.entity_id = search_result.entity_id WHERE (stock_status_index.stock_status = 1)

Not add my condition.
If i make $this->getSelect()->where('type_id = \'simple\'');;

protected function _renderFiltersBefore()
    {
       ...
        if ($priceRangeCalculation) {
            $this->filterBuilder->setField('price_dynamic_algorithm');
            $this->filterBuilder->setValue($priceRangeCalculation);
            $this->searchCriteriaBuilder->addFilter($this->filterBuilder->create());
        }


        ....
       $this->getSelect()->where('type_id = \'simple\'');
        echo '<pre>'.$this->getSelect()->__toString().'</pre>';die;
        $this->_totalRecords = $this->searchResult->getTotalCount();

        if ($this->order && 'relevance' === $this->order['field']) {
            $this->getSelect()->order('search_result.'. TemporaryStorage::FIELD_SCORE . ' ' . $this->order['dir']);
        }
        return parent::_renderFiltersBefore();
    }

Result:

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `stock_status_index`.`stock_status` AS `is_salable`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=3 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='41'
 INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '3' AND price_index.customer_group_id = 0
 INNER JOIN `search_tmp_5798b68b0145e1_10978632` AS `search_result` ON e.entity_id = search_result.entity_id WHERE (stock_status_index.stock_status = 1) AND (type_id = 'simple')

It's good, but not correct result count elements on page. I think get total calculate how $this->_totalRecords = $this->searchResult->getTotalCount();

May be exist other method get all product by type_id

[vutrankien]

Hi @sereban
To now i still not have best solution
The solution i using is :
In class

namespace Magento\Theme\Block\Html;

/**
 * Html pager block
 * @SuppressWarnings(PHPMD.ExcessivePublicCount)
 */
class Pager extends \Magento\Framework\View\Element\Template

Change

return $this->getCollection()->getSize();

to this

return count($this->getCollection());

[senthilengg]

@magento-team any update on this. I too have the similar problem but not with the number.

For me, few products showing again in the catalog page up on going to the next page using the pagination.

For example i land up in some catalog page --> http://www.example.com/example.html it shows more than 100 products and the name of the first product is product 1

If i click the pagination in the bottom right and move to the next page showing product1 again. I hav e tried to go a little more and figured that the display order get changed if i add ?p=1 in the URL if there is no ?p=1 in then the order remains same.

Magento Version 2.1.0
PHP - FPM + NGINX

[bijalbhavsar]

@magento-team kindly guide us on this known issue related to pagination. We are also facing similar issue pagination is showing products but in listing not a single product showing. Verify below image for the same. Note: We are using Magento v2.0.7.

Looking forward to have your quick response.

[senthilengg]

I think if you doesn't set the position (default product sort order) under category --> products section then it will have this issue.We have to think of a smart workaround for this. @vinai quoted to have an observer to sort by entity id somewhere in stack overflow if I am not wrong.

Since there is no position magento randomly displaying products that might be the potential cause of this issue. But I have to dig further to conclude.

[sevos1984]

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

[TommyKolkman]

@sintsov I'm stuck here - I'm on the same approach as you, with the plugin and all, but cannot seem to get @sereban's solution working.

Would it be much work for you to show this how you fixed it in the end?

[SatishDubariya]

Product Sorting in Category page by Best Seller
Magento Upgraded to 2.1.3 from 2.1.0
Override below function

public function getAttributeUsedForSortByArray()
{
$options = ['position' => __('Position'),'topseller' => __('Top Seller')];
foreach ($this->getAttributesUsedForSortBy() as $attribute) {
/* @var $attribute \Magento\Eav\Model\Entity\Attribute\AbstractAttribute */
$options[$attribute->getAttributeCode()] = $attribute->getStoreLabel();
}
return $options;
}

public function setCollection($collection)
{
if($this->getCurrentOrder()=="topseller")
{
$collection->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('e.qty_ordered DESC');
}
$this->_collection = $collection;
$this->_collection->setCurPage($this->getCurrentPage());
// we need to set pagination only if passed value integer and more that 0
$limit = (int)$this->getLimit();
if ($limit) {
$this->_collection->setPageSize($limit);
}
if ($this->getCurrentOrder()) {
$this->_collection->setOrder($this->getCurrentOrder(), $this->getCurrentDirection());
}
return $this;
}

After doing this when i select Top Seller from sorter in toolbar i found that pagination missing from toolbar. after debugging my code i found that there is issue in group used in join query.
So can anyone explain what should i do to get proper pagination in toolbar.

[mayankzalavadia]

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

[surfer190]

@mayankzalavadia That worked but what about the layered navigation giving incorrect values?

There are still too many filters. Please try with bundled products in the category.

I think it would be better to solve the root cause of this issue. Why is it even bringing back the incorrect number of results?

[surfer190]

Oh and @mayankzalavadia now it counts how many are on the page, if you have a set greater than the amount on the page it will count the max that can be displayed on the page

[mayankzalavadia]

Sorry for late replay @surfer190. Just because I am busy with other work but once I am free I'll definatly Look in to it and let you know solution for that issue. But in that time if you find any solution for that then please dont forgot to share solution with US.

Thank You,
Mayank Zalavadia

[whereowareadmin]

@mayankzalavadia that solution is not working for me. I am using configurable products and the paging is counting the associated products as well, even though I made associated products visibility in search only, not catalog, search. do you have any comment for me?

[galanis-a]

@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.

[umarch06]

@mayankzalavadia I faced this issue again in v2.2.2, what about reopening this issue? We should fix this in next build as it is common when we are using group by.

[dpoltoratsky]

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.

[kazimnoorani]

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.

[RonakParmar]

@kazimnoorani Thanks for the solution.

[onepack]

@kazimnoorani

Using:
public function getTotalNum() {
return count($this->getCollection()->getAllIds());
}
Will result in the count being correct if the filtered collection fits on one page.
The total collection count however will be broken if it's bigger than one page.
So if you have a collection of 48 products it will show 1 - 20 of 20 (per page of 20).
This should be: 1-20 of 48.

Implementing: public function getLastPageNumber()
Will result in the loss of the pagination on all pages.

[onepack]

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?