BLOG

Magento EAV Collections

Wed, 08/29/2012 - 15:27

How many times did you need to join attributes from Products, Categories or Customers to your custom collections, and because of the lack of such methods in the base abstract collection class, you ended quering the database to find each attribute id value, looking wich EAV table store the values for those attributes and joining each EAV table manually? While I was working on a project, I was having this exact same scenario; I was in the need to join Product and Customer attributes into my own custom collections but it didn't like me the idea of join each attribute table, so I started first to search in the internet for a solution, but most of the search results( if they were not all of them ) explained exactly what I didn't want, so I started to look into the Mage code, searching for a better solution that generates the needed joins for me, like the EAV Collections does when you call addAttributeToSelect or addAttributeToFilter methods. And the solution was just right there. Why not create a new abstract custom base class that implements those methods? And well... I'll share it with you. To use it, you must put it into your extension, and extend your collections from this class. Then in the __construct method of your Collection you must set 2 values, the EAV entity( currently you can use the class to join attributes from just one entity, but with some changes it could work to join any kind of entity ) that you will join and the FK column from you table. For example: {syntaxhighlighter brush:php} protected function _construct() { $this->_fk = 'main_table.customer_id'; $this->_init([YOUR MODEL]); $this->setEntity("customer/customer"); } {/syntaxhighlighter} And then call the addAttributeToSelect or addAttributeToFilter methods like with the EAV Collections: {syntaxhighlighter brush:php} $collection = Mage::getResourceModel('mypackage_myextension/mycollection'); $collection->addAttributeToSelect('firstname') ->addAttributeToSelect(array('lastname', 'dob', 'email)) ->addAttributeToFilter('firstname', array('like' => 'Ag%')); {/syntaxhighlighter} The abstract class: {syntaxhighlighter brush:php} abstract class MyPackage_MyExtension_Model_Resource_Collection_Abstract extends Mage_Core_Model_Resource_Db_Collection_Abstract { protected $_selectAttributes = array(); protected $_joinFields = array(); protected $_staticFields = array(); protected $_filterAttributes = array(); protected $_fk; protected $_entity; public function setEntity($entity) { if(is_string($entity)) $entity = Mage::getResourceSingleton($entity); if ($entity instanceof Mage_Eav_Model_Entity_Abstract) { $this->_entity = $entity; } elseif (is_string($entity) || $entity instanceof Mage_Core_Model_Config_Element) { $this->_entity = Mage::getModel('eav/entity')->setType($entity); } else { throw Mage::exception('Mage_Eav', Mage::helper('eav')->__('Invalid entity supplied: %s', print_r($entity, 1))); } return $this; } public function addAttributeToSelect($attribute, $joinType = false) { if (is_array($attribute)) { Mage::getSingleton('eav/config')->loadCollectionAttributes($this->getEntity()->getType(), $attribute); foreach ($attribute as $a) { $this->addAttributeToSelect($a, $joinType); } return $this; } if ($joinType !== false) { $this->_addAttributeJoin($attribute, $joinType); } elseif ('*' === $attribute) { $attributes = $this->getEntity() ->loadAllAttributes() ->getAttributesByCode(); foreach ($attributes as $attrCode=>$attr) { $this->_selectAttributes[$attrCode] = $attr->getId(); } } else { if (isset($this->_joinAttributes[$attribute])) { $attrInstance = $this->_joinAttributes[$attribute]['attribute']; } else { $attrInstance = Mage::getSingleton('eav/config') ->getCollectionAttribute($this->getEntity()->getType(), $attribute); } if (empty($attrInstance)) { throw Mage::exception( 'Mage_Eav', Mage::helper('eav')->__('Invalid attribute requested: %s', (string)$attribute) ); } $this->_selectAttributes[$attrInstance->getAttributeCode()] = $attrInstance->getId(); } return $this; } public function addAttributeToFilter($attribute, $condition = null, $joinType = 'inner') { if ($attribute === null) { $this->getSelect(); return $this; } if (is_numeric($attribute)) { $attribute = $this->getEntity()->getAttribute($attribute)->getAttributeCode(); } else if ($attribute instanceof Mage_Eav_Model_Entity_Attribute_Interface) { $attribute = $attribute->getAttributeCode(); } if (is_array($attribute)) { $sqlArr = array(); foreach ($attribute as $condition) { $sqlArr[] = $this->_getAttributeConditionSql($condition['attribute'], $condition, $joinType); } $conditionSql = '('.implode(') OR (', $sqlArr).')'; } else if (is_string($attribute)) { if ($condition === null) { $condition = ''; } $conditionSql = $this->_getAttributeConditionSql($attribute, $condition, $joinType); } if (!empty($conditionSql)) { $this->getSelect()->where($conditionSql, null, Varien_Db_Select::TYPE_CONDITION); } else { Mage::throwException('Invalid attribute identifier for filter ('.get_class($attribute).')'); } return $this; } protected function _getAttributeConditionSql($attribute, $condition, $joinType = 'inner') { if (isset($this->_joinFields[$attribute])) { return $this->_getConditionSql($this->_getAttributeFieldName($attribute), $condition); } if (isset($this->_staticFields[$attribute])) { return $this->_getConditionSql($this->getConnection()->quoteIdentifier('e.' . $attribute), $condition); } // process linked attribute if (isset($this->_joinAttributes[$attribute])) { $entity = $this->getAttribute($attribute)->getEntity(); $entityTable = $entity->getEntityTable(); } else { $entity = $this->getEntity(); $entityTable = 'e'; } if ($entity->isAttributeStatic($attribute)) { $conditionSql = $this->_getConditionSql( $this->getConnection()->quoteIdentifier('e.' . $attribute), $condition ); } else { $this->_addAttributeJoin($attribute, $joinType); if (isset($this->_joinAttributes[$attribute]['condition_alias'])) { $field = $this->_joinAttributes[$attribute]['condition_alias']; } else { $field = $this->_getAttributeTableAlias($attribute) . '.value'; } $conditionSql = $this->_getConditionSql($field, $condition); } return $conditionSql; } protected function _addAttributeJoin($attributeCode, $joinType = 'inner') { if (!empty($this->_filterAttributes[$attributeCode])) { return $this; } $adapter = $this->getConnection(); $attrTable = $this->_getAttributeTableAlias($attributeCode); if (isset($this->_joinAttributes[$attributeCode])) { $attribute = $this->_joinAttributes[$attributeCode]['attribute']; $entity = $attribute->getEntity(); $entityIdField = $entity->getEntityIdField(); $fkName = $this->_joinAttributes[$attributeCode]['bind']; $fkAttribute = $this->_joinAttributes[$attributeCode]['bindAttribute']; $fkTable = $this->_getAttributeTableAlias($fkName); if ($fkAttribute->getBackend()->isStatic()) { if (isset($this->_joinAttributes[$fkName])) { $fk = $fkTable . '.' . $fkAttribute->getAttributeCode(); } else { $fk = 'e.' . $fkAttribute->getAttributeCode(); } } else { $this->_addAttributeJoin($fkAttribute->getAttributeCode(), $joinType); $fk = $fkTable . '.value'; } $pk = $attrTable . '.' . $this->_joinAttributes[$attributeCode]['filter']; } else { $entity = $this->getEntity(); $entityIdField = $entity->getEntityIdField(); $attribute = $entity->getAttribute($attributeCode); $fk = $this->_fk; $pk = $attrTable . '.' . $entityIdField; } if (!$attribute) { throw Mage::exception('Mage_Eav', Mage::helper('eav')->__('Invalid attribute name: %s', $attributeCode)); } if ($attribute->getBackend()->isStatic()) { $attrFieldName = $attrTable . '.' . $attribute->getAttributeCode(); } else { $attrFieldName = $attrTable . '.value'; } $fk = $adapter->quoteColumnAs($fk, null); $pk = $adapter->quoteColumnAs($pk, null); $condArr = array("$pk = $fk"); if (!$attribute->getBackend()->isStatic()) { $condArr[] = $this->getConnection()->quoteInto( $adapter->quoteColumnAs("$attrTable.attribute_id", null) . ' = ?', $attribute->getId()); } /** * process join type */ $joinMethod = ($joinType == 'left') ? 'joinLeft' : 'join'; $this->_joinAttributeToSelect($joinMethod, $attribute, $attrTable, $condArr, $attributeCode, $attrFieldName); $this->removeAttributeToSelect($attributeCode); $this->_filterAttributes[$attributeCode] = $attribute->getId(); /** * Fix double join for using same as filter */ $this->_joinFields[$attributeCode] = array( 'table' => '', 'field' => $attrFieldName, ); return $this; } protected function _getAttributeTableAlias($attributeCode) { return 'at_' . $attributeCode; } protected function _joinAttributeToSelect($method, $attribute, $tableAlias, $condition, $fieldCode, $fieldAlias) { $this->getSelect()->$method( array($tableAlias => $attribute->getBackend()->getTable()), '('.implode(') AND (', $condition).')', array($fieldCode => $fieldAlias) ); return $this; } public function removeAttributeToSelect($attribute = null) { if ($attribute === null) { $this->_selectAttributes = array(); } else { unset($this->_selectAttributes[$attribute]); } return $this; } protected function _getAttributeFieldName($attributeCode) { $attributeCode = trim($attributeCode); if (isset($this->_joinAttributes[$attributeCode]['condition_alias'])) { return $this->_joinAttributes[$attributeCode]['condition_alias']; } if (isset($this->_staticFields[$attributeCode])) { return sprintf('e.%s', $attributeCode); } if (isset($this->_joinFields[$attributeCode])) { $attr = $this->_joinFields[$attributeCode]; return $attr['table'] ? $attr['table'] . '.' . $attr['field'] : $attr['field']; } $attribute = $this->getAttribute($attributeCode); if (!$attribute) { throw Mage::exception('Mage_Eav', Mage::helper('eav')->__('Invalid attribute name: %s', $attributeCode)); } if ($attribute->isStatic()) { if (isset($this->_joinAttributes[$attributeCode])) { $fieldName = $this->_getAttributeTableAlias($attributeCode) . '.' . $attributeCode; } else { $fieldName = 'e.' . $attributeCode; } } else { $fieldName = $this->_getAttributeTableAlias($attributeCode) . '.value'; } return $fieldName; } public function getAttribute($attributeCode) { if (isset($this->_joinAttributes[$attributeCode])) { return $this->_joinAttributes[$attributeCode]['attribute']; } return $this->getEntity()->getAttribute($attributeCode); } /** * Get collection's entity object * * @return Mage_Eav_Model_Entity_Abstract */ public function getEntity() { if (empty($this->_entity)) { throw Mage::exception('Mage_Eav', Mage::helper('eav')->__('Entity is not initialized')); } return $this->_entity; } public function addExpressionAttributeToSelect($alias, $expression, $attribute) { // validate alias if (isset($this->_joinFields[$alias])) { throw Mage::exception( 'Mage_Eav', Mage::helper('eav')->__('Joint field or attribute expression with this alias is already declared') ); } if (!is_array($attribute)) { $attribute = array($attribute); } $fullExpression = $expression; // Replacing multiple attributes foreach ($attribute as $attributeItem) { if (isset($this->_staticFields[$attributeItem])) { $attrField = sprintf('e.%s', $attributeItem); } else { $attributeInstance = $this->getAttribute($attributeItem); if ($attributeInstance->getBackend()->isStatic()) { $attrField = 'e.' . $attributeItem; } else { $this->_addAttributeJoin($attributeItem, 'left'); $attrField = $this->_getAttributeFieldName($attributeItem); } } $fullExpression = str_replace('{{attribute}}', $attrField, $fullExpression); $fullExpression = str_replace('{{' . $attributeItem . '}}', $attrField, $fullExpression); } $this->getSelect()->columns(array($alias => $fullExpression)); $this->_joinFields[$alias] = array( 'table' => false, 'field' => $fullExpression ); return $this; } } {/syntaxhighlighter} Hope you like it!