Join EAV attributes to a flat table collection in Magento

Joining an EAV based attribute to a flat table collection in Magento can be tricky sometimes (and very repetitive when joining several attributes).

A function to join EAV attributes

To simplify adding EAV attributes to a flat collection, I have written the following function:

/**
 * @param $attrCode                 (the attribute code, for example 'name' or 'sku')
 * @param $entityType               (catalog_product, catalog_category, customer, customer_address)
 * @param $joinField                (the field to join the attribute value entity_id on (for example main_table.product_id))
 * @param $collection               (the collection to join to)
 * @param int $storeID              (store ID to filter the attribute values by store)
 * @return $this
 */
public function joinEAV($attrCode, $entityType, $joinField, $collection, $storeID=0)
{
    //retrieve the attribute object
    $attr = Mage::getModel("eav/config")->getAttribute($entityType, $attrCode);
    $attrID = $attr->getAttributeId();
    $attrTable = $attr->getBackendTable();
    if ($attr->getBackendType() == 'static') {
	    $joinSql = "{$attrTable}.entity_id={$joinField}";
	    //don't use an alias for static table, use table name
	    $alias = $attrTable;
	    //if static join all fields
	    $fields = '*';
	    //create alias for current field to add as expression attribute
	    $fieldAlias = $entityType . '_' . $attrCode;
    }else{
	    //for regular attribute, create alias for table (table might be joined multiple times)
	    $alias = $entityType . '_' . $attrCode;
	    $dbRow = 'value';
	    $joinSql = "{$alias}.entity_id={$joinField} AND {$alias}.store_id={$storeID} AND {$alias}.attribute_id={$attrID}";
	    //if non-static, create alias for value field in join
	    $fields = array($alias => "{$alias}.{$dbRow}");
    }
    //if field or static table is already joined, don't join again
    if (stristr($collection->getSelectSql(), "`{$alias}`")) {
	    $dontJoin = true;
    }
    //if field is static, create field alias for display in grid / collection
    if ($attr->getBackendType() == 'static') {
	    $collection->addExpressionFieldToSelect($fieldAlias,"{$attrTable}.{$attrCode}");
    }
    if ($dontJoin) {
	    return $this;
    }
    //join select
    $collection
	    ->getSelect()
	    ->joinLeft(
		array($alias => $attrTable),
		$joinSql,
		$fields
	);
    return $this;
}

The function takes the following arguments:

/**
 * @param $attrCode                 (the attribute code, for example 'name' or 'sku')
 * @param $entityType               (catalog_product, catalog_category, customer, customer_address)
 * @param $joinField                (the field to join the attribute value entity_id on (for example main_table.product_id))
 * @param $collection               (the collection to join to)
 * @param int $storeID              (store ID to filter the attribute values by store)
 * @return $this
 */

The alias (or index when creating a grid column), is the concatenated string of the entity type and the attribute code. For example the alias for 'sku' would become 'catalog_product_sku'. This is to prevent conflicts when joining two attributes with the same code but with different entity types.

Retrieving the attribute value from the collection

When looping through a collection you can retrieve the attribute value as follows:

foreach($collection as $item){
    $item->getCatalogProductSku();
    //or
    $item->getData('catalog_product_sku');
}

Adding the attribute as a grid row in the Magento admin

To add the joined attribute to a Magento admin grid, simply use the concatenated value of the entity type and the attribute name as index. For example when joining the product name:

$grid->addColumn('your_column_name',array(
	 'label' => $this->__('Your Label'),
	 'index' => 'catalog_product_name'
    )
)

When working with a Magento grid, and wanting to filter or sort on the joined EAV attribute, it will be nessesary to specify a "filter index" for the joined attribute.

The filter index consists of the following:

table_alias.column_name

Or in case of the 'static' SKU attribute, when using the above mentioned function:

catalog_product_entity.sku

When joining a non 'static' attribute like product name however, it would be:

catalog_product_name.value

In case of the product name attribute the result would look like this:

$grid->addColumn('your_column_name',array(
	 'label' => $this->__('Product Name'),
	 'index' => 'catalog_product_name',
	 'filter_index' => 'catalog_product_name.value'
    )
)

It would be of course great if we can retrieve the correct index and filter index automatically without too much hassle. For this I wrote the following additional functions:

/**
 * Get index for grid attribute
 *
 * @param $attr
 * @param $entityType
 * @return string
 */
public function getAttrIndex($attr, $entityType){
    return ($entityType . '_' . $attr);
}
/**
 * Get Filter Index for grid attribute
 *
 * @param $attr
 * @param $entityType
 * @return string
 */
public function getAttrFilterIndex($attr, $entityType)
{
    $attr = Mage::getModel("eav/config")->getAttribute($entityType, $attr);
    if ($attr->getBackendType() == 'static') {
	    //if static use default entity table
	    $index = $attr->getBackendTable() . '.' . $attr->getAttributeCode();
    }else{
	    //if non-static use generated table alias value field
	    $attrCode = $attr->getAttributeCode();
	    $index = "{$entityType}_{$attrCode}.value";
    }
    return $index;
}

Mashing it all together

The two above mentioned functions can be added to your collection model class, to the used grid class, or if you reuse the code multiple times, it can be added to a helper class, which can then in turn be called from several grid or model classes.

We can optimize the function a bit more: By using the Magento registry to store the joined attribute data we can minimize DB calls.

As an example here are the combined functions in a helper class:

<?php
class Company_Module_Helper_Eav extends Mage_Core_Helper_Abstract
{
    /**
     * @param $attrCode                 (the attribute code, for example 'name' or 'sku')
     * @param $entityType               (catalog_product, catalog_category, customer, customer_address)
     * @param $joinField                (the field to join the attribute value entity_id on (for example main_table.product_id))
     * @param $collection               (the collection to join to)
     * @param int $storeID              (store ID to filter the attribute values by store)
     * @return $this
     */
    public function joinEAV($attrCode, $entityType, $joinField, $collection, $storeID=0)
    {
        $attr = $this->getCachedAttr($entityType, $attrCode);
        $attrID = $attr->getAttributeId();
        $attrTable = $attr->getBackendTable();
        if ($attr->getBackendType() == 'static') {
            $joinSql = "{$attrTable}.entity_id={$joinField}";
            //don't use an alias for static table, use table name
            $alias = $attrTable;
            //if static join all fields
            $fields = '*';
            //create alias for current field to add as expression attribute
            $fieldAlias = $entityType . '_' . $attrCode;
        }else{
            //for regular attribute, create alias for table (table might be joined multiple times)
            $alias = $entityType . '_' . $attrCode;
            $dbRow = 'value';
            $joinSql = "{$alias}.entity_id={$joinField} AND {$alias}.store_id={$storeID} AND {$alias}.attribute_id={$attrID}";
            //if non-static, create alias for value field in join
            $fields = array($alias => "{$alias}.{$dbRow}");
        }
        //if field or static table is already joined, don't join again
        if (stristr($collection->getSelectSql(), "`{$alias}`")) {
            $dontJoin = true;
        }
        //if field is static, create field alias for display in grid / collection
        if ($attr->getBackendType() == 'static') {
            $collection->addExpressionFieldToSelect($fieldAlias,"{$attrTable}.{$attrCode}");
        }
        if ($dontJoin) {
            return $this;
        }
        //join select
        $collection
            ->getSelect()
            ->joinLeft(
                array($alias => $attrTable),
                $joinSql,
                $fields
            );
        return $this;
    }
    /**
     * Get index for grid attribute
     *
     * @param $attrCode
     * @param $entityType
     * @return string
     */
    public function getAttrIndex($attrCode, $entityType){
        return ($entityType . '_' . $attrCode);
    }
    /**
     * Get Filter Index for grid attribute
     *
     * @param $attrCode
     * @param $entityType
     * @return string
     */
    public function getAttrFilterIndex($attrCode, $entityType)
    {
        $attr = $this->getCachedAttr($entityType, $attrCode);
        if ($attr->getBackendType() == 'static') {
            //if static use default entity table
            $index = $attr->getBackendTable() . '.' . $attr->getAttributeCode();
        }else{
            //if non-static use generated table alias value field
            $attrCode = $attr->getAttributeCode();
            $index = "{$entityType}_{$attrCode}.value";
        }
        return $index;
    }
    /**
     * Returns cached attribute if available, else loads attribute
     *
     * @param $entityType
     * @param $attrCode
     * @return Mage_Eav_Model_Entity_Attribute_Abstract
     */
    public function getCachedAttr($entityType,$attrCode)
    {
        //retrieve the attribute object
        if ($cachedAttr = Mage::registry("eavjoin_{$entityType}_{$attrCode}")) {
            $attr = $cachedAttr;
        }else{
            $attr = Mage::getModel("eav/config")->getAttribute($entityType, $attrCode);
            Mage::register("eavjoin_{$entityType}_{$attrCode}",$attr);
        }
        return $attr;
    }
}

Using these functions it becomes really straightforward to join EAV attributes to a flat collection in Magento:

For the sake of the example, let's say the above functions are added to a Helper class like above.

The collection code:

//get collection
$collection = Mage::getModel('company_module/model')->getCollection();
//join SKU
Mage::helper('company_module/eav')->joinEAV('sku', 'catalog_product', 'main_table.product_id', $collection);
//join Product Name
Mage::helper('company_module/eav')->joinEAV('name', 'catalog_product', 'main_table.product_id', $collection);

The code to add grid columns:

//product sku
$this->addColumn("sku", array(
    "header" => Mage::helper("company_module")->__("SKU"),
    "index" => 'catalog_product_sku',
    "filter_index" => $this->getAttrFilterIndex('sku','catalog_product'),
));
//product name
$this->addColumn("product_name", array(
    "header" => Mage::helper("company_module")->__("Product name"),
    "index" => $this->getAttrIndex('name','catalog_product'),
    "filter_index" => $this->getAttrFilterIndex('name','catalog_product'),
    "width" => "320px",
));

I hope the above Magento examples and code have been useful to you. Do you know another (maybe better?) way to handle this, or have any questions, please let me know in the comments below!