Exporting Magento Product Prices with Configurable Product Super Options

A client today needed to export their pricing for products. This is normally not hard, the admin provides a simple system to do it. But, my client was using related products with pricing modifications involving super attributes and options. Think "Shirt X costs $4.99. Large is another $2 and XL is $3 more". That doesn't export well, and the table storage for the values is a huge mystery at first. Here's the finished solution to get those prices.

First, a couple important concepts:

  1. The base price of the product is stored in catalog_product_entity_decimal, it's just an attribute value.
  2. The configurable pricing modifications are stored in catalog_product_super_attribute_pricing which relates directly from that table to both the eav_attribute, and eav_attribute_option_value tables directly
  3. The attribute ids in my system may not match yours, for me, 71 is 'name' of the product.

Here's the queries you'll need to pull the info, hope they make sense!

Base product price:

  1. SELECT cped.`value` AS `price`, cpe.sku, cpev.value AS `name`
  2. FROM catalog_product_entity_decimal cped
  3. JOIN catalog_product_entity cpe ON cpe.entity_id = cped.entity_id
  4. JOIN catalog_product_entity_varchar cpev ON cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 71
  5. WHERE cped.attribute_id IN (
  6.         SELECT attribute_id FROM eav_attribute WHERE attribute_code IN ('price')
  7. )
  8. AND cped.`value` > 0;
  9. ORDER BY cpe.sku

Pricing modifications:

  1. SELECT cpe.sku, cpev.value AS `name`, ea.attribute_code, cpsal.value AS attribute_label, eaov.value AS option_label, cpsap.pricing_value AS price_modification
  2. FROM catalog_product_super_attribute_pricing cpsap
  3. JOIN catalog_product_super_attribute_label cpsal ON cpsal.product_super_attribute_id = cpsap.product_super_attribute_id
  4. JOIN catalog_product_super_attribute cpsa ON cpsa.product_super_attribute_id = cpsap.product_super_attribute_id
  5. JOIN catalog_product_entity cpe ON cpe.entity_id = cpsa.product_id
  6. JOIN eav_attribute ea ON ea.attribute_id = cpsa.attribute_id
  7. JOIN catalog_product_entity_varchar cpev ON cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 71
  8. JOIN eav_attribute_option_value eaov ON eaov.option_id = cpsap.value_index
  9. ORDER BY cpe.sku
Tags: 

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.