Magento 2 add complex (aggregate sql function) collection query for grid

This blogpost provides solution to most common problem which we face several time while creating grid. If you have some complex collection query for grid creation specially if you are using aggregate function then you might face issue with filters, export csv functionality.

Magento 2 grid Creation with custom query

For adding custom query we need to override __initSelect function in our collection file as shown in below code:-

namespace Namespace\ModuleName\Model\ResourceModel\SpCommission;

use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;

class Collection extends AbstractCollection {

    protected function _initSelect() {
        parent::_initSelect();
        $ObjectManager = \Magento\Framework\App\ObjectManager::getInstance();

        $this->getSelect()->columns('created_dt as salesperson_createddt');
        $connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION);
        $table = $connection->getTableName('i95dev_ar_book');
        $this->getSelect()->join(array('options' => $table), '`main_table`.`i95dev_ar_book_id` = `options`.`primary_id`');
        $this->getSelect()->columns('SUM(salesperson_commision) as total_commission')
            ->group(array('gp_salesperson_id'));
        $this->addFilterToMap('salesperson_createddt', 'main_table.created_dt');
        $this->addFilterToMap('primary_id', 'main_table.primary_id');

    }
}

So above is the way to customize your collection query for grids. Now we will see the tips for some issue you might face:-

Field is ambiguous

Suppose you joined two table and both have same name field and you are using it in filters then ‘field is ambiguous’ is most common issue you will face, to avoid it you need to map field with it alias and use alias everywhere rather than field name.

$this->addFilterToMap('primary_id', 'main_table.primary_id');

total_commission(Aggregate function field) is not a column

Note:- Magento 2 grid are made to work for simple queries with not much complexity

This happens when we do filter because as you can see above we are using ‘group by’ and aggregate function. If you see getSelectCountSql() function in \Magento\Framework\Data\Collection\AbstractDb file

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

        $countSelect = clone $this->getSelect();
        $countSelect->reset(\Magento\Framework\DB\Select::ORDER);
        $countSelect->reset(\Magento\Framework\DB\Select::LIMIT_COUNT);
        $countSelect->reset(\Magento\Framework\DB\Select::LIMIT_OFFSET);
        $countSelect->reset(\Magento\Framework\DB\Select::COLUMNS);

        if (!count($this->getSelect()->getPart(\Magento\Framework\DB\Select::GROUP))) {
            $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).")")));
        return $countSelect;
    }

Above you can see that our collection column, limit offset, count , order and all other things are being reset to get the count of result. Since column get reset how will magneto know what is total_commission as this is not already created column name in any table thus you need to override this method to make customization according to your own needs.

Aggregate function column Alias is not working with filter due to where clause

Here mapping filter will be a bad suggestion, thus you need to override addFieldToFilter() function and request for having clause rather than where to make your code work.

Leave a Comment.