Flattening the magento eav structure in SQL

The Magento EAV structure is one that is designed for scalability, but loses efficiency when it comes to data analysis. Due to the use of multiple tables for storing the “entity”, “attribute” and then the “value”, a SQL query to perform complex product analyses is required to perform joins across several tables. This article outlines an approach to “flatten” this structure so that all products and their associated attributes are stored in the same table, allowing for easy analysis.

The SQL query to be used to flatten the product EAV model is given at the end of the article.

On running the query, the resulting table will have the below format. Each product will have multiple rows, each row associated with a different attribute. For example, in the below example, the product with SKU  8132 has 3 rows - color, size and status. The product with SKU 9145, on the other hand, has only two rows because it has two attributes associated with it - color and size.

​The below SQL query is made up of 4 smaller queries. These 4 queries are identical, except that each one references a different data type table in the EAV structure - int, varchar, decimal and datetime. Individual parts can be removed from the query as required.

Still have questions? Leave a comment or reach out!

(SELECT p.entity_id, 
        p.sku, 
        ea.attribute_id, 
        ea.frontend_label, 
        pi.value :: text AS int_value, 
        CASE 
          WHEN eaov.value IS NULL THEN pi.value :: text 
          ELSE eaov.value 
        END              AS value 
 FROM   catalog_product_entity p 
        left join catalog_product_entity_int pi 
               ON p.entity_id = pi.entity_id 
        left join eav_attribute ea 
               ON pi.attribute_id = ea.attribute_id 
        left join eav_attribute_option eao 
               ON pi.value = eao.option_id 
                  AND pi.attribute_id = eao.attribute_id 
        left join eav_attribute_option_value eaov 
               ON eao.option_id = eaov.option_id) 

UNION 

(SELECT p.entity_id, 
        p.sku, 
        ea.attribute_id, 
        ea.frontend_label, 
        pi.value AS int_value, 
        CASE 
          WHEN eaov.value IS NULL THEN pi.value :: text 
          ELSE eaov.value :: text 
        END      AS value 
 FROM   catalog_product_entity p 
        left join catalog_product_entity_varchar pi 
               ON p.entity_id = pi.entity_id 
        left join eav_attribute ea 
               ON pi.attribute_id = ea.attribute_id 
        left join eav_attribute_option eao 
               ON pi.value = eao.option_id :: text 
                  AND pi.attribute_id = eao.attribute_id 
        left join eav_attribute_option_value eaov 
               ON eao.option_id = eaov.option_id) 

UNION 
​
(SELECT p.entity_id, 
        p.sku, 
        ea.attribute_id, 
        ea.frontend_label, 
        pi.value :: text AS int_value, 
        CASE 
          WHEN eaov.value IS NULL THEN pi.value :: text 
          ELSE eaov.value 
        END              AS value 
 FROM   catalog_product_entity p 
        left join catalog_product_entity_decimal pi 
               ON p.entity_id = pi.entity_id 
        left join eav_attribute ea 
               ON pi.attribute_id = ea.attribute_id 
        left join eav_attribute_option eao 
               ON pi.value = eao.option_id 
                  AND pi.attribute_id = eao.attribute_id 
        left join eav_attribute_option_value eaov 
               ON eao.option_id = eaov.option_id)