2

In Magento 2, how can I get the product image using only MySQL queries? For example, /c/t/ct0126black.jpg. All of my products are configurable products so they have different images for different colours.

  • u can see Magento\Catalog\Helper\Image.php. – xanka Feb 2 '16 at 9:38
  • I meant that I need to know how to do this using MySQL queries. It's for a script that I'm writing outside of Magento's framework. – Liam McArthur Feb 2 '16 at 10:48
  • @LiamMcArthur Just a note. I understand you are doing this outside of Magento but my recommendation would be to create a frontend controller to handle the query instead of a standalone script. The controller retrieves the images etc in a standard Magento collection. You would query the controller from your outside script, you get much tighter security in this way. – Eirik May 21 '17 at 0:53
3

You can use the below query.

select gal.value from catalog_product_entity as ent
left join catalog_product_entity_media_gallery_value as val on (ent.row_id= val.row_id)
left join catalog_product_entity_media_gallery as gal on (val.value_id = gal.value_id)
where ent.type_id='configurable';
0

Select all configurable products with their pathes to images

select * from catalog_product_entity ent
left join catalog_product_entity_media_gallery_value val on (val.entity_id=ent.entity_id)
left join catalog_product_entity_media_gallery gal on (gal.value_id=val.value_id)
where ent.type_id='configurable';
0

This should do the trick for Magento 2

SELECT e.entity_id AS 'id',
       e.sku AS 'sku',
       v2.value AS 'image',
       v3.value AS 'thumbnail',
       v4.value AS 'small image',
       mg.value AS 'media_gallery'
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar v2 ON e.entity_id = v2.row_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.row_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_varchar v4 ON e.entity_id = v4.row_id
AND v4.store_id = 0
AND v4.attribute_id =
  (SELECT attribute_id
   FROM eav_attribute
   WHERE attribute_code = 'small_image'
     AND entity_type_id =
       (SELECT entity_type_id
        FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'))
LEFT JOIN
  (SELECT m1.value_id AS m1_value_id, GROUP_CONCAT(m2.value) AS VALUE, m2.value_id AS m2_value_id, m1.row_id AS m1_row_id
   FROM catalog_product_entity_media_gallery_value 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.row_id) mg ON e.entity_id = m1_row_id
WHERE e.sku IN
(
"sku1",
"sku2",
"sku3"
);

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.