Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ```
- SELECT e.entity_id AS 'id',
- v1.value AS 'name',
- e.sku,
- d1.value AS 'price',
- t1.value AS 'short_description',
- t2.value AS 'description',
- v2.value AS 'image',
- v3.value AS 'thumbnail',
- mg.value AS 'media_gallery',
- cids.category_ids AS 'category_ids',
- cids.category_names AS 'category_names'
- 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
- (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 ;
- ```
Add Comment
Please, Sign In to add comment