Magento 1

Magento Cart Items – Getting Child Product Items

This is how it’s done in the Mage_Checkout_Block_Cart_Item_Renderer_Configurable class. It will return the simple product if available otherwise it will return the product of the cart item.

public function getChildProduct()
    if ($option = $this->getItem()->getOptionByCode('simple_product')) {
        return $option->getProduct();
    return $this->getProduct();
Magento 1

Adding an Image Attribute To A Product

This post deals with adding an image (or any other type of media) to a product. This is useful where an image is required to be separate from Magento’s built in product media system.

The Installer

First, we want to create our attribute.

$installer = $this;


$this->addAttribute('catalog_product', 'poster_frame', array(
    'attribute_set' => 'Default',
    'group'         => 'iWeb Media',
    'input'         => 'image',
    'type'          => 'varchar',
    'label'         => 'Poster Frame',
    'visible'       => true,
    'required'      => false,
    'visible_on_front' => true,
    'global'        => Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL,
    'backend'       => 'iwebmedia/product_attribute_backend_image',
    'input_renderer'=> 'iwebmedia/adminhtml_product_image',
    'sort_order' => 10

  • Because we’re saving an image, we need to create our own backend class to deal with the saving of the image and setting the filename of the image on the product object.
  • We also need to create a custom renderer for the input. This is a block class which we’ll create in our module. By default, Magento will use its own image renderer which is Mage_Adminhtml_Block_Catalog_Category_Helper_Image. This extends Varien_Data_Form_Element_Image and hardcodes the getUrl() method to look in media/catalog/product.

Note: The input_renderer is a special field for the catalog_product EAV type and is stored in the catalog_eav_attribute table as frontend_input_renderer.

The Input Renderer

class Iweb_Media_Block_Adminhtml_Product_Image extends Varien_Data_Form_Element_Image{
    protected function _getUrl()
        $url = false;
        if ($this->getValue()) {
            $url = Mage::helper('iwebmedia')->getPlaceholderUrl() . $this->getValue();
        return $url;

– We just needed to change the getUrl() method to return the path to our image location, which we’ve got a helper to do so that’s only in one location.

The Helper Class

class Iweb_Media_Helper_Data extends Mage_Core_Helper_Abstract {
    const PLACEHOLDER_UPLOAD_DIR = "iwebmedia";
    public function getPlaceholderDir(){
        return Mage::getBaseDir('media') . DS . self::PLACEHOLDER_UPLOAD_DIR . DS;
    public function getPlaceholderUrl(){
        return Mage::getBaseUrl('media') . '/' . self::PLACEHOLDER_UPLOAD_DIR . '/';

The Attribute’s Backend Model

This is the class which deals with the saving of our data and image

class Iweb_Media_Model_Product_Attribute_Backend_Image extends Mage_Eav_Model_Entity_Attribute_Backend_Abstract{        
    public function beforeSave($object){
        $name = $this->_getName();
        $imageData = $object->getData($name);
        if(isset($imageData['delete']) && (bool) $imageData['delete']){
            return $this->_removeImage($object, $imageData['value']);
            return $this->_uploadImage($object);
    protected function _getHelper(){
        return Mage::helper('iwebmedia');
    protected function _getName(){
        return $this->getAttribute()->getName();
    protected function _removeImage($object, $fileName){
        $file = $this->_getHelper()->getPlaceholderDir() . $fileName;
        $name = $this->_getName();
        $object->setData($name, '');
    protected function _uploadImage($object){
        $name = $this->_getName();
        if(!isset($_FILES[$name]) || (int) $_FILES[$name]['size'] <= 0){
        $path = $this->_getHelper()->getPlaceholderDir();
        $uploader = new Varien_File_Uploader($_FILES[$name]);
        // Allow Magento to create a name for this upload!
        $result = $uploader->save($path);
        $object->setData($name, $result['file']);

– We use the beforeSave() method which then uses the delete input (provided by Magento’s Varien_Data_Form_Element_Image class) to check whether we should remove the existing image or upload a new one.
– The Varien_File_Uploader class deals with the validation of the image and will throw an error in case of any failure.
– setAllowRenameFiles(true) allows the file uploader to create a new file name if the current one already exists.
$object->setData($name, $result[‘file’]); sets the new filename on the product for saving.

Magento 1

Simplified Magento Flow with Events

The Mage_Core_Controller_Varien_Front is instantiated. This is the class which is always used despite its front name. It picks which routers should be used.

Event: controller_front_init_before
Event: controller_front_init_routers

Mage_Core_Controller_Varien_Front → dispatch() → Router Match Loop. The standard routers are:

  • Mage_Core_Controller_Varien_Router_Standard (Frontend)
  • Mage_Core_Controller_Varien_Router_Admin (Admin)
  • Mage_Core_Controller_Varien_Router_Default (Default)

When a router matches in the loop, its job is to set the following on the request:


And then calls dispatch on the instance of the controller (Eg. Mage_Cms_IndexController).

Dispatch is defined in Mage_Core_Controller_Varien_Action, which is extended by Mage_Core_Controller_Front_Action, which should be extended by custom controllers on the frontend.


The dispatch method calls predispatch in the controller. To not dispatch the controller, I.e to forward to another controller at this point, set:


And the controller’s action method will not be called. To switch controller, action, etc, change the values on the request object, and the router’s match loop will continue and attempt to dispatch the new controller. The predispatch method then fires:

Event: controller_action_predispatch
Event: controller_action_predispatch_ + getRouteName()
Event: controller_action_predispatch_ + getFullActionName()

These events can also be used to hook into, and forward to another controller if required. At this point, no blocks are created – the layout cannot be manipulated. If the request’s isDispatched method, remains as true, then the flow continues, and the controller’s action method is executed.


The following two methods (loadLayout and renderLayout) must be called from the custom controller’s action method for these to apply


is called with the option to supply handles. These replace the ‘default’ handle, so if using this, pass default in your handles array. Note: These handles will be added before any action layout handles, so the instructions within them may be overwritten.


adds the STORE_code layout handle, theme handle (THEME_*), and the full action name in lowercase (E.g. customer_account_index)


Event: controller_action_layout_load_before
This is the best event to use to add custom layout handles, however it will fire for every controller. See this for workarounds.


Event: controller_action_layout_generate_xml_before


Event: controller_action_layout_generate_blocks_before
The XML has been generated at this point, not any actual layout blocks. If required, modifications to the XML could be made at this point.


The blocks are now created. Each block fires:
Event: core_block_abstract_prepare_layout_before
before the block’s _prepareLayout() is called, followed by
*Event:* core_block_abstract_prepare_layout_after

Event: controller_action_layout_generate_blocks_after
This is where blocks can programatically be inserted into the layout.


Event: controller_action_layout_render_before
Event: controller_action_layout_render_before_ + fullActionName
where blocks can also be added to the layout programatically, however a reference to the layout object is not passed in the dispatchd observer, so the layout must be retrieved using Mage::app()->getLayout()


The controller’s postDispatch() method is then called, firing the following events:

Event: controller_action_postdispatch
Event: controller_action_postdispatch_ + $this->getFullActionName()
Event: controller_action_postdispatch_ + $this->getRouteName()

Magento 1

Adding Layout Handles Programatically (and in order)

Adding custom layout handles in the correct order can sometimes be an awkward process, it can often be tempting to use either the controller_action_predispatch or the controller_action_predispatch_fullActionName, event and then add the layout handle in the corresponding observer code.

The problem with this approach is that these events are fired in the controller’s preDispatch method, meaning loadLayout hasn’t yet been called and there won’t be any other layout handles present at that point. This causes problems because your layout handle may rely on items from other layout handles which haven’t been loaded at that point.

Correcting the handles’ order

One method to fix this issue is to use the controller_action_layout_load_before event which is fired in the loadLayoutUpdates, method (this is called via the loadLayout method). At this point, addActionLayoutHandles has already been executed, which makes relying on other handles a possibility. One problem with this approach is the observer method will be called for every page, which won’t work correctly for code which relies on running for a fullActionName. One fix for this method is to either check the existence of a required layout handle or controller action’s full name in the observer code.




public function updateLayoutHandle($observer)
    $update = $observer->getEvent()->getLayout()->getUpdate();
    $handles = $update->getHandles();

    foreach($handles as $k => $handle){
        if($handle == 'catalog_category_default'){
            $handles[$k] = 'catalog_category_layered';


This allows us to get all layout handles, change them as necessary, and re-add them (an array can be passed to the addHandle method). We could also splice in handles at required points using this method.

Magento 1

Returning an Array of Values from a Collection

Magento collections contain the protected method _toOptionArray which can be exposed to public methods of the collection. The method accepts two arguments, the first becomes the key of the resultant array, and the second becomes the value.

public function toOptionArray(){
return $this->_toOptionArray('business_type_id', 'title');
Magento 1

Important Methods Cheat Sheet

Store Information

Get the Admin Store Model

<?php Mage::getModel('core/store')->load('admin', 'code');

Get the Base Website

<?php Mage::getModel('core/website')->load('base', 'code');

Get the Admin Store ID (Constant)

<?php Mage_Store_Model_App::ADMIN_STORE_ID;


Reindex everything

/* @var $indexCollection Mage_Index_Model_Resource_Process_Collection */
$indexCollection = Mage::getModel('index/process')->getCollection();
foreach ($indexCollection as $index) {
/* @var $index Mage_Index_Model_Process */

Reindex a Particular Indexer

$process = Mage::getModel('index/indexer')->getProcessByCode('catalog_product_price');
Magento 1

Adminhtml Pages


Create an adminhtml.xml file in your module. These items can exist in your config.xml file, but to cut down on xml parsing time on the frontend, they were moved to their own file. Inside of config.xml, they must be in an node.

        <menuitem translate="title" module="mymodule">
            <title>My wonderful menu item</title>

This will create a top level admin menu item called My Wonderful Menu Item. An optional note will describe the route for the menu item.

Within the menu items, each can have a child node which describes sub menu items. Each sub menu item can also have further sub menus.

    <flogit translate="title" module="catalog">
        <title>FLOG IT</title>

Note the adminhtml. This isn’t actually part of the route, it’s the router that magento is to use. Look in app/code/core/Mage/Adminhtml/etc/config.xml:


Which has a frontName of admin.
** When adding admin controllers, they should all add themselves to the adminhtml front name, so layout handles always read adminhtml_controller_action **


By default, only admin users with full privileges would see the above menu item. To allow other users access to these menu items, the following node needs to be added to the adminhtml.xml file:

                <horse translate="title" module="dangerousdave">
                    <title>Horse Admin Ability</title>
                        <goose translate="title" module="dangerousdave">
                            <title><![CDATA[Let's have some goose <b>fat</b>]]></title>

Under the resources->admin->children node, the structure mirrors that of the menu node. Each name must match that of the menu node, with the title being displayed in the admin area System > Permissions > Roles. These roles can now be assigned to users.

ACL resource are cached in the PHP session object. This means it’s often necessary to log in and log out to view changes related to newly configured ACL resources.

The front name to the admin is defined in app/etc/local, so it is always the last thing that’s loaded. This is so that other modules cannot interfere with the adminhtml’s frontname. By convention, it should be changed from just admin as a first line of defence.

Permissions – Extended

As every adminhtml controller extends Mage_Adminhtml_Controller_Action, they will by default fall back to that class’ _isAllowed method which returns true. This means, that regardless of ACL XML, every user has access. Relevant ACL checks should be placed in this method.


protected function _isAllowed(){
   return Mage::getSingleton('admin/session')->isAllowed('admin');

This means that any user which does not have full admin privileges by default would not be able to view the page. This should be overridden in the module’s controller:

class Dangerous_Dave_Adminhtml_TestController extends Mage_Adminhtml_Controller_Action{

	public function _isAllowed(){
		return Mage::getSingleton(‘admin/session’)->isAllowed(‘admin/horse/goose’);


This will deny access to any user which does not have access to the node ‘admin/horse/goose’, whose nodes are defined by the ACL. The admin part of the check from this method is optional.

System Configuration ACL

To allow access to parts of the system config admin, use the following structure:

                                    <catalog translate="title" module="catalog">
                                        <title>Catalog Section</title>

This differs from setting ACL items on regular menu items, as we have to dig from menu to page to set ACL on the system panels.

Note: When adding to the ACL for system, the tab is not used, go from the sections node


Controllers are largely the same as they are on the frontend. The one difference, however, is that they should exist in the adminhtml router by adding another node to its node. Eg.

                        <Dangerous_Dave before="Mage_Adminhtml">Dangerous_Dave_Adminhtml</Dangerous_Dave>

This will check the folder Dangerous_Dave_Adminhtml before Mage_Adminhtml for controllers matching the specified url’s route. Note that routers live under the admin node in config. Most other aspects of adding to adminhtml will live under the adminhtml node.

All adminhtml pages should extend the controller Mage_Adminhtml_Controller_Action

class Dangerous_Dave_Admminhtml_TestController extends Mage_Adminhtml_Controller_Action{
	public function indexAction(){
		// Action

To test routes in a development environment without the need for a corresponding menu item, secret keys can be turned off (the hash at the end of admin urls). To do this, go to System > Configuration > Admin > Secret Keys. After switching the setting off, you will be logged out of the admin area and will no longer have a hash in the Url after logging back in.


Adding layout files to the adminhtml area:


It would appear that most Magento extensions use default/default *to put their assets in, however *base/default can also be used, and the same as in the frontend area is the last level of fallback.

Adminhtml Controller (Mage_Adminhtml_Controller_Action)
should be extended for all admin controllers

There are several utility methods which can be used from this controller.

_getSession() // Mage::getSingleton(‘adminhtml/session’); (This is not the same as checking ACL permissions - that’s admin/session
_getHelper() // return Mage::helper(‘adminhtml');
__setActiveMenu($menuPath) - $this->getLayout()->getBlock(‘menu')->setActive($menuPath);
_addBreadcrumb($label, $title, $link = null) // Adds a link to the breadcrumbs block. Note: Breadcrumbs are not implemented in the admin at this point.
_addContent($block) // Appends a block to the content block via $layout->getBlock(‘content’)
_addLeft($block) // Appends a block to the left block 
_addJs($block) // Appends a block to the js block

deniedAction() // Redirects the user to the login
noRouteAction() // Redirects the user to a 404
setUsedModuleName(‘helper_group’) // Used for setting the helper in translation
__() // translates using the module’s helper set in setUsedModuleName()
_redirectReferer() // Redirects to the referring page
_redirect($path, $arguments)
_forward($action, $controller, $module, $params);
getUrl($route, $params)
_title($title) // Sets the page title. This can be called consecutively to chain titles, E.g. $this->_title(‘CMS’)->_title->(‘Edit Page’); The individual calls are separated with slashes.

It’s advisable that in each action method which will save data to a model, the model is instantiated in the controller and placed in the registry for easy access by other parts of the system.

To output a widget container, it can either be added to the content via XML in the conventional way, or added via the action method using the _addContent method above.

Widget Container

This is responsible for setting up the grid automatically. In the constructor, certain parameters
to infer the grid block must be set:

$this->_controller = “adminhtml/download” // The controller class we’re currently in
$this->_blockGroup = ‘stormkingskin’ // The block group the container will look in to create the grid
$this->_headerText = Mage::helper(‘stormkingskin’)->__(‘Download Log’); // This gets output in the widget/grid/container.phtml template

Blocks & Grids

  • Adminhtml works on a system of containers.
  • Adminhtml_Block_Widget_Form_Container Used for wrapping. One or more container for ‘real’ form.
  • Adminhtml_Block_Widget_Grid: Used for displaying grids of data
  • Adminhtml_Block_Widget_Form: Class in Magento which handles forms
  • Varien_Data_Form: Generating and processing form

Form elements we can add to fieldsets can be found in
lib/Varien/Form/Element, which include

Checkbox, Date, File, Imagefile, Link, Note, Radio, Reset, Textarea, Button, Collection, Editor, Gallery, Image, Multiline, Obscure, Radios, Select, Text, Checkboxes, Column, Fieldset, Hidden, Label, Multiselect, Password, Renderer, Submit, Time

Grid Containers

A grid container can loosely be defined as an admin ‘page’. They allow easier setting up of grids, however infer their setup information for the grids based on class variables:

$this->_blockGroup = ‘dangerousdave’ // This tells the container where to find the block grid
$this->_controller = ‘adminhtml_goose’ // Tells the container the current controller that we’re in
$this->_headerText = “It’s a Moose!” // Sets the page header text (the orange text on the page)

When the container attempts to instantiate the grid object, it automatically looks in
$this->_blockGroup . “/“ . $this->_controller . “_grid”

So, the above would look for the block in dangerousdave/adminhtml_goose_grid, meaning the file Dangerous_Dave_Block_Adminhtml_Goose_Grid must exist. When a grid is created in this way, the method ->setSaveParametersInSession(true) is called on it, which should save all filter and sorting parameters on the grid for when the page is revisited.

Buttons are also created in the constructor, and an add button is created by default. This can be removed using $this->removeButton(‘add’);

Adding more buttons to the Grid Container

 $this->_addButton('add', array(
            'label'     => ‘Add New’,
            'onclick'   => ‘setLocation(\’*/*/new\’)’,
            'class'     => 'add',

Hrefs all seem to be handled be javascript. Nice.

When adding buttons, it’s a good idea to check whether the user has permission to perform the associated action via an ACL check:

	// Add button code


Components for grids are filters (the bar at the top which allows record sets to be filtered), sorters, data, and totals.

As well as the normal block flow (constructor, _prepareLayout, _toHtml, _beforeToHtml), grids have extra methods which are automatically called:

_prepareGrid // Calls _prepareColumns, _prepareMassactionBlock and _prepareCollection
*_prepareColumns* // Creates column items
*_prepareCollection* // Gets the collection for the grid

_prepareColumns – addColumn – pass in an array of keys and values for the column:

header (Column Title)
width (Eg 50px – optional)
type (Eg number)
index (Eg name, text, date) //This puts a relevant filter type at the top of the column

_prepareMassaction – used for performing actions on more than one row

protected function _prepareMassaction(){
            $this->setMassactionIdField(‘id'); // A unique name for the column
            $this->getMassactionBlock()->setFormFieldName(‘items'); // The form field name the values will be posted as
            $this->getMassactionBlock()->addItem('delete', array(
                 'label'    => Mage::helper(‘catalog')->__('Delete'), // Label of the button
                 'url'      => $this->getUrl(‘*/*/massDelete'), // Controller / action to post to
                 'confirm'  => Mage::helper('catalog')->__('Are you sure?’) // displayed in a JS confirmation

Example Controller for the example above

    public function massDeleteAction(){
        $ids = $this->getRequest()->getParam('items');
        foreach($ids as $id){
            $model = Mage::getModel('dangerousdave/goose')->load($id);
        Mage::getSingleton('adminhtml/session')->addSuccess("They've been deleted, guv!");

_prepareCollection – this should retrieve the data for the grid. Even if columns have been set up, without a valid collection object being passed to $this->setCollection, the grid will not render.

public function _prepareCollection(){
        $collection = Mage::getModel('dangerousdave/goose')->getCollection();

        return parent::_prepareCollection();

This is the one place where joins may happen outside of resource models – all of the data which the grid requires needs to be on one collection object to be displayed in the grid.
Note: Calling _prepareCollection on the parent is important for prepareCollection, as it applies filters and sorting.

Updating with AJAX

To update grids using AJAX, firstly set the following on the grid (this can be done in the constructor)


Secondly, create the following getGridUrl method, where ‘grid’ is the name of the action.

public function getGridUrl(){
    return $this->getUrl('*/*/grid', array('_current'=> true));

Thirdly, add an ajax check to the indexAction of the controller. This will forward the request to the grid action if the ajax parameter is passed:

public function indexAction(){
	if ($this->getRequest()->getQuery('ajax')) {


To output the grid, this can be done in XML where the correct layout handle is used which directly outputs the block and removes the root node, however this may cause issues with other parts of the system which rely on the root node.

The second way to do this would be to create the corresponding method in the controller class, and output the block directly:

  public function gridAction(){
        return $this->getResponse()->setBody(

Edit Pages

Edit pages are constructed in a similar way to grid pages, they consist of a container which should be added to the content block, and tabs which should be added to the left block.

Form Container

Firstly, create a container class which extends Mage_Adminhtml_Block_Widget_Form_Container

Similar to the grid container, the form container also automatically instantiates a child element based from set variables.

 $this->setChild('form', $this->getLayout()->createBlock($this->_blockGroup . '/' . $this->_controller . '_' . $this->_mode . '_form'));

So, the variables
$this->blockGroup, $this->_controller, and $this->_mode must be set. Mode is ‘edit’ by default.

Use the constructor to add and remove buttons, just like the grid container. The object ID also should be set here, which refers to the primary key ID.
$this->_updateButton and $this->removeButton are methods for this:
$this->_objectId = ‘goose_id’;
*$this->_updateButton(‘save’, ‘label’, Mage::helper(‘dangerousdave’)->__(‘Save Goose’));*

To set the header text on the page, use the method getHeaderText:

public function getHeaderText(){
    return "Edit Goose";

Form – extends Mage_Adminhtml_Block_Widget_Form

Use the *__construct* method to set an ID for the form and to set the title for the form. The ID is not the ID used for the form element, that is set on the *Varien_Data_Form* below.

public function __construct(){
	$this->setTitle($this->__('Goose Information'));

The _prepareLayout method can be used to add anything to the head block or add any other requirements for the form, E.g.

protected function _prepareLayout()
        if (Mage::getSingleton('cms/wysiwyg_config')->isEnabled()) {

The main part of the form class is the _prepareForm method, which is in charge of adding all of the fields to the actual form.

An instance of Varien_Data_Form is firsty created, with an id, an action, and a method. These are all attributes to go onto the form element itself:

$form = new Varien_Data_Form(
        'id' => 'edit_form', 
        'action' => $this->getData('action'), 
        'method' => ‘post'

A prefix can be set for form elements to use. This is used for the output only, not when retrieving data:


Now, field sets can be added to the form:

$fieldset = $form->addFieldset(
        'legend'=>Mage::helper('cms')->__('General Information'),
        'class' => ‘fieldset-wide'

The first parameter is the Id of the fieldset, and the second parameter is the array which will be passed to Varien_Data_Form_Element_Fieldset. All keys within this array will be transferred the data of the fieldset.

Set a key of legend in the parameter array to give the fieldset a title.

Adding Form Elements

Add form elements to field sets:

$fieldset->addField('name', 'text', array(
    'name' => 'tag_name',
    'label' => Mage::helper('tag')->__('Tag Name'),
    'title' => Mage::helper('tag')->__('Tag Name'),
    'required' => true,
    'after_element_html' => ' ' . Mage::helper('adminhtml')->__('[GLOBAL]'),

When creating fields, the name of the field must correspond with the data keys set on the form.

Magento 1

The Checkout Object and Session Object

** TODO: Mage_Checkout_Model_Cart **

The Magento Session object should be used when querying the current quote. Access it like so:

 <?php $checkout = Mage::getSingleton('checkout/session')

The getQuote() method is responsible for retrieving the current quote. If a quote ID doesn’t exist on the session object then a new quote object (Mage_Sales_Model_Quote) is created and set up with all of the necessary data (customer id, store id, remote ip etc).

If a product has never been added to the cart or the cart isn’t being loaded from a previous session, it’s likely the cart will never have been saved and will have no id. The cart gets saved when a product is added from the Checkout module’s CartController (Mage_Checkout_CartController) in the addAction.

Once the cart has been saved, the checkout session only retains the database Id of the cart, and uses that to reload the cart on subsequent getCart() calls. The quote id persists the session because it’s set on the data object of the checkout session object. Other properties (such as _quote) do not and are reloaded when requested.

Useful Methods – checkout/session

// Nullifys the quote

// Checks whether a quote exists

// Gets the currently active quote, or creates one if a quote id is not set on the session object

// Get the Quote ID

// Clears the quote and dispatches event checkout_quote_destroy

// Get the last real order ID

Events Dispatched

// At the start of the getQuote() method
Mage::dispatchEvent('custom_quote_process', array('checkout_session' => $this));

// In the getQuote() method, if no customer is logged in and there's no customer object on the session
Mage::dispatchEvent('checkout_quote_init', array('quote'=>$quote));

// In the loadCustomerQuote() method, when an attempt is made to retrieve a customer's quote
Mage::dispatchEvent('load_customer_quote_before', array('checkout_session' => $this));

// In the clear() method:
Mage::dispatchEvent('checkout_quote_destroy', array('quote'=>$this->getQuote()));
Magento 1

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:

protected function _prepareCollection(){
	$collection = Mage::getResourceModel('llapgoch_banners/banner_collection');
		array('banner_item' => $collection->getTable('llapgoch_banners/banner_item')),
	$collection->addExpressionFieldToSelect('num_banner_items', 'COUNT(*)', null);

	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:

protected function _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:

$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.
Magento 1

SQL in Magento

Although Magento’s collections and models are convenient, they can often be cumbersome and awkward to get to perform certain tasks. Fortunately, through the connection (Magento_Db_Adapter_Pdo_Mysql, and ultimately Zend’s MySql connection stack down to Zend_Db_Adapter_Abstract) and select (Varien_Db_Select, and ultimately Zend’s Zend_Db_Select) allow us perform database queries without too much hassle.

Getting the Connection Object

A connection object can be used from the following places:

$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$connection = Mage::getSingleton('core/resource')->getConnection('core_write');


Getting a Table’s Name

$table = Mage::getSingleton('core/resource')->getTableName('llapgoch_banners/banner');

Getting part of a SELECT


Fetch Col

$connection->fetchCol('SELECT sku from ' . $resource->getTableName('catalog/product'));

– This puts the columns into a single array. I.e. all skus will be in a one dimensional array (array(‘sku1’, ‘sku2’, ‘sku3’) etc.

Fetch One

$connection->fetchOne('SELECT * FROM ' . $resource->getTableName('llapgoch_banners/banner'));

– Returns a single row for the query

Fetch All

$connection->fetchAll('SELECT * FROM ' . $resource->getTableName('llapgoch_banners/banner'));

– Returns all rows for the query

Combining with a Select Object

Writing direct SQL is all well and good, but it’s often neater and safer to write such queries using a select object, and passing that to the connection:

$resource = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('core_read');
$select = new Varien_Db_Select($connection);
$select->from($resource->getTableName('llapgoch_banners/banner'), array('title', 'is_active'))
     ->where('banner_id > 1');
$results = $connection->fetchAll($select);

Binding Values

Binding values is useful for automatic value escaping. By referencing variables using :variable_name, we can pass in arrays of replacement variables into the connection’s fetch methods:

$resource = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('core_read');
$select = new Varien_Db_Select($connection);
	->where('banner_id > :num_banners');
$results = $connection->fetchAll($select, array(
	'num_banners' => 1

Select Object Methods


 <?php $select->where($condition, $value = null);

– where provides the option of passing a single value into the condition to be replaced, but can also be used like the above example using binds. To use the value in the condition, we use a question mark:

 <?php $select->where('banner_id > ?', 1);


 <?php $select->limit($number, $offset);


 <?php $select->orderRand();


 <?php $select->from($tableName, $cols = array());


 <?php $select->columns($cols = "*", $correlationName = null);?>

– Specifies the columns to be brought in the select. Can contain aggregate queries.


 <?php $select->union($select = array(), $type = self::SQL_UNION); 


 <?php $select->join($name, $cond, $cols = self::SQL_WILDCARD, $schema = null);

– This is the same as the joinInner method.

Join Left

 <?php $select->joinLeft($name, $cond, $cols = self::SQL_WILDCARD, $schema = null);

Join Right

 <?php $select->joinRight($name, $cond, $cols = self::SQL_WILDCARD, $schema = null); 

OR Where

 <?php $select->orWhere($cond, $value = null, $type = null); 


 <?php $select->group($spec) 

– $spec can be an array or a string


 <?php $select->having($cond, $value = null, $type = null); 

– $value will be replaced into $cond using a question mark in the query.

OR Having

 <?php $select->orHaving($cond, $value = null, $type = null);

– $value will be replaced into $cond using a question mark in the query.


 <?php $select->order($spec);

– $spec can be a string or an array of columns.


 <?php $select->query($fetchMode = null, $bind = array()); 

– $fetchMode is an integer. This is optional to using the connection’s fetchAll, fetchOne, and fetchCols methods


 <?php $select->reset($part = null);

– Allows parts of the query to be reset


 <?php $select->getAdapter();

– Returns the adapter (connection object) passed into the constructor.

TODO: Add info about the Zend Connection (adapter) methods in Zend_Db_Adapter_Abstract