How EAV Data Storage Works in Magento 2

How EAV Data Storage Works in Magento 2

EAV (Entity-attribute-value) is a model of storing the values of entity attributes in a certain data storage. As a data storage, Magento 2 supports MySQL-compatible databases (like MySQL, MySQL NDB Cluster, MariaDB, Percona and others).

The principles of EAV data storage work in Magento
What happens when a new attribute is added to the system

A table of the classical EAV model has 3 columns:

  1. entity (the object to which you want to set the attribute value)
  2. attribute
  3. value

In the Flat model, the attribute values are stored in the same table as the entities, for each attribute, a separate column in the table is created.

In the EAV model, attribute values are stored in a separate table, where a separate column is not created for each attribute. For each attribute value of an entity, however, a new row is created in the EAV table.

The principles of EAV data storage work in Magento

The differences between the classic EAV model and the one used by Magento:

  1. Each attribute is assigned a backend type (varchar, int, text …).
  2. Each type of EAV entities has a separate table. The types of EAV entities are stored in the eav_entity_type table. The name of the tables in which the entities are stored in the entity_table column.
  3. System EAV entities are stored in the eav_entity table.
  4. Attributes that have the backend type as static are stored in the same table as the entity. These attributes have a global scope.
  5. Each backend type of each entity type has its own table, in which the attribute values are stored. The name of this table is constructed from the pattern {entity_table}_{backend_type}, where entity_table is the name of the entity table, and backend_type is the backend type of the attribute. These tables have the following columns: value_id (int), attribute_id (int), store_id ** (int), entity_id * (int), value (depending on the backend type).

Here is an image for illustrating the connections:

image5

In order to get/write the attribute value, you need:

  1. store_id**
  2. entity_id*
  3. entity_table or entity_type_id
  4. attribute_id

If the entity_table is unknown, but you know the entity_type_id, then the entity_table can be obtained from the eav_entity_type table.

Magento gets the attributes values of the entity with one large SQL query, which is generated using the following algorithm:

  1. Get all attribute tables for a particular entity_type.
  2. For each table perform the following:
    • A select subquery is created from the current table, which requests value and attribute_id
    • a condition is added that entity_id = ID of the requested entity*
    • a condition is added for each scope that store_id IN ($scope->getValue()) **
    • sorted by store_id in descending order
  3. UNION ALL is done for all subqueries.

* The field does not have to be called entity_id. Magento uses $metadata->getLinkField() to get the name of the field.

** The field does not have to be called store_id. Magento uses $scope->getIdentifier() to get the field name.

What happens when a new attribute is added to the system

When a new attribute is added, new records are created in the database. Similar records are created for each attribute type.

Let’s consider some standard types of attributes.

Text Field

Let’s take a look at the example of creating a new attribute of the Text Field type.

  1. A new entry is created in the eav_attribute table:
attribute_id entity_type_id attribute_code
154 4 test_code
  1. One entry per store view is created in the eav_attribute_label table with the Label of the attribute:
attribute_label_id attribute_id store_id value
17 154 1 Test label
  1. A new entry is created in the catalog_eav_attribute table:
attribute_id frontend_input_renderer is_global
154 NULL 1

Dropdown

image4

The differences with Text Field:

  • eav_attribute.frontend_input = “select”;
  • eav_attribute.source_model = “Magento\Eav\Model\Entity\Attribute\Source\Table”;
  • eav_attribute.backend_type = “int”;
  • eav_attribute.default_value = eav_attribute_option.option_id by default;
  • one row per value is added to eav_attribute_option.
option_id attribute_id sort_order
210 155 1
211 155 2
212 155 3
  • Rows are added to eav_attribute_option_value:
value_id option_id store_id value
208 211 0 2
210 212 0 3
205 210 1 One
207 211 1 Two
209 212 1 Three
206 210 0 1

Price

The differences with Text Field:

  • eav_attribute.frontend_input = “price”
  • eav_attribute.backend_model = “Magento\Catalog\Model\Product\Attribute\Backend\Price”
  • eav_attribute.backend_type = “decimal”

Media image

The differences with Text Field:

  • eav_attribute.frontend_input = “media_image”

Text swatch

image6

The differences with Dropdown:

  • catalog_eav_attribute.additional_data have an approximate form {“swatch_input_type”:”text”,”update_product_preview_image”:”0″,”use_product_image_for_swatch”:0}
  • Rows are added to eav_attribute_option_swatch
swatch_id option_id store_id type value
1 213 0 0 1
6 215 1 0 3
2 213 1 0 1
4 214 1 0 2
3 214 0 0 2
5 215 0 0 3

This is what EAV model is all about. Please leave your questions in the comment box below if something is unclear. The second part of the EAV topic will be published next week, so stay tuned.



Post a new comment

top
BelVG Newsletter
Subscribe to our mailing list and get interesting stuff and updates to your email inbox.
Email *