Една проста заявка за експорт на всички продукти на Magento с всички необходими атрибути:
Име, цена, промо цена, промо цена от-до, бранд ид,бранд, цвят и т.н.
Целта, разбира се е да експортнем данните от Магент`У и да ги налеем в наш къстъм магазин!
SELECT e.entity_id AS 'id',
e.sku,
d1.value AS 'price',
d2.value AS 'weight',
d3.value AS 'special_price',
t1.value AS 'short_description',
t2.value AS 'description',
v1.value AS 'name',
v2.value AS 'image',
v3.value AS 'thumbnail',
v4.value AS 'meta_description',
v5.value AS 'meta_keyword',
v6.value AS 'meta_title',
v7.value AS 'pol',
mg.value AS 'media_gallery',
i1.value AS 'color',
i2.value AS 'manufacturer',
i3.value AS 'status',
dt1.value AS 'special_from_date',
dt2.value AS 'special_to_date',
cids.category_ids AS 'category_ids',
cids.category_names AS 'category_names',
(SELECT v.value as manufacturer_name FROM catalog_product_entity_int i LEFT JOIN eav_attribute_option o ON i.value = o.option_id LEFT JOIN eav_attribute_option_value v ON o.option_id = v.option_id AND v.store_id = 0 LEFT JOIN catalog_product_entity e ON i.entity_id = e.entity_id WHERE i.attribute_id = 83 and i.entity_id=id AND i.store_id = 0 ) as manufacturer_title,
(SELECT v.value as color_title FROM catalog_product_entity_int i LEFT JOIN eav_attribute_option o ON i.value = o.option_id LEFT JOIN eav_attribute_option_value v ON o.option_id = v.option_id AND v.store_id = 0 LEFT JOIN catalog_product_entity e ON i.entity_id = e.entity_id WHERE i.attribute_id = 93 and i.entity_id=id AND i.store_id = 0 ) as color_title,
(SELECT v.value as pol_title FROM catalog_product_entity_varchar i LEFT JOIN eav_attribute_option o ON i.value = o.option_id LEFT JOIN eav_attribute_option_value v ON o.option_id = v.option_id AND v.store_id = 0 LEFT JOIN catalog_product_entity e ON i.entity_id = e.entity_id WHERE i.attribute_id = 171 and i.entity_id=id AND i.store_id = 0 ) as pol_title
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar v1 ON e.entity_id = v1.entity_id
AND v1.store_id = 0
AND v1.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'name'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_text t1 ON e.entity_id = t1.entity_id
AND t1.store_id = 0
AND t1.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'short_description'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_text t2 ON e.entity_id = t2.entity_id
AND t2.store_id = 0
AND t2.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'description'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v2 ON e.entity_id = v2.entity_id
AND v2.store_id = 0
AND v2.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'image'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v3 ON e.entity_id = v3.entity_id
AND v3.store_id = 0
AND v3.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'thumbnail'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_decimal d1 ON e.entity_id = d1.entity_id
AND d1.store_id = 0
AND d1.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'price'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_decimal d2 ON e.entity_id = d2.entity_id
AND d2.store_id = 0
AND d2.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'weight'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_int i1 ON e.entity_id = i1.entity_id
AND i1.store_id = 0
AND i1.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'color'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_int i2 ON e.entity_id = i2.entity_id
AND i2.store_id = 0
AND i2.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'manufacturer'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_int i3 ON e.entity_id = i3.entity_id
AND i3.store_id = 0
AND i3.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'status'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v4 ON e.entity_id = v4.entity_id
AND v4.store_id = 0
AND v4.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'meta_description'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v5 ON e.entity_id = v5.entity_id
AND v5.store_id = 0
AND v5.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'meta_keyword'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v6 ON e.entity_id = v6.entity_id
AND v6.store_id = 0
AND v6.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'meta_title'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_varchar v7 ON e.entity_id = v7.entity_id
AND v7.store_id = 0
AND v7.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'pol'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_decimal d3 ON e.entity_id = d3.entity_id
AND d3.store_id = 0
AND d3.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'special_price'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_datetime dt1 ON e.entity_id = dt1.entity_id
AND dt1.store_id = 0
AND dt1.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'special_from_date'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN catalog_product_entity_datetime dt2 ON e.entity_id = dt2.entity_id
AND dt2.store_id = 0
AND dt2.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'special_to_date'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
LEFT JOIN
(SELECT m1.entity_id,
GROUP_CONCAT(m2.value) AS value
FROM catalog_product_entity_media_gallery_value_to_entity m1
INNER JOIN catalog_product_entity_media_gallery m2 ON m2.value_id = m1.value_id
AND m2.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'media_gallery'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_product'))
GROUP BY m1.entity_id) mg ON e.entity_id = mg.entity_id
LEFT JOIN
(SELECT product_id,
GROUP_CONCAT(c.category_id SEPARATOR ',') AS category_ids,
GROUP_CONCAT(cv.value SEPARATOR ',') AS category_names
FROM catalog_category_product c
INNER JOIN catalog_category_entity_varchar cv ON c.category_id = cv.entity_id
AND cv.store_id = 0
AND cv.attribute_id =
(SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'name'
AND entity_type_id =
(SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_category'))
GROUP BY product_id) cids ON e.entity_id = cids.product_id limit 10 \G
