Created
January 31, 2020 19:30
-
-
Save willboudle/dc22d35db2a04ae463cc700fccf806fa to your computer and use it in GitHub Desktop.
M2 - Delete Gallery Image Where Not like sku.jpg or Seconday Image.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DELETE | |
galval, | |
gal | |
FROM | |
catalog_product_entity_media_gallery_value galval | |
JOIN catalog_product_entity_media_gallery gal | |
ON galval.value_id = gal.value_id | |
WHERE galval.value_id IN | |
(SELECT | |
value_id | |
FROM | |
(SELECT | |
galval.value_id AS value_id | |
FROM | |
catalog_product_entity cpe | |
LEFT JOIN catalog_product_entity_varchar AS image | |
ON cpe.row_id = image.row_id | |
AND image.attribute_id = 87 #image | |
AND image.store_id = 0 | |
LEFT JOIN catalog_product_entity_media_gallery_value AS galval | |
ON (cpe.row_id = galval.row_id) | |
LEFT JOIN catalog_product_entity_media_gallery AS gal | |
ON (galval.value_id = gal.value_id) | |
WHERE image.value LIKE CONCAT( | |
'%', | |
TRIM('-1' FROM SUBSTRING(cpe.sku, 4)), | |
'%' | |
) # where image like sku with or without last "-1" as many images don't have it | |
AND gal.value NOT LIKE CONCAT( | |
'%', | |
TRIM('-1' FROM SUBSTRING(cpe.sku, 4)), | |
'%' | |
) # where gallery image like sku with or without last "-1" as many images don't have it | |
AND gal.value NOT REGEXP 'a[0-9].jpg' #not a secondary image a1, a2 etc) | |
) value_ids) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment