How to fix Magento 2 pagination when use group in collection

Sometimes when you work with Magento 2 collection, you have to join some information or aggregate data from other tables. You may receive an exception like "Item (Magento\Catalog\Model\Product\Interceptor) with the same ID "#" already exists." In that case, you will have to group collection by entity ID field. When the native Magento 2 pagination is used for this collection, you will face an issue that the size of the collection is always 1. It happens because Magento, in order to calculate collection size, takes the first cell value of the result provided by select count query.

Check get size logic below:

public function getSize() {
    if ($this->totalRecords === null) {
        $sql = $this->getSelectCountSql();
        $this->totalRecords = $this->getConnection()->fetchOne($sql, $this->bindParams);
    }

    return intval($this->totalRecords);
}

A solution is to wrap a select query provided by collection by another select to sum values for all result records. The solution works for any type of collection. In my example, I will show how to do a plugin for product collection.

1. Define a plugin for product collection:

 #File: app/code/My/Module/etc/di.xml
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../../../lib/internal/Magento/Framework/ObjectManager/etc/config.xsd">
    <type name="Magento\Catalog\Model\ResourceModel\Product\Collection">
        <plugin name="my-module-product-collection" type="My\Module\Plugin\Catalog\ResourceModel\Product\Collection"/>
    </type>
</config>

2. Create a plugin class:

#File: app/code/My/Module/Plugin/Catalog/ResourceModel/Product/Collection.php

namespace My\Module\Plugin\Catalog\ResourceModel\Product;

class Collection {

    /**
    * Get select count sql 
    * @param $subject \Magento\Catalog\Model\ResourceModel\Product\Collection
    * @param $result \Magento\Framework\DB\Select
    * @return \Magento\Framework\DB\Select
    */
    public function afterGetSelectCountSql(
        \Magento\Catalog\Model\ResourceModel\Product\Collection $subject,
        \Magento\Framework\DB\Select $result
    ) {
        $countSelect = clone $result;
        $countSelect->reset();

        // we should find a name of column which was used for select count sql
        $columns = $result->getPart('columns');
        $columns = reset($columns);

        $countSelect->from(["entity_table" => new \Zend_Db_Expr("({$result})")], "SUM(`{$columns[1]}`)");

        return $countSelect;
    }
}