Know About Magento 2 EAV Concepts

Entity–attribute–value model (EAV) is a data model to encode, in a space-efficient manner, entities where the number of attributes (properties, parameters) that can be utilized to represent them is conceivably vast, but the amount that will really connect to a given entity is comparatively simple. Such entities epitomize to the mathematical concept of a sparse matrix.

EAV stands for Entity, Attribute and Value.

Entity (E):

Entity refers to data item. For example, Customer, Category, Product etc.

catalog_category_entity
catalog_product_entity
customer_entity
customer_address_entity

Attribute (A):

Attribute relates to the various attributes of the Entity. For example, product has different attributes like color, size, price, pattern etc.

We can efficiently get all the attributes correlated to an entity utilizing the subsequent SQL query:

SELECT
  attribute_code,
  attribute_id,
  backend_type
FROM
  eav_attribute
WHERE entity_type_id =
  (SELECT
    entity_type_id
  FROM
    eav_entity_type
  WHERE entity_type_code = 'catalog_product')

Similarly You can find out all the attributes of other entities by replacing ‘catalog_product’ with ‘catalog_category’ or ‘customer’ or ‘customer_address’

Value (V):

Value connects to the exact content of the characteristic of the entity. Like color has value black, size has value XL, etc.

In Magento values of an attribute of an entity are stored in catalog_product_entity_[backend_type] tables.

Where [backend_type] connects to the value of field: backend_type (except value ‘static’) of table: eav_attribute.

E.g. To find all the backend types related to a product entity:

SELECT DISTINCT
  backend_type
FROM
  eav_attribute
WHERE entity_type_id =
  (SELECT
    entity_type_id
  FROM
    eav_entity_type
  WHERE entity_type_code = 'catalog_product')

Which shows that values are stored in below tables for product entity.

catalog_product_entity_text
catalog_product_entity_int
catalog_product_entity_varchar
catalog_product_entity_decimal
catalog_product_entity_datetime

Note: You wont find any table related to backend_type = static. For an attribute with the static backend_type, values are collected as a column directly in the entity table. For example, SKU is an attribute of backend_type = static and its values are collected in the entity table itself: catalog_product_entity under field: SKU.

Why is EAV used?

It’s used because of scalability of the system. Using EAV, you don’t need to change core table structure for every new attribute.

How does EAV work?

To better understand, let’s take an example of product entity of Magento. Each product entity has many attributes like name, description, price etc. Each product then has value for the given attribute e.g. product name is “iPhone 6”, product price is “200$” etc.

Normally, in other framework / structure, while designing database, there is one product table with all product information in same table, another category table with all category information and third product category table which links these two tables.

However, Magento development treats it bit different by splitting all this information into sub tables, rather than storing all in one table.

The base / main / top table in this hierarchy is catalog_product_entity. It includes some basic information of a product and does not include any useful information other than the SKU! However we can build a full product record using this table only with reference from the attribute and value tables.

Let’s take a look.

First, you need to start joining attribute tables to this entity table. Take a look at the table called eav_attribute. eav_attribute is used to store attributes for all different entities (product, customer, order, category etc.).

Each entity (product, category, customer etc) is presented an entity_type_id. To find out our entity (catalog_product_entity) entity_type_id, open table eav_entity_type and look for row where entity_table is our entity i.e. catalog_product_entity. You will find that our entity_type_code is catalog_product and entity_type_id is 4 for catalog_product_entity. Similarly you can find entity_type_id for any entity.

Using this value and the attribute code, it is possible to load the attributes for a product, or any entity from the table eav_attribute.

E.g.

# To Load all product attributes
   SELECT attribute_code FROM eav_attribute
   WHERE entity_type_id = 4;
 
# To Load a single product attribute
   SELECT attribute_code FROM eav_attribute
   WHERE entity_type_id = 4 AND attribute_code = 'name';

Let’s get the values of attributes of our entity.

Values are separated across several different tables in Magento. The process the values are divided depends upon their type. For instance, all costs and other decimal attributes are collected in catalog_product_entity_decimal whereas all brief text strings are stored in catalog_product_varchar. To figure out which table each attribute is collected in, Magento development practices the column backend_type in the table eav_attribute.

Let’s find out the backend type for the product attribute ‘name’ by using below query.

SELECT attribute_code, backend_type FROM eav_attribute
WHERE entity_type_id = 4 AND attribute_code = 'name';

You will find out that backend_type is “varchar” for product attribute “name”.

Based on this we can figure out that the value for the name attribute will be stored in catalog_product_entity_varchar.

Below is the query to load all values of the attribute ‘name’ for a product.

SELECT e.entity_id AS product_id, var.value AS product_name
   FROM catalog_product_entity e, eav_entity_type ety, eav_attribute eav, catalog_product_entity_varchar var
   WHERE
      ety.entity_table = 'catalog_product_entity'
      AND ety.entity_type_id = eav.entity_type_id
      AND eav.attribute_code = 'name'
      AND eav.attribute_id = var.attribute_id
      AND var.entity_id = e.entity_id

The preceding code lists out the name and id for each outcome in your database.

To load all values of the attribute ‘name’ for a product for given store only.

SELECT e.entity_id AS product_id, var.value AS product_name
   FROM catalog_product_entity e, eav_entity_type ety, eav_attribute eav, catalog_product_entity_varchar var
WHERE
ety.entity_table = 'catalog_product_entity'
AND ety.entity_type_id = eav.entity_type_id
AND eav.attribute_code = 'name'
AND eav.attribute_id = var.attribute_id
AND var.entity_id = e.entity_id
AND var.store_id = 0

What are the down sides to EAV?

A major downside to EAV is its impact on performance. As entity data being so fragmented, creating a whole entity record requires a lot of expensive table joins which will be affecting the performance. However, Magento developer manages this will its inbuilt cache and flat table concept very efficiently.