Understand EAV structure and related tables to get any attribute

Question

For products I have created new attribute brix_level which is a text field.

Now I need products data using SQL.

I am good at quries, but the EAV structure is beyond my level :D

Can anyone explain the structure of EAV, and which tables do I need to use to get these data.

Product ID, SKU, Product Title, Brix Level

Answer 1

This is a good article https://belvg.com/blog/eav-in-magento-2.html that covers much of what you need, this is a Magento 1 tutorial https://blog.magestore.com/entity-attribute-value-in-magento/ but the DB stuff is still relevant.

The EAV model and the Magento EAV model are extensively documented over the internet.

EDIT.

• eav_attribute_table - main attribute table (attribute_code, attribute_id etc)

• catalog_eav_attribute - additional attribute table, specific to product attributes only (is_used_in_promo_rule, is_searchable etc.)

• catalog_product_entity - main product table

• catalog_product_[backend_type] - values stored for each attribute based on their type

• backend_type can be one of int|decimal|varchar|text|datetime

• attributes with backend_type static are saved in the main table

  Using plain custom SQL makes sense on very few occasions. You have to account or product type(simple, configurable..etc) and attribute set (eav_attribute_set).

Magento offers the possibility of adding custom and extension attributes to the product. You can also add product attributes directly from the admin using the GUI.

You should find yourself at SQL level only if you really know what you are doing and if the situation really asks for it (e.g. optimization).

To retrieve the data you can use either collections or repositories

Answer 2

In the EAV structure, you have to make a join for each attribute for each store. (if there is only one store then just for each attribute)

SELECT 
     e.entity_id as 'ID'
   , e.sku 
   , pt.value AS 'Product Title'
   , bl.value AS 'Brix Level'
FROM catalog_product_entity as e 
left join catalog_product_entity_text as pt on pt.entity_id = e.entity_id and pt.attribute_id = {xx}
left join catalog_product_entity_text as bl on bl.entity_id = e.entity_id and bl.attribute_id = {yy}
group by e.entity_id

where {xx} and {yy} is the attribute_id for Product Title and Brix Level respectively. (if you don't know the attribute_id, you can use a subquery attribute_id in (select attribute_id from eav_attribute where attribute_code = 'brix_level' and entity_type_id = 4)

I assumed product title is text, if not, change table name accordingly, e.g. catalog_product_entity_varchar.

SKU is a static type, so it is stored in the entity table as a column