Adding a Category Attribute

The installer needs to be run as the Mage_Catalog_Model_Resource_Setup class:

<resources>
    <rayware_setup>
        <setup>
            <module>Rayware_Core</module>
            <class>Mage_Catalog_Model_Resource_Setup</class>
        </setup>
    </rayware_setup>
</resources>
$this->removeAttribute('catalog_category', 'brand');

$this->addAttribute('catalog_category', 'brand', array(
    'group'         => 'General Information',
    'input'         => 'select',
    'type'          => 'int',
    'label'         => 'Brand Link',
    'backend'       => 'eav/entity_attribute_backend_array',
    'source'        => 'rayware/attribute_source_brand',
    'visible'       => true,
    'required'      => false,
    'visible_on_front' => true,
    'used_in_product_listing' => true, // Use this for the attribute to be added to the category flat table!
    'global'        => Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL,
));

Note: It would be nice to be able to reindex after adding the category attribute so that it propagates to the catalog_category_flat_store table, however it would appear that just running the catalog_category_flat indexer is not sufficient from the installer. Unless all indexers are run, which could be a really slow process, the attribute does not appear to be added to the flat table.

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;

Reindexing

Reindex everything

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

Reindex a Particular Indexer

<?php
$process = Mage::getModel('index/indexer')->getProcessByCode('catalog_product_price');
$process->reindexAll();

Adminhtml Pages

Menus

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.

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

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.

<children>
    <flogit translate="title" module="catalog">
        <title>FLOG IT</title>
        <sort_order>100</sort_order>
        <action>adminhtml/routename/index</action>
        <children>
	...
	</children>
    </flogit>
</children>

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:

<admin>
     <routers>
         <adminhtml>
             <use>admin</use>
             <args>
                 <module>Mage_Adminhtml</module>
                 <frontName>admin</frontName>
             </args>
         </adminhtml>
     </routers>
</admin>

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 **

Permissions

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:

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

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.

*Note: THIS HAS BEEN CHANGED IN THE NEWEST VERSION OF MAGENTO 1.9: *

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:

<config>
    <acl>
        <resources>
            <admin>
                <children>
                    <system>
                        <children>
                            <config>
                                <children>
                                    <catalog translate="title" module="catalog">
                                        <title>Catalog Section</title>
                                    </catalog>
                                </children>
                            </config>
                        </children>
                    </system>
                </children>
            </admin>
        </resources>
    </acl>
</config>

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

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.

<config>
    <admin>
        <routers>
            <adminhtml>
                <args>
                    <modules>
                        <Dangerous_Dave before="Mage_Adminhtml">Dangerous_Dave_Adminhtml</Dangerous_Dave>
                    </modules>
                </args>
            </adminhtml>
        </routers>
    </admin>
</config>

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.

Layouts

Adding layout files to the adminhtml area:

<config>
   <layout>
      <updates>
     	   <dangerous_layout>
            <file>dangerous/admin_layout.xml</file>
         </dangerous_layout>
      </updates>
  </layout>
</config>

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:

if(Mage::getSingleton(‘admin/session’)->isAllowed(‘cms/page/new’)){
	// Add button code
}else{
	$this->removeButton(‘add’);
}

Grids

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
*_prepareMassactionBlock*
*_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);
            if($model->getId()){
                $model->delete();
            }
        }
        
        Mage::getSingleton('adminhtml/session')->addSuccess("They've been deleted, guv!");
        
        $this->_redirectReferer();
    }

_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();
        
        $this->setCollection($collection);

        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)

$this->setUseAjax(true);

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')) {
		$this->_forward('grid');
		return;
	}

    $this->loadLayout();
    $this->renderLayout();
}

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(){
        $this->loadLayout();
        return $this->getResponse()->setBody(
            $this->getLayout()->createBlock('dangerousdave/adminhtml_goose_grid')->toHtml()
        );
    }

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’));*
*$this->_removeButton(‘save’);*

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->setId('goose_form');
	$this->setTitle($this->__('Goose Information'));
	parent::__construct();
}

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()
    {
        parent::_prepareLayout();
        if (Mage::getSingleton('cms/wysiwyg_config')->isEnabled()) {
            $this->getLayout()->getBlock('head')->setCanLoadTinyMce(true);
        }
    }

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(
    array(
        '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:

$form->setHtmlIdPrefix(‘block_’);

Now, field sets can be added to the form:

$fieldset = $form->addFieldset(
    'base_fieldset', 
    array(
        '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.

Registering a Module

Location

For the purpose of this example, we’ll make our module in app/code/.

Create a namespace directory – E.g. ‘llapgoch’. Within that should be another directory which is typically the name of the module; for this example we’ll use ‘developertoolbar’. The structure of this folder is quite similar to Magento 1’s module structure.

Within our developertoolbar directory, we need to create a registration.php file which Magento will look for in order to register our module. This would be a rough equivalent of the old module registration stub files back in Magento 1, which in this case would be Llapgoch_Developertoolbar.xml.

Add the following to the registration.php file to tell Magento about our new module:

\Magento\Framework\Component\ComponentRegistrar::register(
    \Magento\Framework\Component\ComponentRegistrar::MODULE,
    'Llapgoch_Developertoolbar',
    __DIR__
);

The Register Method’s Parameters

  1. Type– Either MODULE , LIBRARY, LANGUAGE, or THEME which are constants within the ComponentRegistrar class.
  2. Name
  3. Location – Typically, we’ll use PHP’s magic constant __DIR__ to tell Magento that our module exists in the same place as our registration.php file

Check The Module’s Status

To check Magento can actually see the module, navigate to the bin folder in terminal and run:

./magento module:status

Your module should be listed at the bottom, in “List of disabled modules”.

Enable the Module

Run the following from terminal which will enable your module:

./magento module:enable Llapgoch_Developertoolbar

And that’s it! We’ve got a module registered with Magento. Now there’s the task of actually making it do something…

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

<?php
// Nullifys the quote
Mage::getSingleton('checkout/session')->unsetAll()

// Checks whether a quote exists
Mage::getSingleton('checkout/session')->hasQuote()

// Gets the currently active quote, or creates one if a quote id is not set on the session object
Mage::getSingleton('checkout/session')->getQuote()

// Get the Quote ID
Mage::getSingleton('checkout/session')->getQuoteId()

// Clears the quote and dispatches event checkout_quote_destroy
Mage::getSingleton('checkout/session')->clear()

// Get the last real order ID
Mage::getSingleton('checkout/session')->getLastRealOrder()

Events Dispatched

<?php
// 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 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.

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:

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

$collection->getConnection();
$model->getResource()->getReadConnection();

Getting a Table’s Name

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

Fetch Col

<?php
$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

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

– Returns a single row for the query

Fetch All

<?php
$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:

<?php
$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:

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

Select Object Methods

Where

 <?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);

Limit

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

OrderRand

 <?php $select->orderRand();

From

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

Columns

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

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

Union

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

Join

 <?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); 

Group

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

– $spec can be an array or a string

Having

 <?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.

Order

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

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

Query

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

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

Reset

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

– Allows parts of the query to be reset

getAdapter

 <?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

Redirecting the Newsletter Success Page

Sometimes, clients would like the newsletter success action to redirect to an actual page rather than just setting a message and redirecting to the referrer (the default action). Here, we override the newsletter controller and allow a CMS page to be set to the success page which allows the client to edit the content and also change the page at their will.

config.xml

<frontend>
		<routers>
			<newsletter>
				<args>
					<modules>
						<VanVault_Skin before="Mage_Newsletter">VanVault_Skin_Newsletter</VanVault_Skin>
					</modules>
				</args>
			</newsletter>
		</routers>
	</frontend>
 

The Controller

<?php
require_once(Mage::getModuleDir('controllers', 'Mage_Newsletter') . DS . 'SubscriberController.php');

class VanVault_Skin_Newsletter_SubscriberController extends Mage_Newsletter_SubscriberController{
	const XML_PATH_CMS_REDIRECT_PAGE = "newsletter/subscription/redirect_page";
	
	public function newAction(){
		parent::newAction();
		
		if($pageIdentifier = Mage::getStoreConfig(self::XML_PATH_CMS_REDIRECT_PAGE)){
			$this->_redirectUrl(Mage::helper('cms/page')->getPageUrl($pageIdentifier));
		}
	}
}

system.xml

<?xml version="1.0"?>
<config>
	<sections>
		<newsletter>
			<groups>
				<subscription>
					<fields>
						<redirect_page translate="label" module="vanvault">
							<label>CMS Redirect Page</label>
                            <show_in_default>1</show_in_default>
                            <show_in_website>1</show_in_website>
                            <show_in_store>1</show_in_store>
							<frontend_type>select</frontend_type>
							<source_model>vanvault/adminhtml_system_config_source_cms_page</source_model>
							<sort_order>200</sort_order>
						</redirect_page>
					</fields>
				</subscription>
			</groups>
		</newsletter>
	</sections>
</config>

The System Config Source Class

We override the standard cms page selector (Mage_Adminhtml_Model_System_Config_Source_Cms_Page) to add a blank option at the start. This allows the default behaviour of the newsletter success action to remain if no option is selected.

<?php
class VanVault_Skin_Model_Adminhtml_System_Config_Source_Cms_Page extends Mage_Adminhtml_Model_System_Config_Source_Cms_Page{
	public function toOptionArray(){
		$options = parent::toOptionArray();
		array_unshift($options, array(
			'value' => '',
			'label' => Mage::helper('vanvault')->__('-- Please Select --')
		));
		
		return $options;
	}
		
}