Magento – how to get product specific attribute codes & values using raw sql query

product-attributesql

The following query will give product all attribute codes & values.but I want specific attributes.and i want to store that result into one table.the table format like flat catalog product table.

Note:question is updated.

$query = "SELECT    ce.sku,          
       ea.attribute_code,
       CASE ea.backend_type
          WHEN 'varchar' THEN ce_varchar.value
          WHEN 'int' THEN ce_int.value
          WHEN 'text' THEN ce_text.value
          WHEN 'decimal' THEN ce_decimal.value
          WHEN 'datetime' THEN ce_datetime.value
          ELSE ea.backend_type
       END AS value
   FROM (select sku ,entity_type_id,entity_id from catalog_product_entity where type_id='simple' limit $limit,$offset ) AS ce
   LEFT JOIN eav_attribute AS ea
       ON ce.entity_type_id = ea.entity_type_id
   LEFT JOIN catalog_product_entity_varchar AS ce_varchar
       ON ce.entity_id = ce_varchar.entity_id
       AND ea.attribute_id = ce_varchar.attribute_id
       AND ea.backend_type = 'varchar'
   LEFT JOIN catalog_product_entity_int AS ce_int
       ON ce.entity_id = ce_int.entity_id
       AND ea.attribute_id = ce_int.attribute_id
       AND ea.backend_type = 'int'
   LEFT JOIN catalog_product_entity_text AS ce_text
       ON ce.entity_id = ce_text.entity_id
       AND ea.attribute_id = ce_text.attribute_id
       AND ea.backend_type = 'text'
   LEFT JOIN catalog_product_entity_decimal AS ce_decimal
       ON ce.entity_id = ce_decimal.entity_id
       AND ea.attribute_id = ce_decimal.attribute_id
       AND ea.backend_type = 'decimal'
   LEFT JOIN catalog_product_entity_datetime AS ce_datetime
       ON ce.entity_id = ce_datetime.entity_id
       AND ea.attribute_id = ce_datetime.attribute_id
       AND ea.backend_type = 'datetime'";

Best Answer

For Magento version 2.2, where a new redirection has been added in their EAV model, below query is working for my project. There the entity_type_id needed to be retrieved from catalog_product_entity.

SELECT    ce.sku,           
        ea.attribute_code,
        CASE ea.backend_type
           WHEN 'varchar' THEN ce_varchar.value
           WHEN 'int' THEN ce_int.value
           WHEN 'text' THEN ce_text.value
           WHEN 'decimal' THEN ce_decimal.value
           WHEN 'datetime' THEN ce_datetime.value
           ELSE ea.backend_type
        END AS value
    FROM (select cpe.sku, eas.entity_type_id, cpe.entity_id 
                FROM catalog_product_entity AS cpe, eav_attribute_set AS eas
                WHERE cpe.attribute_set_id=eas.attribute_set_id) AS ce
    LEFT JOIN eav_attribute AS ea
        ON ce.entity_type_id = ea.entity_type_id
    LEFT JOIN catalog_product_entity_varchar AS ce_varchar
        ON ce.entity_id = ce_varchar.entity_id
        AND ea.attribute_id = ce_varchar.attribute_id
        AND ea.backend_type = 'varchar'
    LEFT JOIN catalog_product_entity_int AS ce_int
        ON ce.entity_id = ce_int.entity_id
        AND ea.attribute_id = ce_int.attribute_id
        AND ea.backend_type = 'int'
    LEFT JOIN catalog_product_entity_text AS ce_text
        ON ce.entity_id = ce_text.entity_id
        AND ea.attribute_id = ce_text.attribute_id
        AND ea.backend_type = 'text'
    LEFT JOIN catalog_product_entity_decimal AS ce_decimal
        ON ce.entity_id = ce_decimal.entity_id
        AND ea.attribute_id = ce_decimal.attribute_id
        AND ea.backend_type = 'decimal'
    LEFT JOIN catalog_product_entity_datetime AS ce_datetime
        ON ce.entity_id = ce_datetime.entity_id
        AND ea.attribute_id = ce_datetime.attribute_id
        AND ea.backend_type = 'datetime';
Related Topic