Magento – SQL Query to get actual name of custom attribute value

eavproduct-attributesql

I am able to get the value of a custom attribute from the following query, however, the number doesn't help much. I would like to get the actual name of the value.

SELECT entity.sku, entity.type_id as 'type',
entity_cod_custom_attribute.value as 'cod_custom_attribute'

FROM catalog_product_entity entity

LEFT JOIN catalog_product_entity_int entity_cod_custom_attribute ON
entity_cod_custom_attribute.entity_id = entity.entity_id AND
entity_cod_custom_attribute.attribute_id = (SELECT attribute_id FROM
eav_attribute ea LEFT JOIN eav_entity_type et ON ea.entity_type_id =
et.entity_type_id WHERE ea.attribute_code = 'cod_custom_attribute'
AND et.entity_type_code = 'catalog_product')

This gives me:
SKU-ABC, simple, 355

where 355 is actually "Adult-Large"

I would like it to show:
SKU-ABC, simple, Adult-Large

It looks like I need to do a bit more connecting from the eav_attribute_option table and the eav_attribute_option_value tables.

Any suggestions?

Best Answer

Try following query.

$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 ) 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