Last week, we took a look into Magento tutorial 18 – how to override core files in Magento and learned overriding principle in Magento as well as overriding model class, helper class and resource model in Magento.
Now, to keep on our Magento tutorial series, we will discover Entity Attribute Value (EAV) in Magento. Lesson 19 will cover three sections and last for three hours:
- EAV Model
- Working with EAV Model
- Working with collections and EAV collections in Magento
1. EAV Model
1.1. What is Entity Attribute Value
In common sense, Entity Attribute Value model is a data model to describe entities where the number of attributes (properties and parameters) which can be used to describe them are potentially vast, but the number of attributes which will actually apply to a given entity are relatively modest.
In mathematics, this model is known as a sparse matrix. EAV is also known as object–attribute–value model, vertical database model and open schema.
There are certain cases where an EAV schematic is an optimal approach to data modelling for a problem domain. However, in many cases where data can be modelled in statically relational terms an EAV based approach is an anti-pattern which can lead to longer development times, poor use of database resources and more complex queries when compared to a relationally-modelled data schema.
In Magento, EAV stands for Entity, Attribute and Value. Let’s look into each part and try to understand them a little better.
- Entity: The entity represents Magento data items such as products, categories, customers and orders. Each entity (product, category, etc.) will have its own entity record in the database.
- Attribute: The attributes represent data items that belong to an entity. For example, the product entity has attributes such as name, price, status and many more.
- Value: The value is the simplest to understand as it is simply a value linked to an attribute. For better understanding, let’s consider the product entity. Each product entity will have a series of attributes, one of them is the name attribute. Each product will then have a value for the name attribute (and all other attributes). This might not be clear yet but keep on reading!
1.2. Entity table structure
This data representation is analogous to space-efficient methods of storing a sparse matrix, where only non-empty values are stored. In an EAV data model, each attribute-value pair is a fact describing an entity, and a row in an EAV table stores a single fact. EAV tables are often described as “long and skinny”: “long” refers to the number of rows, “skinny” refers to the few columns.
Data is recorded as three columns:
- The entity: The item being described.
- The attribute or parameter: A foreign key into a table of attribute definitions. At the very least, the attribute definitions table would contain the following columns: an attribute ID, attribute name, description, data type, and columns assisting input validation, e.g., maximum string length and regular expression, set of permissible values, etc.
- The value of the attribute.
1.3. Why is Entity Attribute Value used
EAV is used because it is much more scalable than the usual normalised database structure. Developers can add attributes to any entity (product, category, customer, order etc) without modifying the core database structure. When a custom attribute is added, no logic must be added to force Magento to save this attribute because it is already built into the model; as long as the data is set and the attribute has been created, the model will be saved!
1.4. What is the downside of EAV
A major downside of EAV is its speed. With entity data being so fragmented, creating a whole entity record requires a lot of expensive table joins. Fortunately, the team at Varien have implemented an excellent cache system, allowing developers to cache information that doesn’t often change.
Another problem with EAV is its learning curve, which means that a lot of junior developers give up before they can truly see the simplicity of it. While there is no quick fix for this, hopefully this article will help people start overcoming this problem.
2. Working with EAV Model
The Achilles heel of Entity Attribute Value is the difficulty of working with large volumes of EAV data. It is often necessary to transiently or permanently inter-convert between columnar and row-or EAV-modeled representations of the same data. This can be both error-prone if done manually as well as CPU-intensive. Generic frameworks that utilize attribute and attribute-grouping metadata address the former but not the latter limitation; their use is more or less mandated in the case of mixed schemas that contain a mixture of conventional-relational and EAV data, where the error quotient can be very significant.
Obviously, no matter what approaches you take, querying EAV will never be as fast as querying standard column-modeled relational data, in much the same way that access of elements in sparse matrices are not as fast as those on non-sparse matrices if the latter fit entirely into main memory. (Sparse matrices, represented using structures such as linked lists, require list traversal to access an element at a given X-Y position, while access to elements in matrices represented as 2-D arrays can be performed using fast CPU register operations.). If, however, you chose the EAV approach correctly for the problem that you were trying to solve, this is the price that you pay; in this respect, EAV modeling is an example of a space (and schema maintenance) versus CPU-time tradeoff.
2.1. How Entity Attribute Value data storage work in Magento
The EAV data storage in Magento seems quite complicated with the separated data for each store. There are some data storage tables:
- EAV entity type: stores the entity type including the information of model for the entity or the default attribute set.
- EAV entity: Contains an eav_entity table storing objects of a certain entity type
- EAV entity attribute: Attributes are divided into groups (one group may have a lot of attributes and one attribute may be in a lot of groups). An attribute set includes the number of groups. An object has an attribute set.
- EAV entity value: Magento optimizes the data storage by providing value tables corresponding to the data types such as: eav_entity_datetime, eav_entity_varchar, eav_entity_int…
2.2. Entity Attribute Value data access process in Magento
Exporting EAV data requires querying continuously from many tables, so model carries out mapping data on many database tables.
- Read data: To read data from database to object, model needs to follow these steps:
- Read data from main table or entity table
- Define set of object attributes
- Read out values of attributes for objects
- Change values of attributes
- Map data in object
- Burn data: Burning EAV data to database process as follows:
- Get data mapped in the objects
- Change the values of attributes
- Save data in main table or entity table
- Save data in values of attributes table
3. Working with collections and EAV collections in Magento
Dealing with the Collection
A collection is a Model type containing other Models, it is frequently used in Magento to handle product lists (i.e. from a category or a bundle option).
TO DO 1: Explain how Magento implements a collection
Use this to explain how Magento implements a collection by looking at code in a model, so that people can learn to write their own collections
This is a simple example of loading some product collections from a category and ordering them on their product name using Magento’s API.
$collection = Mage::getModel('catalog/category')->load($categoryId)
->getProductCollection()
->addAttributeToSort('name', 'ASC');
To sort multiple Fields, you can change calls to the Collection’s method addAttributeToSort (recommended)
$collection = Mage::getModel('catalog/product')->getCollection()
->addAttributeToSort('name', 'ASC')
->addAttributeToSort('sku', 'ASC')
->addAttributeToSort('price', 'ASC');
TO DO 2: Use Magento’s API use cases, not Zend_Db_Select ones
You can pass IF/THEN statements, but be sure to use the proper quotation of your table’s fields.
$collection = Mage::getModel('catalog/product')->getCollection();
$collection->getSelect()->order( array('IF(`order`>0, `order`, 9999)ASC','name ASC', 'sku ASC') );
In this example, the table will be sorted by the order field, by name, and by SKU where order is greater than zero, followed by order being equal to or less than zero, all ascending.
TO DO 3: Join Table
Use the following code to add SQL joins to a select:
$collection = Mage::getModel('module/model_name')->getCollection();
$collection->getSelect()->join(
array('table_alias'=>$this->getTable('module/table_name')),
'main_table.foreign_id = table_alias.primary_key',
array('table_alias.*'), 'schema_name_if_different');
In this example, the join method takes an array of alias⇒table_name key pairs, then a standard join clause uses `main_table` to refer to the original selection. Next, an array of fields is retrieved in the join (defaults to *), a different schema can be specified as a last parameter.
→join defaults to an inner join, others can be used:
→joinInner() →joinLeft() →joinRight() →joinFull() →joinCross() →joinNatural()
See lib/Zend/Db/Select.php for source.
[yellowbox] Exclusive information: Worried about messy stock? Magento Inventory Management helps you get rid of wasting hours on counting stock and maximize time for selling! [/yellowbox]TO DO 4: Filter in collection
Filtering in collection is the same as using ‘where’ condition in SQL. To filter in collection, you use function addFieldToFilter(‘field’,’condition’) or addAttributeToFilter(‘field’,’condition’)
Look at the code below:
$collection = Mage::getModel('sales/order')->getCollection();
$collection->addFieldToFilter('entity_id',1);
It’s is the same as in SQL:
SELECT `main_table`.* FROM `sales_flat_order` AS `main_table` WHERE (main_table.entity_id = '1')
It’s very simple to filter in collection. Here is the filter condition codes:
| Condition code | SQL equivalent | Description |
| eq | = | |
| neq | != | |
| like | LIKE | |
| nlike | NOT LIKE | |
| in | IN | |
| nin | NOT IN | |
| is | IS | |
| notnull | NOT NULL | |
| null | NULL | |
| moreq | >= | |
| lt | < | |
| gt | > | |
| lteq | <= | |
| gteq | >= | |
| findset | FIND_IN_SET() | |
| from | >= | For use with date |
| to | <= | For use with date |
| date | Optional flag for use with from/to to specify that comparison value should first be converted to a date | |
| datetime | Optional flag for use with from/to to specify that comparison value should first be converted to a date-time |
Below are some examples showing you how to use those condition codes. In these examples, I will use the product collection. Remember that function addAttributeToFilter(‘field’,’condition’) can be used with EAV Collection only.
$collection = Mage::getModel('catalog/product')->getCollection()
->addAttributeToSelect('*');
$collection->addAttributeToFilter('entity_id', array(
'in' => array(1, 2, 3),
));
$collection->addAttributeToFilter('created_at', array(
'from' => '2000-09-10',
));
$collection->addAttributeToFilter('created_at', array(
'from' => '10 September 2000',
'to' => '11 September 2000',
'date' => true, // specifies conversion of comparison values
));
$collection->addAttributeToFilter('sku', array('notnull' => true, ));
// Add OR condition:
$collection->addAttributeToFilter(array(
array(
'attribute' => 'entity_id',
'in' => array(1, 2, 3),
),
array(
'attribute' => 'created_at',
'from' => '2000-09-10',
),
));
Collection Manipulation Method
Varien_Data_Collection
| addFilter($field, $value, $type = ‘and’) |
| addItem(Varien_Object $item) |
| clear() |
| count() |
| distinct($flag) |
| each($obj_method, $args=array()) |
| getAllIds() |
| getColumnValues($colName) |
| getCurPage($displacement = 0) |
| getFirstItem() |
| getFlag($flag) |
| getItemByColumnValue($column, $value) |
| getItemById($idValue) |
| getItems() |
| getItemsByColumnValue($column, $value) |
| getIterator() |
| getLastItem() |
| getLastPageNumber() |
| getNewEmptyItem() |
| getPageSize() |
| getSize() |
| hasFlag($flag) |
| isLoaded() |
| loadData($printQuery = false, $logQuery = false) |
| load($printQuery = false, $logQuery = false) |
| removeItemByKey($key) |
| setCurPage($page) |
| setDataToAll($key, $value=null) |
| setFlag($flag, $value = null) |
| setOrder($field, $direction = self::SORT_ORDER_DESC) |
| setPageSize($size) |
| toArray($arrRequiredFields = array()) |
| toOptionArray() |
| toOptionHash() |
| toXml() |
Varien_Data_Collection_Db
Inherits above methods for Varien_Data_Collection, and in addition (re)defines the following:
| addFieldToFilter($field, $condition=null) |
| addOrder($field, $direction = self::SORT_ORDER_DESC) |
| distinct($flag) |
| getConnection() |
| getData() |
| getIdFieldName() |
| getSelect() |
| getSelectCountSql() |
| getSize() |
| loadData($printQuery = false, $logQuery = false) |
| load($printQuery = false, $logQuery = false) |
| printLogQuery($printQuery = false, $logQuery = false, $sql = null) |
| resetData() |
| setConnection($conn) |
| setOrder($field, $direction = self::SORT_ORDER_DESC) |
| unshiftOrder($field, $direction = self::SORT_ORDER_DESC) |
Mage_Eav_Model_Entity_Collection
Inherits above methods for Varien_Data_Collection_Db, and in addition (re)defines the following:
| addAttributeToFilter($attribute, $condition=null, $joinType=’inner’) | adds WHERE clause on $attribute specified by $condition |
| addAttributeToSelect($attribute, $joinType=false) | gets the value for $attribute in the SELECT clause; specify * to get all attributes (i.e. to execute SELECT *) |
| addAttributeToSort($attribute, $dir=’asc’) | adds ORDER BY clause on $attribute |
| addEntityTypeToSelect($entityType, $prefix) | doesn’t seem to do anything – don’t use |
| addExpressionAttributeToSelect($alias, $expression, string|array $attribute) | adds SQL expression $expression, using $alias, to SELECT clause (typically containing aggregate functions such as SUM(), COUNT()); when $attribute specifies a single attribute as a string, $expression can reference the attribute as simply {{attribute}}, but when passing an array of attributes, each attribute must be referenced in $expression by the name of the specific attribute; N.B. use with groupByAttribute() when including aggregate functions q.v. |
| addFieldToFilter($attribute, $condition=null) | alias for addAttributeToFilter() |
| addItem(Varien_Object $object) | adds an object to the collection |
| addStaticField($field) | specifies $field as being static, i.e. as existing on the entity table itself, rather than in the EAV table |
| delete() | deletes all entities in the collection |
| exportToArray() | returns collection data as a 2D array |
| getAllIds($limit=null, $offset=null) | returns array of all entity IDs selected by current WHERE clause (optionally specifying $limit and $offset) |
| getAllIdsSql() | not recommended – don’t use |
| getAttribute($attributeCode) | for specified $attributeCode, returns Mage_Eav_Model_Entity_Attribute_Abstract object of the entity object used by the collection (i.e. calls getAttribute($attributeCode) on the entity object and returns the result) |
| getEntity() | returns Mage_Eav_Model_Entity_Abstract object used by collection for attributes |
| getLoadedIds() | returns array of IDs of currently loaded entities |
| getResource() | returns Mage_Core_Model_Resource_Abstract instance; actually an alias for getEntity() q.v. (an entity is a special type of resource, that is, Mage_Eav_Model_Entity_Abstract extends Mage_Core_Model_Resource_Abstract) |
| getRowIdFieldName() | returns field name of ID attribute for entities in the collection |
| getTable($table) | alias for Mage::getSingleton(‘core/resource’)->getTableName($table) |
| groupByAttribute($attribute) | adds $attribute to GROUP BY clause |
| importFromArray($arr) | imports 2D array into collection as objects |
| joinAttribute($alias, Mage_Eav_Model_Entity_Attribute_Abstract|string $attribute, string $bind, string $filter=null, string $joinType=’inner’, $storeId=null) | joins another entity and adds attribute from joined entity, using $alias, to SELECT clause; $attribute can specify attribute object, or string in format [entity]/[attribute]; $bind specifies attribute of the main entity on which to make join; $filter specifies primary key of the joined entity on which to make join (normally defaults to entity_id); $joinType should be inner or left |
| joinField($alias, $table, $field, $bind, $cond=null, $joinType=’inner’) | joins regular table field using an attribute as foreign key |
| joinTable($table, $bind, $fields=null, $cond=null, $joinType=’inner’) | joins table $table |
| load($printQuery = false, $logQuery = false) | runs query and load data into collection; specify $printQuery as true to print SQL for debugging |
| removeAttributeToSelect($attribute=null) | removes $attribute from SELECT clause; specify null to remove all attributes |
| save() | saves all entities in the collection |
| setEntity(string|object $entity) | sets entity object (i.e. Mage_Eav_Model_Entity_Abstract) used by collection for attributes |
| setObject(Varien_Object $object=null) | sets template object for the collection |
| setOrder(string| $attribute, $dir=’desc’) | alias for addAttributeToSort() q.v., identical except that it can accept array of attributes, and default $dir is desc; N.B. overrides parent function |
| setPage($pageNum, $pageSize) | sets LIMIT clause by specifying page number (one-indexed) and number of records per page; N.B. equivalent to calling setCurPage($pageNum) and setPageSize($pageSize) q.q.v. |
| setRowIdFieldName($fieldName) | returns field name of ID attribute for entities in the collection |
| toArray(array $arrAttributes=array()) | calls toArray($attAttributes) on each entity in collection, and returns result as array |
It is the end of Magento tutorial 19 with the topic “Entity Attribute Value in Magento”. After practicing this lesson, you should know EAV structure and how Magento use EAV structure to mange database. Moreover, you can feel comfortable to work with a collection.
You may interest in our Magento Open Course for Magento Tutorial.
We will update Magento tutorials regularly, especially tutorials about Magento 2. Let’s subscribe to our blog to be the first one catch up with the latest news by leaving your email below.
