Codegento Who Let Mage Out Of The Cage?

15Mar/119

Joining An EAV Table (With Attributes) To A Flat Table

If you have created a "custom" flat table that has a foreign key to an existing Magento EAV table, you might come across the need to join the two together. If you main table is the flat table, you will find it difficult to join the two using existing Magento code.

Below is a snippet of code that joins an EAV table (and all of its attributes) to a flat table. I have no idea about the performance of this bad boy, but I know it works.

/**
 * This assumes that the foreign key is the entity id of the eav table.
 * $collection is a collection object of a flat table.
 * $mainTableForeignKey is the name of the foreign key to the eav table.
 * $eavType is the type of entity (the entity_type_code in eav_entity_type)
 */
public function joinEavTablesIntoCollection($collection, $mainTableForeignKey, $eavType){

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

		//Use an incremented index to make sure all of the aliases for the eav attribute tables are unique.
		$index = 1;
		foreach ($attributes->getItems() as $attribute){
			$alias = 'table'.$index;
			if ($attribute->getBackendType() != 'static'){
				$table = $entityTable. '_'.$attribute->getBackendType();
				$field = $alias.'.value';
				$collection->getSelect()
				->joinLeft(array($alias => $table),
					   'main_table.'.$mainTableForeignKey.' = '.$alias.'.entity_id and '.$alias.'.attribute_id = '.$attribute->getAttributeId(),
				array($attribute->getAttributeCode() => $field)
				);
			}
			$index++;
		}
		//Join in all of the static attributes by joining the base entity table.
		$collection->getSelect()->joinLeft($entityTable, 'main_table.'.$mainTableForeignKey.' = '.$entityTable.'.entity_id');

		return $collection;

	}

The example below is joining the wishlist table to the customer's eav tables:

$wishlistCollection = Mage::getModel('wishlist/wishlist')->getCollection();
$wishlistCollection = Mage::helper('awesome')->joinEavTablesIntoCollection($wishlistCollection, 'customer_id', 'customer');
echo $wishlistCollection->getSelect()->__toString();

This produces the following SQL:

SELECT `main_table`.*, `table1`.`value` AS `confirmation`, `table3`.`value` AS `created_in`, `table4`.`value` AS `default_billing`, `table5`.`value` AS `default_shipping`, `table6`.`value` AS `dob`, `table8`.`value` AS `firstname`, `table9`.`value` AS `gender`, `table11`.`value` AS `lastname`, `table12`.`value` AS `middlename`, `table13`.`value` AS `password_hash`, `table14`.`value` AS `prefix`, `table16`.`value` AS `suffix`, `table17`.`value` AS `taxvat`, `customer_entity`.* FROM `wishlist` AS `main_table` LEFT JOIN `customer_entity_varchar` AS `table1` ON main_table.customer_id = table1.entity_id and table1.attribute_id = 16 LEFT JOIN `customer_entity_varchar` AS `table3` ON main_table.customer_id = table3.entity_id and table3.attribute_id = 3 LEFT JOIN `customer_entity_int` AS `table4` ON main_table.customer_id = table4.entity_id and table4.attribute_id = 13 LEFT JOIN `customer_entity_int` AS `table5` ON main_table.customer_id = table5.entity_id and table5.attribute_id = 14 LEFT JOIN `customer_entity_datetime` AS `table6` ON main_table.customer_id = table6.entity_id and table6.attribute_id = 11 LEFT JOIN `customer_entity_varchar` AS `table8` ON main_table.customer_id = table8.entity_id and table8.attribute_id = 5 LEFT JOIN `customer_entity_int` AS `table9` ON main_table.customer_id = table9.entity_id and table9.attribute_id = 32 LEFT JOIN `customer_entity_varchar` AS `table11` ON main_table.customer_id = table11.entity_id and table11.attribute_id = 7 LEFT JOIN `customer_entity_varchar` AS `table12` ON main_table.customer_id = table12.entity_id and table12.attribute_id = 6 LEFT JOIN `customer_entity_varchar` AS `table13` ON main_table.customer_id = table13.entity_id and table13.attribute_id = 12 LEFT JOIN `customer_entity_varchar` AS `table14` ON main_table.customer_id = table14.entity_id and table14.attribute_id = 4 LEFT JOIN `customer_entity_varchar` AS `table16` ON main_table.customer_id = table16.entity_id and table16.attribute_id = 8 LEFT JOIN `customer_entity_varchar` AS `table17` ON main_table.customer_id = table17.entity_id and table17.attribute_id = 15 LEFT JOIN `customer_entity` ON main_table.customer_id = customer_entity.entity_id

...which produces the following result:

wishlist_id 	customer_id 	shared 	sharing_code 	                    updated_at 	            confirmation 	created_in 	        default_billing 	default_shipping 	dob 	firstname 	gender 	lastname 	middlename 	password_hash 	                        prefix 	suffix 	taxvat 	entity_id 	entity_type_id 	attribute_set_id 	website_id 	email 	            group_id 	increment_id 	store_id 	created_at 	            updated_at 	            is_active
1 	            2 	            0  	    061d54b5caa53871e1cd3d1d1dc3c028 	2011-03-16 02:10:27 	NULL 	        Default Store View 	NULL 	            NULL 	            NULL 	Ben 	    NULL 	Robie 	    NULL 	    f7d9c4687f98a8c166c15c73574da95a:kF 	NULL 	NULL 	NULL 	2 	        1 	            0 	                1 	        brobie@gmail.com 	1 	  	                    1           2011-03-16 02:10:25 	2011-03-16 02:10:26 	1

Hope this helps! Join on!

Posted by Ben Robie

Comments (9) Trackbacks (0)
  1. Thanks just the hint I needed!
    My approach has been to then add a similar routine as a method to the custom collection (eg public function addEavAttributes($mainTableForeignKey, $eavCode, $attributes)) and pass that an array of attributes and an entity code to add, that keeps the result set down to only specifically requested columns and saves iterating through the entire attribute table.
    To get this to work I moved the table index variable up to a class property as well so you can chain multiple $this->addEavAttributes()->addEavAttributes() calls without collision.

  2. Oh!Terrible!

  3. I have exctlay what info I want. Check, please. Wait, it’s free? Awesome!

  4. When using this to add the product EAV model to a flat table, MySQL says there are to many tables. Since I, and probably anybody, doesn’t need all attributes, I added an array with attributes to select:

    [code]
    /**
    * This assumes that the foreign key is the entity id of the eav table.
    * $collection is a collection object of a flat table.
    * $mainTableForeignKey is the name of the foreign key to the eav table.
    * $eavType is the type of entity (the entity_type_code in eav_entity_type)
    *
    * Since MySQL can only handle a limited amount of joined tables, and to avoid
    * performance issues, limit the selected attributes! Set $select_attributes
    * to an array with the needed attribute codes.
    *
    * More info see: http://codemagento.com/2011/03/joining-an-eav-table-to-flat-table/
    *
    * @param type $collection
    * @param string $mainTableForeignKey
    * @param string $eavType
    * @param array $eavType Default null to select every attribute.
    * @return $collection
    */
    public function joinEavTablesIntoCollection($collection, $mainTableForeignKey, $eavType, $select_attributes = null)
    {

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

    //Use an incremented index to make sure all of the aliases for the eav attribute tables are unique.
    $index = 1;
    foreach ($attributes->getItems() as $attribute)
    {
    if (is_null($select_attributes) || in_array($attribute->getAttributeCode(), $select_attributes)) {
    $alias = 'table' . $index;
    if ($attribute->getBackendType() != 'static')
    {
    $table = $entityTable . '_' . $attribute->getBackendType();
    $field = $alias . '.value';
    $collection->getSelect()
    ->joinLeft(array($alias => $table), 'main_table.' . $mainTableForeignKey . ' = ' . $alias . '.entity_id and ' . $alias . '.attribute_id = ' . $attribute->getAttributeId(), array($attribute->getAttributeCode() => $field)
    );
    }
    $index++;
    }
    }
    //Join in all of the static attributes by joining the base entity table.
    $collection->getSelect()->joinLeft($entityTable, 'main_table.' . $mainTableForeignKey . ' = ' . $entityTable . '.entity_id');

    return $collection;
    }
    [/code]

  5. Thanks for the code!

    I needed to add an EAV attribute based on something other than the entity_id. I also added my code inside a resource collection so I did not need to pass the collection.

    Here’s what I came up with:

    public function joinEavAttribute($eavCode, $attributes, $bind, $filter=’entity_id’)
    {
    // get eavModel
    $entityType = Mage::getModel(‘eav/entity_type’)->loadByCode($eavCode);
    $entityAttributes = $entityType->getAttributeCollection();
    $entityTable = $this->getTable($entityType->getEntityTable());

    foreach($entityAttributes->getItems() as $entityAttribute){
    if($entityAttribute->getAttributeCode() == $filter) {
    // left join by $bind=$filter
    if($entityAttribute->getBackendType() != ‘static’)
    {
    $table = $entityTable . ‘_’ . $entityAttribute->getBackendType();
    $this->getSelect()->joinLeft(
    array(‘filter_table’ => $table),
    ‘main_table.’.$bind.’=filter_table.value and filter_table.attribute_id=’.$entityAttribute->getAttributeId(),
    array($entityAttribute->getAttributeCode() => ‘filter_table.value’)
    ); // include filter field data in case that is the attribute we wanted
    }else{
    $this->getSelect()->joinLeft(
    array(‘filter_table’ => $entityTable),
    ‘main_table.’.$bind.’=filter_table.’.$filter,
    array($entityAttribute->getAttributeCode())
    );
    }
    }
    }
    if(!is_array($attributes)) $attributes = array($attributes);
    $index=1;
    foreach($entityAttributes->getItems() as $entityAttribute){
    if($entityAttribute->getAttributeCode() == $filter) continue; // $filter field already added in previous join
    if(in_array($entityAttribute->getAttributeCode(),$attributes)) {
    $tableAlias = ‘attribute_table_’.$index++;
    $attributeAlias = array_search($entityAttribute->getAttributeCode(),$attributes);
    if(is_numeric($attributeAlias)) $attributeAlias = $entityAttribute->getAttributeCode();
    // left join by filter.entity_id=attribute.entity_id
    if($entityAttribute->getBackendType() != ‘static’)
    {
    $table = $entityTable . ‘_’ . $entityAttribute->getBackendType();
    $this->getSelect()->joinLeft(
    array($tableAlias => $table),
    ‘filter_table.entity_id=’.$tableAlias.’.entity_id and ‘.$tableAlias.’.attribute_id=’.$entityAttribute->getAttributeId(),
    array($attributeAlias => $tableAlias.’.value’)
    );
    }else{
    $this->getSelect()->joinLeft(
    array($tableAlias => $entityTable),
    ‘filter_table.entity_id=’.$tableAlias.’.entity_id’,
    array($attributeAlias => $entityAttribute->getAttributeCode())
    );
    }
    }
    }
    return $this;
    }

  6. Hi,
    I am new to magento. Can you guys please tell me where I have to add this code? Do I have to create a seperate helper file for this?

  7. But this query not working when any attribute value search in grid.

  8. Exactly what I was looking for. I remove the iteration on the attributes by loading the attribute model directly, which is a little faster:

    [code]
    //insert within the custom resource model collection
    public function joinProductAttribute($code)
    {
    $attributeId = Mage::getResourceModel('eav/entity_attribute')->getIdByCode('catalog_product', $code);
    $entityType = Mage::getModel('eav/entity_type')->loadByCode('catalog_product');
    $attribute = Mage::getModel($entityType->getAttributeModel())->load($attributeId); //$entityType->getAttributeModel()='catalog/resource_eav_attribute'
    $entityTable = $this->getTable($entityType->getEntityTable()); //$entityType->getEntityTable()='catalog/product'

    $alias = 'table' . $code;
    $table = $entityTable . '_' . $attribute->getBackendType();
    $field = $alias . '.value';
    $this->getSelect()
    ->joinLeft(array($alias => $table),
    'main_table.product_id = ' . $alias . '.entity_id AND ' . $alias . '.attribute_id = ' . $attribute->getAttributeId(),
    array($attribute->getAttributeCode() => $field)
    );

    return $this;

    }
    [/code]


Leave a comment

(required)

Trackbacks are disabled.