Magento1.9 Grid Pagination – Fix Group Clause Issue

collection-filteringcollection;gridmagento-1.9

I m working on product grid but its pagination or product count not working(as it display wrong count). as my block _preparecollection function is as below.i have added category filter code in collection so i have to use group clause to prevent error for same id already exist.

    protected function _prepareCollection()
    {
        $store = $this->_getStore();
        $collection = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToSelect('sku')
            ->addAttributeToSelect('name')
            ->addAttributeToSelect('attribute_set_id')
            ->addAttributeToSelect('type_id')
            ->joinField('category_id',
                'catalog/category_product',
                'category_id',
                'product_id=entity_id',
                null,
                'left');
$collection->addAttributeToFilter('category_id', array('in' => array(4,10)))
            ->distinct(true);
            $collection->getSelect()->group('e.entity_id');


        if (Mage::helper('catalog')->isModuleEnabled('Mage_CatalogInventory')) {
            $collection->joinField('qty',
                'cataloginventory/stock_item',
                'qty',
                'product_id=entity_id',
                '{{table}}.stock_id=1',
                'left');
        }
        $collection->joinField('position',
                'catalog/category_product',
                'position',
                'product_id=entity_id',
                null,
                'left');
        $collection->joinField('websites',
            'catalog/product_website',
            'website_id',
            'product_id=entity_id',
            null,
            'left');
        if ($store->getId()) {
            //$collection->setStoreId($store->getId());
            $adminStore = Mage_Core_Model_App::ADMIN_STORE_ID;
            $collection->addStoreFilter($store);
            $collection->joinAttribute(
                'name',
                'catalog_product/name',
                'entity_id',
                null,
                'inner',
                $adminStore
            );

            $collection->joinAttribute(
                'custom_name',
                'catalog_product/name',
                'entity_id',
                null,
                'inner',
                $store->getId()
            );
            $collection->joinAttribute(
                'status',
                'catalog_product/status',
                'entity_id',
                null,
                'inner',
                $store->getId()
            );
            $collection->joinAttribute(
                'visibility',
                'catalog_product/visibility',
                'entity_id',
                null,
                'inner',
                $store->getId()
            );
            $collection->joinAttribute(
                'price',
                'catalog_product/price',
                'entity_id',
                null,
                'left',
                $store->getId()
            );
        }
        else {
            $collection->addAttributeToSelect('price');
            $collection->joinAttribute('status', 'catalog_product/status', 'entity_id', null, 'inner');
            $collection->joinAttribute('visibility', 'catalog_product/visibility', 'entity_id', null, 'inner');
        }

        $this->setCollection($collection);

        parent::_prepareCollection();
        $this->getCollection()->addWebsiteNamesToResult();
        return $this;
    }

I had google and got answer and add it to lib/varian/data/collection/db.php

    public function getSelectCountSql()
{
     $this->_renderFilters();

        $countSelect = clone $this->getSelect();
        $countSelect->reset(Zend_Db_Select::ORDER);
        $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
        $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
        $countSelect->reset(Zend_Db_Select::COLUMNS);

        if(count($this->getSelect()->getPart(Zend_Db_Select::GROUP)) > 0) {
            $countSelect->reset(Zend_Db_Select::GROUP);
            $countSelect->distinct(true);
            $group = $this->getSelect()->getPart(Zend_Db_Select::GROUP);
            $countSelect->columns("COUNT(DISTINCT ".implode(", ", $group).")");
        } else {
            $countSelect->columns('COUNT(*)');
        }
        return $countSelect;
}

enter image description here
But no luck please help to resolve this

Best Answer

Collections and Lazy Loading in Magento

The reason pagination does not work is because of how collections are counted and how lazy loading works with collections.

Collections in Magento implement the class Countable. Due to lazy loading of collections in Magento, whenever the method count() is called, the data has to be loaded. As a workaround of this, collections implement a method called getSize(). It will clone your SQL statement, wrap it in a COUNT() and return the result. This allowed a collection to get a total count without loading all the data. This allows for things such as filters to be added at the last minute.

This is what Varien_Data_Collection_Db::getSize() and it's partner getSelectCountSql() looks like:

/**
     * Get collection size
     *
     * @return int
     */
    public function getSize()
    {
        if (is_null($this->_totalRecords)) {
            $sql = $this->getSelectCountSql();
            $this->_totalRecords = $this->getConnection()->fetchOne($sql, $this->_bindParams);
        }
        return intval($this->_totalRecords);
    }

    /**
     * Get SQL for get record count
     *
     * @return Varien_Db_Select
     */
    public function getSelectCountSql()
    {
        $this->_renderFilters();

        $countSelect = clone $this->getSelect();
        $countSelect->reset(Zend_Db_Select::ORDER);
        $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
        $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
        $countSelect->reset(Zend_Db_Select::COLUMNS);

        $countSelect->columns('COUNT(*)');

        return $countSelect;
    }

Basically, it drops limits, columns, ordering, etc and leaves the filters behind. Then it adds a MySQL COUNT() to the columns.

The Problem

Normally, on one table, this would return one row with the total count. This is why getSize() does a fetchOne() against the query. However, when doing things such as table joins, group bys, and the like, you will not return one row, you will return multiple. It is because of this that you need to alter the getSize() method in your collection.

The Solution

This is what your method should look like now:

public function getSize() {

        if ( is_null( $this->_totalRecords ) ) {
            $sql = $this->getSelectCountSql();
            // fetch all rows since it's a joined table and run a count against it.
            $this->_totalRecords = count( $this->getConnection()->fetchall( $sql, $this->_bindParams ) );
        }

        return intval( $this->_totalRecords );
    }

Instead of a fetchOne(), we ran a fetchAll() wrapped in a count() PHP function. Now your totals will return appropriately.

Related Solutions

Magento – How to use NOT BETWEEN SQL with magento collection

The original answer is for BETWEEN. For NOT BETWEEN see update below

For the collection filter operators, it's always useful to take a look at Varien_Db_Adapter_Pdo_Mysql::prepareSqlCondition() which is well documented:

/**
 * Build SQL statement for condition
 *
 * If $condition integer or string - exact value will be filtered ('eq' condition)
 *
 * If $condition is array is - one of the following structures is expected:
 * - array("from" => $fromValue, "to" => $toValue)
 * - array("eq" => $equalValue)
 * - array("neq" => $notEqualValue)
 * - array("like" => $likeValue)
 * - array("in" => array($inValues))
 * - array("nin" => array($notInValues))
 * - array("notnull" => $valueIsNotNull)
 * - array("null" => $valueIsNull)
 * - array("gt" => $greaterValue)
 * - array("lt" => $lessValue)
 * - array("gteq" => $greaterOrEqualValue)
 * - array("lteq" => $lessOrEqualValue)
 * - array("finset" => $valueInSet)
 * - array("regexp" => $regularExpression)
 * - array("seq" => $stringValue)
 * - array("sneq" => $stringValue)
 *
 * If non matched - sequential array is expected and OR conditions
 * will be built using above mentioned structure
 *
 * @param string|array $fieldName
 * @param integer|string|array $condition
 * @return string
 */

Unfortunately there is no operator for BETWEEN, so you will have to either use $collection->getSelect() to manipulate the query directly or use a combination of < and > instead, which can be built using the from and to keys. For date comparison, there is even an additional date or datetime key that will make sure that the from and to parameters are formatted as dates¹.

But in your case, not the field itself should be "between" to parameters but the current date between two fields, so we cannot use from, to and datetime. Instead we call addFieldToFilter() twice so that the conditions are combined with AND:

->addFieldToFilter(array('start_date' => array('lt' => $curDate))
->addFieldToFilter(array('end_date' => array('gt' => $curDate));

To really use CURDATE() from MySQL and avoid trouble from timezone settings in PHP, initialize $curDate like this:

$curDate = new Zend_Db_Expr('CURDATE()');

Update:

Since you were asking for NOT BETWEEN, you need to switch lt and gt and add both conditions in a single addFieldToFilter call to combine them with OR:

->addFieldToFilter(array(
    'start_date' => array('gt' => $curDate),
    'end_date' => array('lt' => $curDate)
));

This will also find records where start_date is NULL and end_date is in the past (and vice versa), but not records where start_date and end_date are both NULL. This makes sense if start_date and end_date follow the same semantics as in core Magento and you want to find all records that are currently not within the range.

So you already were close to the solution but passed the parameters in a wrong form.

^{1) In theory, 'date' => true should format the parameter as date and 'datetime' => true as date and time. But they forgot to pass the $includeTime parameter to formatDate() in _prepareSqlDateCondition(), so they both use the default format (date and time)}

Magento – Grid pagination does not work in magento

The problem is the following:

The class Varien_Data_Collection implements pagination methods like setPageSize and setCurPage but the implementation of IteratorAggregate does not reflect when pagination has been enabled. It always returns an ArrayIterator over all items, not reflecting the pagination (no provider for pagination).

I suggest you look at the following resources to fix your problem: