Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Magento - finding products without images. Raw SQL.
SELECT *
FROM `catalog_product_entity` AS a
LEFT JOIN `catalog_product_entity_media_gallery` AS b ON a.entity_id = b.entity_id
WHERE b.value IS NULL
@tegansnyder

This comment has been minimized.

Copy link
Owner Author

commented Jan 16, 2014

SELECT a.sku, b.value, c.position
FROM `catalog_product_entity` AS a
LEFT JOIN `catalog_product_entity_media_gallery` AS b ON a.entity_id = b.entity_id
INNER JOIN `catalog_product_entity_media_gallery_value` AS c ON c.value_id = b.value_id
WHERE a.sku = 98044057168
@inluxc

This comment has been minimized.

Copy link

commented Feb 13, 2018

Magento 2

SELECT * FROM `catalog_product_entity` AS a 
LEFT JOIN `catalog_product_entity_media_gallery_value_to_entity` AS b ON a.entity_id = b.entity_id 
WHERE b.value_id IS NULL
@pgsutariya

This comment has been minimized.

Copy link

commented May 7, 2019

Magento 2

SELECT * FROM `catalog_product_entity` AS a 
LEFT JOIN `catalog_product_entity_media_gallery_value` AS b ON a.entity_id = b.entity_id 
LEFT JOIN `catalog_product_entity_media_gallery` AS c ON b.value_id = c.value_id WHERE c.value IS NULL;
@WinstonN

This comment has been minimized.

Copy link

commented Aug 7, 2019

Hello my friends - I hope you are all doing well
Here is my code I used in Magento 2, perhaps it will help you <3

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"
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.