Magento - SQL command to set "exclude" on product images

Magento - SQL command to set "exclude" on product images

August 29, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento - SQL command to set "exclude" on product images

Magento: SQL Query to Set “Exclude” on Single Product Images

As an experienced developer working with Magento e-commerce platforms, I often encounter situations where clients need to exclude specific product images based on certain criteria. In this blog post, we’ll explore how to use SQL queries to set the “exclude” flag for products that have only one image attached.

Before diving into the solution, it’s essential to understand the structure of the catalog_product_entity_media_gallery and catalog_product_entity_media_gallery_value tables in Magento. These tables store information about product images and their corresponding gallery values.

  • The catalog_product_entity_media_gallery table stores the main image information for each product.
  • The catalog_product_entity_media_gallery_value table contains additional details like file names, labels, and disabled flags (which represent whether an image is excluded from the gallery).

Setting the “Exclude” Flag for Products with a Single Image

The client’s initial inquiry points to a SQL query that sets all images as enabled. However, they want to exclude only those products with exactly one image attached while keeping products with multiple images unaffected.

To achieve this, we can use a single SQL query that updates the disabled flag in the catalog_product_entity_media_gallery_value table based on the count of images for each product.

Here’s the complete SQL command:

UPDATE catalog_product_entity_media_gallery_value cpegv
JOIN (
    SELECT
        parent_id,
        COUNT(*) AS image_count
    FROM
        catalog_product_entity_media_gallery
    GROUP BY
        parent_id
) cnt ON cpegv.entity_id = cnt.parent_id
SET
    cpegv.disabled = CASE
        WHEN cnt.image_count = 1 THEN 1
        ELSE 0
    END;

Let’s break down the query:

  • The subquery selects each product’s parent_id and counts the number of images associated with it.
  • We join this count result with the main table, catalog_product_entity_media_gallery_value, using the entity_id column as a common field.
  • Finally, we update the disabled flag based on the image_count:
    • If the product has only one image (image_count = 1), set disabled to 1 (exclude the single image).
    • For products with more than one image (image_count > 1), leave the disabled flag as 0.

Importance of Backup and Testing

Before executing any SQL queries, it’s crucial to:

  • Create a database backup to ensure data recovery if something goes wrong.
  • Test the query on a staging environment that mirrors your production setup.
  • Monitor the changes closely after running the query to confirm they align with your expectations.

Conclusion

In this post, we explored how to use SQL queries in Magento to set the “exclude” flag for products with only one image attached. By joining and comparing the relevant tables, we can efficiently manage product images based on specific criteria without affecting other products.

Remember to always prioritize data safety by creating backups and thoroughly testing changes before applying them to your production environment. If you have any further questions or need more complex customizations for your Magento store, feel free to reach out to our team at Laravel Company.

Happy coding!

Catalog Database Configuration CLI

Our network