0

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
1
  • What research did you do? The internet is full of articles related to EAV and magento EAV Commented Jun 28, 2019 at 5:32

2 Answers 2

2

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

3
  • Thanks mate. I know the internet is full of documentation about EAV, but I am still unclear to understand it's database structure :) If you can tell me related tables to get the data I need that would be great. Commented Jun 28, 2019 at 5:42
  • @MuhammadWasif I edited the answer. Commented Jun 28, 2019 at 7:25
  • Wow. thanks a lot :) Commented Jun 28, 2019 at 8:32
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.