Magento 1

Loading a Product Collection’s media_gallery attribute

This is useful for loading a product’s gallery on the something like the listing page.

Magento 1

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'){

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

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

        // 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');

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

– 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');

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

		array('customer_address' => $collection->getTable('customer/address_entity')),

	// 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');

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


	$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

Magento 1

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.




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


class Llapgoch_AddProductCollectionAttributes_Model_Observer{
	public function addAttributes($observer){
		$attrs = Mage::helper('llapgoch_addpcattributes')->getAttributesToAdd();
		if(is_array($attrs) && count($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.


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));
			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"?>
		<llapgoch translate="label">
		<llapgoch_addpcattributes translate="label" module="llapgoch_addpcattributes">
			<label>Product Attributes</label>
				<general translate="label">

						<attributes translate="label">
							<label>Collection Attributes To Add</label>
							<comment>Space separate attributes to be added to every product collection</comment>

– 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

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

– 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: