Magento – How to get all attribute value and label of a child product of a configurable product using by direct sql

attributesmagento-1.9sql

Anyone know How to get all attribute value and label of a child product of a configurable product using by direct sql?

I only know the way to find out what attributes of a configurable is using:

select * FROM `catalog_product_super_attribute` where product_id = 421

the result is 

product_super_attribute_id  product_id  attribute_id    position
53                          421         92              0
54                          421         180             0

the product 421 has the following child products

//294, 295, 296, 518, 519, 888, 889, 890, 891, 892, 893, 894, 895, 896

because i don't know how the product is linking to a attribute/label/value, so how to get linked attributes label and value of the child products?

—Update—-
just found some a useful sql, alter it can get my wanted result

SET @entityid = 294;

SELECT ea.attribute_code, eav.value AS 'value', 'varchar' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_varchar eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'int' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_int eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'decimal' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_decimal eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'datetime' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_datetime eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid
UNION
SELECT ea.attribute_code, eav.value AS 'value', 'text' AS 'type'
FROM catalog_product_entity e
JOIN catalog_product_entity_text eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE e.entity_id = @entityid

Best Answer

You can get by magento way attribute label/value by the using of following code:

    $configProductId=1;
    $configProductIdObj = Mage::getModel('catalog/product')->load($configProductId);
    $childProductsColl = Mage::getModel('catalog/product_type_configurable')->getUsedProductCollection($configProductIdObj)->addAttributeToSelect('*');
    if(count($childProductsColl)>0){
       foreach($childProductsColl as $child) {
           $associatedProductIds[]=$child->getId();
           $smproduct = Mage::getModel('catalog/product')->load($child->getId());
            //$smproduct->getId();
            //$smproduct->getName();
            //$smproduct->getDescription();
            //$smproduct->getFoodSize();//dropdown attribute value
            //$smproduct->getAttributeText('food_size');//dropdown attribute label
        }
    }
Related Topic