0

I need to list all product entity_id, SKU and associated images from the value field of catalog_product_entity_media_gallery.

I need the output to be CSV based, so all images of each product will be listed in their relevant columns as shown below:

entity_id,sku,image1,image2,image3,image4
23321,prod1,/d/d/dsc_dasda.jpg,/d/d/dsc_d56.jpg,/d/d/dsc_d56.jpg,/d/d/dsc_542.jpg
32132,product2,/d/d/dsc_dsss.jpg,/d/d/dsc_d34s.jpg,/d/d/dsc_d432s.jpg,,

Not all products have the same number of associated images and I am not sure what the maximum is within the catalogue so the 4 images in the headers above is just an example.

Can anyone please help? I can't figure out the joins needed in the SQL but I imagine it is pretty simple if you know how.

I have tried the Magento export options and none can give the result I need.

Magento version 1.9.0.1

  • Which magento version? – jscar Dec 4 '17 at 9:04
  • Version 1.9.0.1 – Adam C Dec 5 '17 at 9:24
0

I figured it out:

SELECT  catalog_product_entity.sku,
        GROUP_CONCAT(CONCAT_WS(':', catalog_product_entity_media_gallery.value) SEPARATOR ',') AS Gallery,
        catalog_product_entity.entity_id
FROM    catalog_product_entity_media_gallery
        catalog_product_entity_media_gallery
INNER JOIN
        catalog_product_entity catalog_product_entity
ON     (catalog_product_entity_media_gallery.entity_id =
        catalog_product_entity.entity_id)
GROUP BY
        catalog_product_entity.sku

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.