Magento: Get all attribute information of a category

A query that extracts all the attribute information of a category in Magento.

SELECT cce.*, ea.attribute_code, 
    CASE ea.backend_type 
       WHEN 'datetime' THEN cce_datetime.value
       WHEN 'decimal' THEN cce_decimal.value
       WHEN 'int' THEN cce_int.value
       WHEN 'text' THEN cce_text.value
       WHEN 'varchar' THEN cce_varchar.value
       ELSE NULL
    END AS value
FROM catalog_category_entity AS cce
LEFT JOIN eav_attribute AS ea ON cce.entity_type_id = ea.entity_type_id
LEFT JOIN catalog_category_entity_decimal AS cce_decimal ON cce.entity_id = cce_decimal.entity_id AND ea.attribute_id = cce_decimal.attribute_id AND ea.backend_type = 'decimal'
LEFT JOIN catalog_category_entity_datetime AS cce_datetime ON cce.entity_id = cce_datetime.entity_id AND ea.attribute_id = cce_datetime.attribute_id AND ea.backend_type = 'datetime'
LEFT JOIN catalog_category_entity_int AS cce_int ON cce.entity_id = cce_int.entity_id AND ea.attribute_id = cce_int.attribute_id AND ea.backend_type = 'int'
LEFT JOIN catalog_category_entity_text AS cce_text ON cce.entity_id = cce_text.entity_id AND ea.attribute_id = cce_text.attribute_id AND ea.backend_type = 'text'
LEFT JOIN catalog_category_entity_varchar AS cce_varchar ON cce.entity_id = cce_varchar.entity_id AND ea.attribute_id = cce_varchar.attribute_id AND ea.backend_type = 'varchar'
where cce.entity_id = 1;

A new category created in Magento will add a record in the table catalog_category_entity. The attribute information such as category name, url_key, url_path, thumbnail image, meta description, meta_keywords, etc are stored accordingly in tables catalog_category_entity_datetime, catalog_category_entity_decimal, catalog_category_entity_int, catalog_category_entity_text, and catalog_category_entity_varchar, depending onthe data type.

Search within Codexpedia

Custom Search

Search the entire web

Custom Search