Joining EAV tables on a Non-EAV Collection

Sometimes, we may want to join EAV tables and their associated value tables on other tables via a foreign key. E.g. we have a table which has a download count for customers who are able to download PDFs. We have a column called customer_id which is our foreign key to link us to the customer_entity table.

Our download table has the following columns:

  • download_log_id
  • customer_id
  • file_path
  • type
  • count
  • customer_name
  • created_at
  • updated_at

The table is grouped by file_path, with each download increasing the count column. We can’t use a collection of the type customer/customer, because we’ll end up with duplicate customers with the same ID in the collection, which Magento will throw an error at.

Instead, we’ll use the collection based from the download table’s collection, and will attempt to join the customer’s EAV tables onto it. To add the attributes firstname and lastname, we can use a custom helper which is detailed in full below:

class Stormking_Skin_Helper_Eav extends Mage_Core_Helper_Abstract{
    protected $_aliasIndex = 0;

    public function joinEAV($collection, $mainTableForeignKey, $eavType, $attrCode, $mainTable = 'main_table'){
        $this->_aliasIndex++;

        $entityType = Mage::getModel('eav/entity_type')->loadByCode($eavType);
        $entityTable = $collection->getTable($entityType->getEntityTable());

        //Use an incremented index to make sure all of the aliases for the eav attribute tables are unique.
        $attribute = Mage::getModel("eav/config")->getAttribute($eavType, $attrCode);

        $attr =  Mage::getModel('eav/entity_attribute')->loadByCode($eavType, $attrCode);

        $alias = 'table_' . $this->_aliasIndex;
        $field = $attrCode; // This will either be the original attribute code or 'value'

        if ($attribute->getBackendType() != 'static'){
            $field = 'value';
            $table = $entityTable. '_'.$attribute->getBackendType();

            $collection->getSelect()
                ->joinLeft(array($alias => $table),
                    $mainTable . '.'.$mainTableForeignKey.' = '.$alias.'.entity_id and '.$alias.'.attribute_id = '. $attr->getId(),
                    array($attribute->getAttributeCode() => $alias . "." . $field)
                );
        }else{
            $collection->getSelect()
                ->joinLeft(array($alias => $entityTable),
                $mainTable . '.'.$mainTableForeignKey.' = '. $alias.'.entity_id',
                    $attribute->getAttributeCode()
                );
        }

        // Return the table alias and field name (either $attrCode or value) so we can use the table in future queries
        return array(
            "table" => $alias,
            "field" => $field
        );

    }
}
  • This creates a join for each of the attributes we wish to join on.
  • The table alias is incremented for each use so we don’t get alias clashes.
  • We return the aliased table name and the name of the field used. This will either be the original field name (in the case of static values) or value in the case of values in a corresponding EAV value table.

Applying The Helper

$collection = Mage::getModel('stormkingskin/download_log')->getCollection();

$helper = Mage::helper('llapgoch_core/eav');
$helper->joinEAV($collection, 'customer_id', 'customer', 'email');
$helper->joinEAV($collection, 'customer_id', 'customer', 'firstname');
$helper->joinEAV($collection, 'customer_id', 'customer', 'lastname');
$helper->joinEAV($collection, 'customer_id', 'customer', 'group_id');

Using the Aliased Table in Other Queries

We may want to use the table which has been joined by our joinEAV query in later additions to our collection. In the example above, suppose we then want to join on the customer’s group_id from the customer_entity table on Magento’s customer_group table so that we can get the customer group name.

The joinEAV method returns the aliased table name which we can then use to make this work:

 
$groupTable = $helper->joinEAV($collection, 'customer_id', 'customer', 'group_id');

$collection->getSelect()->joinLeft(
	array('customer_group' => $collection->getTable('customer/customer_group')),
	'customer_group.customer_group_id=' . $groupTable . '.group_id',
	array('customer_group_code')
);

– This allows us to use the table name from our previous query in our new join to pull out the customer_group_code data.

Filtering in Adminhtml Grids

Using the above methods will cause issues with Adminhtml grids. All of the fields not in the main table’s collection will be stripped out and will cause the SQL to fail. Fortunately, there is a workaround albeit a little convoluted:

1. Log each of the aliased table names

In our grid class, create an array where we will store our generated alias names:

protected $_aliasTables = array();

protected function _prepareCollection() {

	$collection = Mage::getModel('stormkingskin/download_log')->getCollection();
	$helper = Mage::helper('stormkingskin/eav');

	$this->_aliasTables['email'] = $helper->joinEAV($collection, 'customer_id', 'customer', 'email');
	$this->_aliasTables['firstname'] = $helper->joinEAV($collection, 'customer_id', 'customer', 'firstname');
	$this->_aliasTables['lastname'] = $helper->joinEAV($collection, 'customer_id', 'customer', 'lastname');

	$groupTable = $helper->joinEAV($collection, 'customer_id', 'customer', 'group_id');

	$collection->getSelect()->joinLeft(
		array('customer_group' => $collection->getTable('customer/customer_group')),
		'customer_group.customer_group_id=' . $groupTable['table'] . '.group_id',
		array('customer_group_code')
	);

	$collection->getSelect()->joinLeft(
		array('customer_address' => $collection->getTable('customer/address_entity')),
		'customer_address.parent_id=main_table.customer_id',
		array("")
	);

	// Use the last parameter to join this to the customer_address table instead of the default main_table
	$this->_aliasTables['company'] = $helper->joinEAV($collection, 'entity_id', 'customer_address', 'company', 'customer_address');

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

– Note that our query to get the company name differs somewhat as we need to rely on the ID from the customer_address_entity table rather than the default main_table. We use a joinLeft just before to set up the relationship between the main_table and the customer_entity_address table. We can then create our relationship between the customer_entity_address model and the entity’s value table.

2. Add a filter condition callback to any affected fields

This will allow us to override Magento’s default query filter builder process and use our own. All of the field aliases will still be stripped off the final query, but we will now be able to use their original names using the aliases from the last step.

protected function _prepareColumns() {
	$this->addColumn('firstname', array(
		'header'	=> $this->__('First Name'),
		'index'		=> 'firstname',
		'filter_condition_callback' => array($this, '_joinUsingHaving'),
	));

	$this->addColumn('lastname', array(
		'header'	=> $this->__('Last Name'),
		'index'		=> 'lastname',
		'filter_condition_callback' => array($this, '_joinUsingHaving'),
	));

	$this->addColumn('company', array(
		'header'	=> $this->__('Company'),
		'index'		=> 'company',
		'filter_condition_callback' => array($this, '_joinUsingHaving'),
	));
}

3. The Filter Condition Callback Method

protected function _joinUsingHaving($collection, $column){

	$index = $column->getIndex();

	if(!isset($this->_aliasTables[$index])){
		return;
	}

	$tableAlias = $this->_aliasTables[$index];
	$value = $collection->getConnection()->quote("%" . $column->getFilter()->getValue() . "%");
	$collection->getSelect()->where($tableAlias['table'] . "." . $tableAlias['field'] . " LIKE " . $value);
}
  • We check the existence of the index we’re looking for in our alias array.
  • We quote our filter value ready for the custom where query.
  • We then use our table alias and field name.

See here for more information on advanced adminhtml collection queries

Adding Extra Attributes to All Product Collections

This can be very useful for attributes which need to be available on all product collections. This is to be used sparingly, however – adding a lot of attributes to a product collection can drastically impact performance; all attributes should also be indexed into the flat tables so that they’re available when flat catalog is on.

Config

<frontend>
	<events>
		<catalog_product_collection_load_before>
			<observers>
				<llapgoch_pcattributes_add>
					<model>llapgoch_addpcattributes/observer</model>
					<method>addAttributes</method>
					<type>model</type>
				</llapgoch_pcattributes_add>
			</observers>
		</catalog_product_collection_load_before>
	</events>
</frontend>

<default>
	<llapgoch_addpcattributes>
		<general>
			<attributes></attributes>
		</general>
	</llapgoch_addpcattributes>
</default>

– We add an observer to the catalog_product_collection_load_before event. This will make sure our attributes are added to the collection wherever it’s instantiated from.
– We also add a default node as a placeholder – we’ll allow loaded attributes to be set via the admin’s system configuration.

Observer

<?php
class Llapgoch_AddProductCollectionAttributes_Model_Observer{
	public function addAttributes($observer){
		$attrs = Mage::helper('llapgoch_addpcattributes')->getAttributesToAdd();
		
		if(is_array($attrs) && count($attrs)){
			$observer->getCollection()->addAttributeToSelect($attrs);
		}
	}
}

– All we need to do is get the attributes we’d like to add to the collection and add them to the select object.

Helper

<?php
class Llapgoch_AddProductCollectionAttributes_Helper_Data extends Mage_Core_Helper_Abstract{
	const XML_PATH_PRODUCT_ATTRIBUTES = "llapgoch_addpcattributes/general/attributes";
	
	public function getAttributesToAdd(){
		$attrs = explode(" ", Mage::getStoreConfig(self::XML_PATH_PRODUCT_ATTRIBUTES));
		
		if(count($attrs)){
			return $attrs;
		}
		
		return false;
	}
		
}

– We load the attributes which can either be set in the admin area (see System Configuration) or overridden by another module’s config.xml.
– We split the attributes into an array using spaces, but this could be any character.

System Configuration

<?xml version="1.0"?>
<config>
	<tabs>
		<llapgoch translate="label">
			<label>LLAP-Goch</label>
			<sort_order>100</sort_order>
		</llapgoch>
	</tabs>
	<sections>
		<llapgoch_addpcattributes translate="label" module="llapgoch_addpcattributes">
			<label>Product Attributes</label>
			<tab>llapgoch</tab>
			
			<sort_order>1</sort_order>
			<show_in_default>1</show_in_default>
			<show_in_website>1</show_in_website>
			<show_in_store>1</show_in_store>
			
			<groups>
				<general translate="label">
					<label>General</label>

					<sort_order>10</sort_order>
					<show_in_default>1</show_in_default>
					<show_in_website>1</show_in_website>
					<show_in_store>1</show_in_store>
					<fields>
						<attributes translate="label">
							<label>Collection Attributes To Add</label>
							<comment>Space separate attributes to be added to every product collection</comment>
							<frontend_type>text</frontend_type>
							<backend_model>llapgoch_addpcattributes/system_config_backend_attributestring</backend_model>
							<sort_order>1</sort_order>
							<show_in_default>1</show_in_default>
							<show_in_website>1</show_in_website>
							<show_in_store>1</show_in_store>
						</attributes>
					</fields>
				</general>
			</groups>
		</llapgoch_addpcattributes>
	</sections>
</config>

– We just add our field as a text area to the system config.
– We use a backend model which will be used to warn the user if commas are detected in the string because we’ve chosen to split attributes on spaces.

Backend Model

<?php
class Llapgoch_AddProductCollectionAttributes_Model_System_Config_Backend_Attributestring extends Mage_Core_Model_Config_Data{
	public function _afterSave(){
		$helper = Mage::helper('llapgoch_addpcattributes');
		
		if(strpos($this->getValue(), ",") !== false){
			 Mage::getSingleton('core/session')->addNotice($helper->__('Please use spaces to separate your attribute names instead of commas'));
		}
		
		parent::_afterSave();
		
	}
}

– Checks the string for the existence of spaces and adds a notice for the user if so.

This code is available as a complete module here: https://github.com/llapgoch/magento-add-attributes-to-product-collection

Product Attributes

List of Parameters

Standard EAV Parameters

  • backend The backend model to use for saving values. These should extend Mage_Eav_Model_Entity_Attribute_Backend_Abstract
  • type default: varchar
  • frontend The frontend model to use for rendering values
  • input default: text
  • label The label to display in the admin
  • frontend_class
  • source The source model
  • required
  • user_defined
  • default The default value
  • unique
  • note Will be displayed under the input in the admin area

Catalog Product Specific
These are stored in the catalog_eav_attribute table, and the saving of them is catered for by the Mage_Catalog_Model_Resource_Setup class.

  • global The scope of the attributes. Default: Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL
  • visible default: 1
  • searchable default: 0
  • filterable default 0
  • comparable default 0
  • visible_on_front default 0
  • wysiwyg_enabled default 0
  • is_html_allowed_on_front default 0
  • visible_in_advanced_search default 0
  • filterable_in_search default 0
  • used_in_product_listing default 0
  • used_for_sort_by default 0
  • apply_to default 0
  • is_configurable default 1
  • used_for_promo_rules default 0

Attribute Sets and Groups

Attribute groups live in the eav_attribute_group table. They are assigned to attribute sets via their ID and have a default_id column to denote a default attribute group. Attribute sets can have multiple groups, and to change the order of the groups, use the sort_order column.

Getting All Attributes For An Entity (Raw SQL)

SELECT attribute_code, frontend_label
	FROM eav_attribute ea
	JOIN eav_entity_type eet 
		USING(entity_type_id)
	WHERE eet.entity_type_code = 'customer_address'

Running Setup Scripts

Adding product attributes need to be run through the Mage_Catalog_Model_Resource_Setup script. This extends Mage_Eav_Model_Entity_Setup and includes the code to deal with the extra options available in the catalog_eav_attribute table. If your setup scripts aren’t running as this and you can’t change it, if for example other setup scripts rely on a different class, you can directly instantiate it. This instantiates it with the core_setup resource which uses the default_setup connection.

$setup = new Mage_Catalog_Model_Resource_Setup('core_setup');

Adding A Product Attribute with Options

The following will add a dropdown field with the name ‘Product Overlay Logo’. It will have two options – Playline and Horse. Options added in this manner will have a blank option so that saving a product with one of these options is not required.

<?php
$installer = $this;

$installer->startSetup();

$setup = new Mage_Catalog_Model_Resource_Setup('core_setup');
$setup->addAttribute('catalog_product', 'product_overlay_logo', array(
    'attribute_set' => 'Default',
    'group'         => 'Hand Made Places',
    'input'         => 'select',
    'type'          => 'int',
    'label'         => 'Product Overlay Logo',
    'visible'       => true,
    'required'      => false,
    'visible_on_front' => true,
    'global'        => Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL,
    'sort_order' => 10,
    'option' => array(
        'values' => array(
            0 => 'Playline',
            1 => 'Horse'
        )
    )
));
$installer->endSetup();

Adding Options with Translations

Use the Store IDs as the array keys for each language translation.

<?php
$installer = $this;

$installer->startSetup();

$setup = new Mage_Eav_Model_Entity_Setup('core_setup');
$setup->addAttribute('catalog_product', 'product_overlay_logo', array(
    'attribute_set' => 'Default',
    'group'         => 'Hand Made Places',
    'input'         => 'select',
    'type'          => 'int',
    'label'         => 'Product Overlay Logo',
    'visible'       => true,
    'required'      => false,
    'visible_on_front' => true,
    'global'        => Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL,
    'sort_order' => 10,
    'option' => array(
        'values' => array(
            0 => array(
                0 => 'Playline',
                1 => 'Playline French',
                2 => 'Playline German'
            ),
            1 => array(
                0 => 'Horse',
                1 => 'Horse French',
                2 => 'Horse German'
            )
        )
    )
));
$installer->endSetup();

Updating Attributes

It’s important to note that when using the EAV’s update attribute method that the attribute code is not converted to another code — the column should be referenced as it is in the database. E.g. visible_on_front should be is_visible_on_front:

<?php
$entityTypeId = $this->getEntityTypeId('catalog_product');
$this->updateAttribute($entityTypeId, 'is_visible_on_front', 'is_visible_on_front', 0);

Updating a Set of Product Attributes

$attrData = array(
    'attribute_code_here'=> 'Value Here',
);

$storeId = 0;

$productIds = Mage::getModel('catalog/product')->getCollection()->getAllIds();
Mage::getModel("catalog/product_action")->updateAttributes(
    $productIds, 
    $attrData, 
    $storeId
);

Mage_Eav_Model_Setup – Useful Methods

  • getEntityTypeId($entity_code); Gets the Database ID of the entity type with the given code (Eg. Customer)
  • getDefaultAttributeSetId($entityTypeId); Gets the default attribute set ID for the entity
  • getDefaultAttributeGroupId($entityTypeId, $attributeSetId = null); * Gets the default group for the entity’s attribute set. If no attribute set Id is passed in then the default set is used.
  • addAttribute($entity_code, $attribute_code, array)

Adding options to an existing attribute

Sometimes, options need to be added to existing attributes:


$eav = new Mage_Eav_Model_Entity_Setup('core_setup');
$attr = Mage::getSingleton('eav/config')->getAttribute($entityTypeId, $attributeCode);
 

$eav->addAttributeOption(array(
	'value' => array( 
		'small' => array(
			0 => 'Small'
		),
		'large' => array(
			0 => 'Large'
		)
	),
	'attribute_id' => $attr->getId(),
	'order' => array(
		'small' => 100,
		'large' => 200
	)
));

Adding Attribures to a Configurable Product’s Collection










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.

<?php
$installer = $this;

$installer->startSetup();

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

$this->endSetup();
  • 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

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

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

<?php
class Iweb_Media_Model_Product_Attribute_Backend_Image extends Mage_Eav_Model_Entity_Attribute_Backend_Abstract{        
    public function beforeSave($object){
        parent::beforeSave($object);
        
        $name = $this->_getName();
        $imageData = $object->getData($name);
        
        if(isset($imageData['delete']) && (bool) $imageData['delete']){
            return $this->_removeImage($object, $imageData['value']);
        }else{
            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();
        
        if(file_exists($file)){
            unlink($file);
        }
        
        $object->setData($name, '');
    }
    
    protected function _uploadImage($object){
        $name = $this->_getName();
         
        if(!isset($_FILES[$name]) || (int) $_FILES[$name]['size'] <= 0){
            return;
        }
        
        $path = $this->_getHelper()->getPlaceholderDir();
        
        $uploader = new Varien_File_Uploader($_FILES[$name]);
        $uploader->setAllowedExtensions(array('jpg','jpeg','gif','png'));
        // Allow Magento to create a name for this upload!
        $uploader->setAllowRenameFiles(true);
        
        $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.

Collections, Models and Queries

Models

Loading an item:

<?php Mage::getModel('catalog/category')->load($id);

Loading by attribute:

<?php Mage::getModel('catalog/category')->load(‘url_key’, $key);

Collections

Adding an item collection

<?php $collection->addItem($item);

Eav’s AddAttributeToFilter

The addAttributeToFilter for EAV is interchangeable with addFieldToFilter (which non-eav collections use)

Less Than

<?php $products->addAttributeToFilter('price', array('lt' => 100));

Greated Than

<?php $products->addAttributeToFilter('price', array('gt' => 100));

In

<?php $products->addAttributeToFilter('sku', array('in' => array('1222333', 'ABC 456')));

String Equal

<?php $products->addAttributeToFilter('sku', array('in' => array('1222333', 'ABC 456')));

List of methods from Varien_Db_Adapter_PDO

<?php
$conditionKeyMap = array(
            'eq'            => "{{fieldName}} = ?",
            'neq'           => "{{fieldName}} != ?",
            'like'          => "{{fieldName}} LIKE ?",
            'nlike'         => "{{fieldName}} NOT LIKE ?",
            'in'            => "{{fieldName}} IN(?)",
            'nin'           => "{{fieldName}} NOT IN(?)",
            'is'            => "{{fieldName}} IS ?",
            'notnull'       => "{{fieldName}} IS NOT NULL",
            'null'          => "{{fieldName}} IS NULL",
            'gt'            => "{{fieldName}} > ?",
            'lt'            => "{{fieldName}} < ?",
            'gteq'          => "{{fieldName}} >= ?",
            'lteq'          => "{{fieldName}} <= ?",
            'finset'        => "FIND_IN_SET(?, {{fieldName}})",
            'regexp'        => "{{fieldName}} REGEXP ?",
            'from'          => "{{fieldName}} >= ?",
            'to'            => "{{fieldName}} <= ?",
            'seq'           => null,
            'sneq'          => null
        );
  

Performing OR Queries

ORs with the same attributes

<?php $products->addAttributeToFilter('sku', array(array('eq' => '1222333'), array('eq' => 'LLAPGOCH-SIMPLE' ));

ORs with different attributes

<?php
$collection->addAttributeToFilter(
    array(
        array('attribute'=> 'sku','like' => 'value'),
        array('attribute'=> 'otherattribute','nin' => array(1, 2, 3)),
        array('attribute'=> 'anotherattribute','like' => 'value'),
    )
);

Ordering

<?php
$collection->setOrder('attribute', Varien_Data_Collection::SORT_ORDER_DESC);
$collection->setOrder('attribute', Varien_Data_Collection::SORT_ORDER_ASC);

Useful Methods

<?php
// Gets a list of all Ids within a collection
$collection->getAllIds();

// Gets the last page number of a collection (when using the page functions)
$collection->getLastPageNumber();

// Get the pagesize of a collection
$collection->getPageSize();

// Get the size of the collection (similar to count, but the result is cached)
$collection->getSize(0);

// Getting a specific item(s)
$collection->getFirstItem();
$collection->getLastItem();
$collection->getItems();

// Clearing a collection (allows reloading)
$collection->clear();

// Converts the collection to an XML String
$collection->toXML();

Iterating Collections

Sometimes, large collections will cause memory issues in PHP. One solution, is to use Magento’s core/resource_iterator. This is the only purpose of this class:

<?php
public function output(){
        $products = Mage::getResourceModel('catalog/product_collection')

                // When adding attributes to the collection, make sure to use the the second parameter 'inner', otherwise the resource iterator won't pull them out.
        $products->addFieldToFilter('price', array('gt' => 300))
            ->addAttributeToSelect(array('name', 'image', 'url_key', 'price', 'visibility'), 'inner');

        Mage::getSingleton('core/resource_iterator')->walk(
            $products->getSelect(),
            array(array($this, 'walkCallback'))
        );

    }

    public function walkCallback($args) {
        $product = Mage::getModel('catalog/product');
        $product->setData($args['row']);

        var_dump($args['row']);
    }

  • The second join parameter must be ‘inner’ to pull the required data out using this method.

Joining Tables in a Collection

<?php
$collection->join(
	array('banner_item' => 'llapgoch_banners/banner_item'),
	'banner_item.banner_id=main_table.banner_id',
        array('banner_item_title' => 'title')
);

– The first parameter of the join, is the table to join onto. It’s an array, the key of the array being the alias that we’ll use for the table.
– The second parameter is the ON part of the query, I.e. the query to join the two tables together. Use the alias name given in the first parameter to reference the table that we’re joining on, and Magento uses the alias main_table for the table to which this collection refers.
– The third parameter is the fields which should be selected from the table we’re joining on. Leaving this parameter as null will cause Magento to select everything from the table; **This could overwrite variables with the same name from the main table if they clash **. As in the example, this parameter can be used as key value pairs, the key becoming the field’s alias in the query.

Left Joining

  • The collection object doesn’t have a left join method itself, so the collection’s select object needs to be used:
    <?php
    $collection->getSelect()->joinLeft(
    	array('banner_item' => $collection->getTable('llapgoch_banners/banner_item')),
    	'banner_item.banner_id=main_table.banner_id',
    	array('banner_item_title' => 'title')			
    );
    

  • The select object is an instance of Varien_Db_Select, this extends Zend_Db_Select, which is where this method is defined.

  • The first parameter is the table name (which needs to be resolved by the getTable method, either on the collection or connection object), this needs to be done here and not in the collection object’s join method because the collection object performs this operation for us.
  • In the example, we use an array as the first parameter instead of just the table name. The key of this array becomes the alias for the table.
  • The second parameter is the ON query. Use the alias (if defined) from the first parameter. Main table is the table for which the collection refers to.
  • The third parameter is the columns is the columns we would like to select from the joined table. Leaving this as null (default) will cause all columns to be selected. If no columns are required (E.g. we’re just joining to use the data for an aggregate query), then pass an empty array in as this parameter.

Inner Join

  • The select object also includes an inner join method, if the collection’s join method can’t be used:
    <?php
    $collection->getSelect()->joinInner(
    	array('banner_item' => $collection->getTable('llapgoch_banners/banner_item')),
    	'banner_item.banner_id=main_table.banner_id',
    	array('banner_item_title' => 'title')			
    );
    

Grouping

<?php
$collection->getSelect()->group('main_table.banner_id');

– The collection’s select object needs to be employed to group by a column. main_table references the table to which the collection refers to.

Adding aggregate queries

Method One

<?php
$collection->addExpressionFieldToSelect('num_banner_items', 'COUNT(*)', null);

– The first parameter is the fieldset name the value will be assigned to
– The second parameter is the query, aggregate or otherwise.
– The third parameter can be null, or an array of key values used in replacing values specified in the query between double curly brackets: {{value_to_be_replaced}}

Method Two 

<?php
$collection->getSelect()->columns('COUNT(*) as num_banner_items');

– This will add the parameter ‘num_banner_items’ to the collection object’s items.

Randomly Ordering a Collection

Method One

<?php
$collection->getSelect()->orderRand();

Method Two

<?php
$collection->getSelect()->order(new Zend_Db_Expr('RAND()'));

– See the note later on as to what the Zend_Db_Expr object actually is.

Adding a Straight Where Query

<?php
$collection->getSelect()->where('main_table.banner_id <= 1');

Limiting using the Select Object

  • Instead of setting the and page size on the collection, a limit can be called directly on the select object:
    <?php
    $collection->getSelect()->where('main_table.banner_id <= 1');
    

### Getting a collection’s full query ###

<?php
$collection->getSelect()->assemble()

Having

  • Use having to filter on aliases that have been set up either in the $collection->getSelect()->columns method or the $collection->addExpressionFieldToSelect method.
    <?php
    $collection->getSelect()->having('COUNT(*) = ?', 2);
    
    • Note: When using havings or aggregate queries in an admin grid, be careful as Magento will strip off all column names when it performs its pagination queries. You won’t be able to use aggregate aliases in the having, so use the aggregate function again.

The Zend_Db_Expr Object

This is an object which accepts an expression as its parameter. All the object does is cast the value passed in to a string, and then give it back in its _toString() method:

<?php
class Zend_Db_Expr
{
    protected $_expression;
    
    public function __construct($expression)
    {
        $this->_expression = (string) $expression;
    }
    public function __toString()
    {
        return $this->_expression;
    }
}

Flat Catalog

More complex queries may fail id flat catalog is toggled. These can be adapted and checked for using the following check:

  public function isProductFlatCatalogOn()
    {
        $flatHelper = Mage::helper('catalog/product_flat');
        return $flatHelper->isAvailable() && !Mage::app()->getStore()->isAdmin() && $flatHelper->isBuilt(true);
    }

Adding Values For Multiselect Attributes in Magento

Add this to your XML;

<global>
 <sales>
            <quote>
                <item>
                    <product_attributes>
                        <location />
                    </product_attributes>
                </item>
            </quote>
        </sales>
	</global>
  • This makes the product attributes accessible to the Mage_Sales_Model_Quote_Config class’ getProductAttributes() method. This reads in the sales/quote/item/product_attributes node.

  • This is called by the _assignProducts method of the “`Mage_Sales_Model_Resource_Quote_Item_Collection’s _assignProducts() method, where it adds all of the attributes to the product collection of the quote item collection.

Copy the attributes to the quote item

Unlike copying from quote items to order items, there isn’t an XML method to do this – it has to be accomplished through an observer. Add the following to the config (with the observer / method substituted):

<frontend>
    <events>
         <sales_quote_item_set_product>
             <observers>
                 <observer_name>
                        <class>model/observer</class>
                        <method>addAttributesToQuoteItem</method>
                 </observer_name>
             </observers>
         </sales_quote_item_set_product>
    </events>
</frontend>

Then create the following method for the observer above:

    public function addAttributesToQuoteItem($observer){
        $quoteItem = $observer->getQuoteItem();
        $product = $observer->getProduct();
        $quoteItem->setLocation($product->getLocation());
    }