>Magento 2 EAV Model – Things You May Not Know

Magento 2 EAV Model – Things You May Not Know

Hi developers, 

It’s so glad to come back and continue providing all of you with more useful Magento 2 tutorials from now on. 

Today, we will reveal a complete tutorial of Magento 2 Entity-Attribute-Value model (EAV) which is extremely essential for every developer to build products or do projects at the higher customization level. This article mainly concentrates on explaining 7 concerns as below:  

  • Why is the EAV model? 
  • EAV model definition 
  • EAV model’s benefits
  • Structure of EAV tables and data in Magento
  • EAV Entity types in Magento 2
  • EAV Attributes in Magento 2 
  • Limitation of EAV model 

1. Why is Magento 2 EAV model?

why is magento 2 eav

As can be seen from the above image, this is the most simple and less flexible data structure between catalog and products of a store (Flat model). All products are stored in the product table.

It doesn’t matter at all if the store owner just sells one product containing some attributes. In case the store owner widens the business scope by selling more products and each of them includes specific attributes (color, size, height,…), this simple structure still meets usage demand and data organization to implement necessary queries. However, if you use this structure to add a new attribute for the product, you have to change much database and code (add more columns or create a new table to save attributes). Furthermore, there exits unnecessary attributes you don’t need to enter data for a product, causing a larger number of columns without data which is no need to apply to the current product. 

⇒ Let’s take an example for easier understanding: 

A website is selling clothes and wants to widen business by providing drinks for the market also. Hence, it is essential to add other attributes for describing drinks such as calories, carbohydrate amount,…). To have this data, we need to follow one of the two following methods: 

  • Method 1: Create more columns in the catalog table which are not applied to Clothes products.
  • Method 2: Create more tables for each attribute (Calories table, for instance). As a result, a product needs so many tables to describe it and you have to modify the query logic to get data for each corresponding attribute. 

Therefore, Magento comes up with EAV model as an optimized solution for such problems. 

2. EAV model definition 

EAV stands for Entity-Attribute-Value, which is a data model to encode, in a space-efficient manner, entities where the number of attributes (properties, parameters) that can be used to describe them is potentially vast, but the number that will actually apply to a given entity is relatively modest. Such entities correspond to the mathematical notion of a sparse matrix. (Wikipedia)

In which: 

  • Entity: includes information of data type to be stored (customer, product,…)
  • Attribute: is the property of an entity (color, size, height,…)
  • Value: is the value of a given attribute (blue, red,….)

Based on Magento 2 EAV model, we will divide data into various tables corresponding to attribute types. As mentioned above, this data organization is similar to the matrix method: each couple of attributes is the description of an entity and is placed in a row of the EAV table. 

EAV works based on the three main tables: 

  • Entity table 
  • Attribute table
  • Value table 

In case you want to add more attributes, you just need to put these attributes to a table including all declared attributes in advance. For example, you add first_name and last_name attributes to the eav_attribute like the following image: 

what is magento 2 eav

Also, the EAV model supports storing data corresponding to each store, via the relationship between attribute data tables and store tables. When you desire to add attribute data for each store, you just need to add store_id for that attribute data.

what is magento 2 eav

 

⇒ Comparison between the simple structure (Flat model) and EAV model: 

  • In the Flat model, the attribute values and the entities are both stored in the same table. A new column in the table is inserted for each attribute. 
  • In the EAV model, the attribute values are stored in a specific table in which there is no column created for each attribute. A new row is now added in the EAV model for each attribute value of an entity. 

3. Benefits of Magento 2 EAV model  

EAV model helps to resolve a lot of issues related to designing database for websites especially when you want to manage, modify or create new attributes for entities: 

  • Easily create new attributes and manage adjustment. If you desire to add attributes for the entity, there is no need to modify table structures, but the only thing you should do is to add new data to available tables. 
  • Support managing, and storing attribute data based on multi-nations, multi-languages, corresponding to Store or Website.
  • Have higher customization than Flat data structure.

4. Structure of  Magento 2 EAV tables and data 

Let’s take a look at the following diagram of catalog_product eav to know how Magento builds the EAV system: 

magento 2 eav structure

  • catalog_product_entity: determines entity containing attribute data (with attribute type as static)  – It is equivalent to the product table. 
  • eav_attribute: is the table of attribute information.
  • catalog_product_entity_datetime, catalog_product_entity_decimal, catalog_product_entity_int, catalog_product_entity_text, catalog_product_entity_varchar: contains attribute values – It is equivalent to attribute_value table.
  • Besides, when you add an attribute, it is put to the default attribute set and attribute group according to the information in the table (if this attribute is not declared):

+ eav_entity_type: stores information of the attribute model, default attribute set, entity table,…   

eav_entity_type

+ eav_attribute_set: stores attribute sets.

eav_attribute_set

 

  • eav_attribute_group: stores attribute groups and connects with eav_attribute_set via a foreign key (attribute_set_id).

 

eav_attribute_group

+ eav_entity_attribute: stores attribute information of attribute set and attribute group. It connects with the eav_attribute table via attribute_id, and connects with the eav_attribute_group table via a foreign key (attribute_group_id).

   eav_entity_attribute

 

  • An attribute set contains a lot of various attribute groups. Let’s take an example about the default attribute set of products to help you get more understanding: 

Default attribute set

As you can see, the default attribute set of products includes many attribute groups like product details, content, bundle items, and so on. Children attributes are also covered in these groups. You can assign/remove attributes to/from groups and create a specific attribute set for each entity as well. This helps to solve the problem when you want to assign attribute for each entity or entities having the same attribute.

5. EAV Entity type in Magento 2 

Magento 2 divides entity into 8 types as below: 

  • customer – Entity Id = 1
  • customer_address – Entity Id = 2
  • catalog_category – Entity Id = 3
  • catalog – Entity Id = 4
  • order – Entity Id = 5
  • invoice – Entity Id = 6
  • creditmemo – Entity Id = 7
  • shipment – Entity Id = 8

(You can see in the entity_value_type table)

Each entity type has its own table. For example: customer – Entity consists of customer_eav_attribute, customer_eav_attribute_website, customer_entity, customer_entity_datetime, customer_entity_decimal, customer_entity_int, customer_entity_text, customer_entity_varchar (each table is corresponding to each attribute). 

⇒ How to create a new Entity type?

We will create a new entity type as banner by using a simple module: 

php bin/magento setup:upgrade
php bin/magento setup:static-content:deploy
php bin/magento cache:flush

Results are displayed as in the following three images: 

 

 

 

6. Create EAV attributes 

We take a simple example to help you know how to create eav attributes for the entity type: catalog. (you do similarly for other entity types)

6.1. Create via Setup Script 

EAV attribute can be created via the two main setup files as InstallData and UpgradeData. This is a code snippet to create via InstallData by adding a file: Vendor_Module\Setup\InstallData.php

<?php
namespace Vendor\Module\Setup;
use Magento\Framework\Setup\InstallDataInterface;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;
/**
 * Class InstallData
 * @package Vendor\Module\Setup
 */
class InstallData implements InstallDataInterface
{
    /**
     * @var \Magento\Eav\Setup\EavSetupFactory
     */
    private $eavSetupFactory;
    /**
     * InstallData constructor.
     * @param \Magento\Eav\Setup\EavSetupFactory $eavSetupFactory
     */
    public function __construct(\Magento\Eav\Setup\EavSetupFactory $eavSetupFactory)
    {
        $this->eavSetupFactory = $eavSetupFactory;
    }
    /**
     * @param ModuleDataSetupInterface $setup
     * @param ModuleContextInterface $context
     *
     * @SuppressWarnings(PHPMD.UnusedFormalParameter)
     */
    public function install(
        ModuleDataSetupInterface $setup,
        ModuleContextInterface $context
    ) {
        $setup->startSetup();
        $eavSetup = $this->eavSetupFactory->create();
        $eavSetup->addAttribute(
            \Magento\Catalog\Model\Product::ENTITY,
            'simple_test',
            [
                'type' => 'text',
                'group' => 'Product Details',
                'label' => 'Bss Simple Test',
                'input' => 'text',
                'backend' => '',
                'frontend' => '',
                'required' => false,
                'global' => \Magento\Eav\Model\Entity\Attribute\ScopedAttributeInterface::SCOPE_GLOBAL,
                'is_used_in_grid' => false,
                'is_visible_in_grid' => false,
                'is_filterable_in_grid' => false,
                'visible' => false,
                'user_defined' => false,
                'searchable' => true,
                'filterable' => true,
                'comparable' => true,
                'filterable_in_search' => true,
                'is_html_allowed_on_front' => false,
                'used_for_promo_rules' => true,
                'visible_on_front' => false,
                'used_for_sort_by' => true,
                'unique' => false,
                'default' => '',
                'used_in_product_listing' => true,
     'source' => '',
     'option' => '',
                'sort_order' => 10,
                'apply_to' => 'simple,configurable,virtual,bundle,downloadable,grouped'
            ]
        );
        $setup->endSetup();
    }
}

6.2. Create via Declarative Schema

In Magento 2.3 and above, you can create attribute via DataPatch.  For example, create a file: Vendor_Module\Setup\Patch\Data\ApplyNewAttribute.php

<?php
namespace Vendor\Module\Setup\Patch\Data;
use Magento\Framework\Setup\ModuleDataSetupInterface;
use Magento\Framework\Setup\Patch\DataPatchInterface;
use Magento\Framework\Setup\Patch\PatchVersionInterface;
use Magento\Eav\Setup\EavSetup;
use Magento\Eav\Setup\EavSetupFactory;
/**
 * Class ApplyNewAttribute
 * @package Vendor\Module\Setup\Patch\Data
 */
class ApplyNewAttribute implements DataPatchInterface, PatchVersionInterface
{
    /**
     * @var ModuleDataSetupInterface
     */
    private $moduleDataSetup;
    /**
     * @var EavSetupFactory
     */
    private $eavSetupFactory;
    /**
     * ApplyNewAttribute constructor.
     *
     * @param ModuleDataSetupInterface $moduleDataSetup
     * @param EavSetupFactory $eavSetupFactory
     */
    public function __construct(
        ModuleDataSetupInterface $moduleDataSetup,
        EavSetupFactory $eavSetupFactory
    ) {
        $this->moduleDataSetup = $moduleDataSetup;
        $this->eavSetupFactory = $eavSetupFactory;
    }
    /**
     * {@inheritdoc}
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
     */
    public function apply()
    {
        /** @var EavSetup $eavSetup */
        $eavSetup = $this->eavSetupFactory->create(['setup' => $this->moduleDataSetup]);
        $eavSetup->addAttribute(
            \Magento\Catalog\Model\Product::ENTITY,
            /**
Add your attribute here.
*/
……….
       );
        $setup->endSetup();
    }
}

6.3. More explanation of Data types and their meanings

Options are required to declare: attribute_code, and type.  Other options can be declared or not, depending on your usage purposes.  For options with values (Yes/No), the default value is False if you don’t declare. 

  • type: define format of attributes (static, varchar, int, decimal, datetime). Static type is a little bit different because attribute data with this input type is stored in the main table (catalog_product_entity, customer_entity, customer_address_entity, …), not in the text, varchar, decimal, int, datetime tables.
  • group: define the group of attributes (see more in the  eav_attribute_group table). Magento 2 classifies attributes into attribute groups and attribute sets corresponding to product lines or each product. If you don’t declare an attribute, it will belong to the default group depending on attribute types.
  • label: specify the display label of the attribute.
  • input: text, int, select, multiselect, date, hidden, boolean, multiline,…
  • backend: another name is Backend Model which is used to modify attribute data when saving attribute, including validate, afterLoad, beforeSave, afterSave, beforeDelete, afterDelete (see more at Magento\Eav\Model\Entity\Attribute\Backend\AbstractBackend).
  • frontend: you use it to change data as you want after getting data from database ( see more at Magento\Eav\Model\Entity\Attribute\Frontend\AbstractFrontend).
  • required: there are two values as True and False.
  • global: specifies the scope of the attribute. 3 scopes as website, store, global are declared at \Magento\Eav\Model\Entity\Attribute\ScopedAttributeInterface. The default scope is global.
  • is_used_in_grid: enter True or False to determine whether the attribute uses grid or not (depending on entity types to have corresponding grids).
  • is_visible_in_grid: enter True or False to determine whether the attribute is visible in the grid or not (depending on entity types to have corresponding grids).
  • is_filterable_in_grid: enter True or False to determine whether the attribute is filterable in the grid or not (depending on entity types to have corresponding grids).
  • visible: enter True or False to determine whether the attribute is visible in both frontend and backend or not.
  • user_defined: enter True or False to determine whether the admin can change/remove the attribute or not.
  • searchable: enter True or False to determine whether customers can use attribute for searching in the frontend or not.
  • filterable: enter True or False to determine whether customers can use attribute for filtering in the frontend or not.
  • comparable: enter True or False to determine whether customers can use attribute for comparing in the frontend or not.
  • filterable_in_search: enter True or False to determine whether the attribute is used to filter in the frontend layer navigation or not. (only applying to attributes with input type as int).
  • is_html_allowed_on_front: enter True or False to determine whether the attribute value is formatted by HTML tags or not.
  • used_for_promo_rules: enter True or False to determine whether the attribute is used in promotion rules or not.
  • visible_on_front: enter True or False to determine whether the attribute is shown in the Additional Information tab of the frontend product page or not. (only applying to Simple Product or Virtual Product and also depending on visible mentioned above).
  • used_for_sort_by: enter True or False to determine whether the attribute is used to sort on the category page or not.
  • unique: enter True or False to determine whether the attribute value is unique (no repeat) or not.
  • default: the default value of the attribute. After saving, if the attribute has no value, it takes this default value.
  • used_in_product_listing: enter Yes or No (Yes: the attribute is added when loading the collection of product, category,…)
  • source: it is used to change display data of the attribute. (mainly change attribute options for input types as select, multiselect, dropdown, radio – you need to use class to modify).
  • option: you can use option as the way you use source, but you need to declare option in the setup file.  For example: ‘option’ => [‘values’ => [‘Test1’, ‘Test2’, ‘Test3’]]
  • sort_order: the order of the current attribute versus others
  • apply_to: determine which product type to apply

7. Limitation of EAV model 

Although the EAV model is very helpful, it still has some drawbacks such as: 

  • Data is fragmented. When searching or getting attribute data, you need to implement many queries into various tables to receive the last result. As a consequence, queries become more complicated and take you longer to process. 

To prove it, we take an example to get information such as status, name, price, and description (information of product with ID = 6). 

SELECT
 IF(
   t_s.value_id IS NULL, t_d.value, t_s.value
 ) AS `value`
FROM  `catalog_product_entity_int` AS `t_d`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = t_d.entity_id
 LEFT JOIN `catalog_product_entity_int` AS `t_s` ON t_s.attribute_id = t_d.attribute_id
 AND t_s.entity_id = t_d.entity_id
 AND t_s.store_id = 1
WHERE (e.entity_id IN (6) )
 AND ( t_d.attribute_id IN ( '97'))
 AND (t_d.store_id = IFNULL(t_s.store_id, 0))
UNION ALL
SELECT
 IF(
   t_s.value_id IS NULL, t_d.value, t_s.value
 ) AS `value`
FROM  `catalog_product_entity_varchar` AS `t_d`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = t_d.entity_id
 LEFT JOIN `catalog_product_entity_varchar` AS `t_s` ON t_s.attribute_id = t_d.attribute_id
 AND t_s.entity_id = t_d.entity_id
 AND t_s.store_id = 1
WHERE ( e.entity_id IN (6) )
 AND (t_d.attribute_id IN ('73'))
 AND ( t_d.store_id = IFNULL(t_s.store_id, 0) )
UNION ALL
SELECT
 IF(
   t_s.value_id IS NULL, t_d.value, t_s.value
 ) AS `value`
FROM `catalog_product_entity_decimal` AS `t_d`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = t_d.entity_id
 LEFT JOIN `catalog_product_entity_decimal` AS `t_s` ON t_s.attribute_id = t_d.attribute_id
 AND t_s.entity_id = t_d.entity_id
 AND t_s.store_id = 1
WHERE ( e.entity_id IN (6))
 AND (t_d.attribute_id IN ('77'))
 AND ( t_d.store_id = IFNULL(t_s.store_id, 0))
UNION ALL
SELECT
 IF(
   t_s.value_id IS NULL, t_d.value, t_s.value
 ) AS `value`
FROM  `catalog_product_entity_text` AS `t_d`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = t_d.entity_id
 LEFT JOIN `catalog_product_entity_text` AS `t_s` ON t_s.attribute_id = t_d.attribute_id
 AND t_s.entity_id = t_d.entity_id
 AND t_s.store_id = 1
WHERE ( e.entity_id IN (6))
 AND ( t_d.attribute_id IN ('75'))
 AND ( t_d.store_id = IFNULL(t_s.store_id, 0))

⇒ Result is:  result in limits

  • More complex queries require more time to process, leading to more waiting time to load a page and worse site performance. When you have only one website with a few products, the impact on site performance may be not clear. However, in case you have multi-websites with thousands of products, it is really a big problem you need to solve. 

To overcome these drawbacks, Magento 2 comes up with a new solution by combining data into a Flat table: catalog_product_flat, for example.(When you enable Use Flat Catalog Product config at Store⇒ Configuration⇒Catalog⇒Catalog⇒Storefront).  In this table, attribute data is updated/inserted when you run index (via cron or cli) or save the product, depending on the Product Flat Data config in Index Management. Hence, you just need to implement queries to get data via the catalog_product_flat table. And the query is now much more simple:

SELECT
 1 AS `status`,
 `e`.`name`,
 `e`.`price`,
 `e`.`description`
FROM
 `catalog_product_flat_1` AS `e`
WHERE `e`.`entity_id` = 6

⇒ Result is:  solution for limit

Nonetheless, using Flat tables has its own limitations as well: indexing is quite complex and may take a long time to reindex data for Entity.  Also, changing the logic of Index is a difficult issue that needs more time to research. If you have any ideas or solution for this problem, feel free to comment under this article!!! 

In conclusion

We believe that you have got so much useful information about Magento 2 EAV model in this article, haven’t you? Let’s share it to more and more developers to spread our knowledge, contributing to improving our skills day by day!

Next Post >