Magento Adminhtml Grids and Advanced Collection Queries

Sometimes, when we’re creating an Adminhtml grid, we need to join our collection onto another table in order to get the relevant data that we need. This can open up some pitfalls when Magento attempts to do its pagination routines.

Consider the following, our _prepareCollection method:

<?php
protected function _prepareCollection(){
	$collection = Mage::getResourceModel('llapgoch_banners/banner_collection');
		
	$collection->getSelect()->joinLeft(
		array('banner_item' => $collection->getTable('llapgoch_banners/banner_item')),
		'banner_item.banner_id=main_table.banner_id',
		array()			
	);
		
	$collection->getSelect()->group('main_table.banner_id');
	$collection->addExpressionFieldToSelect('num_banner_items', 'COUNT(*)', null);

	$this->setCollection($collection);
		
	return parent::_prepareCollection();	
}

– Note our addExpressionFieldToSelect call, where we get the number of banner items for each banner. Our query would look like this:

SELECT `main_table`.*, COUNT(*) AS `num_banner_items` FROM `llapgoch_banner` AS `main_table` LEFT JOIN `llapgoch_banner_item` AS `banner_item` ON banner_item.banner_id=main_table.banner_id GROUP BY `main_table`.`banner_id`

– All good so far, let’s see what columns we’ve defined in our _prepareColumns method:

<?php
protected function _prepareColumns(){
		parent::_prepareColumns();
		$helper = Mage::helper('llapgoch_banners');
		
		$this->addColumn('banner_id', array(
			'header' => $helper->__('ID'),
			'type' => 'text',
			'index' => 'banner_id',
			'width' => '150px'
		));
		
		$this->addColumn('title', array(
			'header' => $helper->__('Title'),
			'type' => 'string',
			'index' => 'title'
		));
		
		$this->addColumn('is_active', array(
			'header' => $helper->__('Active'),
			'type' => 'options',
			'index' => 'is_active',
			'width' => '150px',
			// Add options to the filter
			'options' => array(
				1 => 'Enabled',
				0 => 'Disabled'
			)
		));
		
		$this->addColumn('num_banner_items', array(
			'header' => $helper->__('Number of Banner Items'),
			'type' => 'text',
			'index' => 'num_banner_items',
			'width' => '150px',
		));
		
		return $this;
	}
 

All appears fine so far, until that is we filter by the num_banner_items field! Magento thoughtlessly strips out all column names from our query, and attempts to add num_banner_items into our where clause – and being an aggregate alias, that would never work anyway.

To get around this, we can add our own filter method to the column, and take control of how it affects the query in question:

<?php
$this->addColumn('num_banner_items', array(
	'header' => $helper->__('Number of Banner Items'),
	'type' => 'options',
	'index' => 'num_banner_items',
	'width' => '150px',
	'filter_condition_callback' => array($this, '_numBannersFilter'),
));

protected function _numBannersFilter($collection, $column){
	$collection->getSelect()->having('COUNT(*) = ?', $column->getFilter()->getValue());
}
  • Now, we still can’t use our alias num_banner_items as Magento will still strip out all columns (apart from its own COUNT(*)) when it runs through its own pagination routine for the grid. Instead, we have to use our aggregate function again. Yes, it’s messy… but it works.