I will explain EAV in this article. Read it carefully. EAV (Entity-attribute-value) is a model for storing entity attribute values ​​in a certain storage place. For storage, Magento 2 supports MySQL-compatible databases (like MySQL, MySQL NDB Cluster, MariaDB, Percona, and others).

To better understand this article, I recommend opening up a development Magento database using a tool such as phpMyAdmin.

What is EAV?

EAV stands for Entity, Attribute and Value. Now what are these? To know more, keep reading..

Entity

The entity represents Magento data items such as products, categories, customers and orders. Each entity (product, category etc) will have it’s 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.

To better understand this, let us consider the product entity. Each product entity will have a series of attributes, one being the attribute name. Each product will then have a value for the attribute name (and all other attributes).

How does EAV work?

If you were designing an eCommerce application, you had one table that contained all of your product information, another that contained your category information and maybe another table that linked these two together. This is simple enough and easy to understand, where as Magento has almost 40 tables for the products and category’s alone!

To understand why, let us use the product table as an example.

Rather than store all product information in one table, Magento splits this information up into sub tables. The top table in this hierarchy is catalog_product_entity. If you take a look at this table in phpMyAdmin, you will see that it includes simple base information for a product and does not appear to include any useful information other than the SKU! Fortunately, using this table it is possible to build a full product record from the attribute and value tables.

To start building a full product record, you will need to start joining attributes to the product entity table. Before you do this, take a look at the table called eav_attribute. eav_attribute is the main attribute store for Magento and is used to store attributes for all different entities (product, customer, order, category etc).

Open this table in phpMyAdmin and click browse. Notice that there are hundreds of different attributes, some even with the same name? At first this confused me because I wasn’t sure how Magento could differentiate between the the two different attributes called name.

How did Magento know which one was for the product and which one was for a category?

As is usually the case with Magento, a small bit of research led me to the an extremely simple answer: entity_type_id! Each entity (product, category, customer etc) is given an entity_type_id.

To find this out, go back to catalog_product_entity and look for the entity_type_id field. The value for every record in that table should be 4, as this has been designated as the entity_type_id for products. If you were to look in catalog_category_entity you should see a different entity_type_id. Using this value and the attribute code, it is possible to load the attributes for a product, or any entity.

Lots of theoretically, lets understand it practically

Take a look at the table called eav_entity_type.

eav_entity_type =>  Used to store attributes for all different entities (product, customer, order, category etc.).

Now, lets take an example of Product entity. So from table, we know that entity_type_id is 4 for Products (code – catalog_product).

So I want to find out all the attributes of the product. To do that, let understand the following query

# Load all product attributes

   SELECT attribute_code FROM eav_attribute 
   WHERE entity_type_id = 4;

The same way, you can find the attributes for categories. An entity_type_id is 3 for Categories (code – catalog_category).

# Load all category attributes

SELECT attribute_code FROM eav_attribute
WHERE entity_type_id = 3;

Now we know the attribute code and also the entiy_type_id of the products. What if I want to fetch single product attribute.

# Load a single product attributes

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

Now to make it more clear. Lets focus on Product entity.

Now that you can get attributes and entities, it is time to start getting values. Values are separated across several different tables. For now though, just take a look at all tables that begin with catalog_product_entity. The way the values are split depends upon their data type.

For example,

catalog_product_entity_decimal => all prices and other decimal attributes are stored here.

catalog_product_entity_varchar => all short text strings are stored.

To figure out which table each attribute is stored in, Magento uses the column backend_type in the table eav_attribute. If you run the following query you should be able to find out the backend type for the product attribute ‘name’.

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

Based on what was said above, we can determine that the value for the name attribute will be stored in catalog_product_entity_varchar.

Now, we have entity – Product, attribute – name and value – we want for all products.

SELECT cpe.entity_id AS product_id, cpev.value AS product_name
   FROM catalog_product_entity cpe, eav_attribute ea, catalog_product_entity_varchar cpev
   WHERE 
      ea.entity_type_id =4
      AND ea.attribute_code = 'name' 
      AND ea.attribute_id = cpev.attribute_id
      AND cpev.entity_id = cpe.entity_id

The above code lists out the name and id for every product in your database. If you got that correct then congratulations, you are well on your way to understanding the EAV architecture.

Looks good. Easy Right?

In this way, you will be able to find out the information or values of any attribute and entity.

If you’re running a multi-store Magento, here is how to adapt the above code to only include products from a certain store.

SELECT cpe.entity_id AS product_id, cpev.value AS product_name
   FROM catalog_product_entity cpe, eav_attribute ea, catalog_product_entity_varchar cpev
   WHERE 
      ea.entity_type_id = 4
      AND ea.attribute_code = 'name' 
      AND ea.attribute_id = cpev.attribute_id
      AND cpev.entity_id = cpe.entity_id
      AND cpev.store_id = 0