Sometimes when you are working on a webstore, your client might ask you totally reasonable questions like how many products have no images uploaded. To answer this question is not so easy when there are 3000+ products in said shop. As the admin interface doesn’t provide an easy way to check for thumbnail-less products, I had to turn to some SQL magic to answer the question.

Unfortunately I’m not smart enough to come up with the needed SQL query myself, I have to give much deserved credit to my colleague András Bán for the solution. Still, no shame as he is a professional backend developer, while I’m only an ex-dev PM 🙂

The search started with google of course where I found this query:

SELECT * FROM `catalog_product_entity` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity_media_gallery_value_to_entity`)

This is very promising, but unfortunately lists all products that have no images regardless if they are disabled or not.

After a dozen more dead ends I asked for András’s help and he opened with a query that lists all enabled products:

SELECT entity_id FROM `catalog_product_entity_int`
WHERE attribute_id = (
    SELECT attribute_id FROM `eav_attribute`
    WHERE `attribute_code` LIKE 'status'
) AND `catalog_product_entity_int`.value = 1

Now all we (I mean he) had to do is to combine them like this:

Or maybe like this:

SELECT * FROM `catalog_product_entity` WHERE `entity_id` NOT IN (SELECT `entity_id` FROM `catalog_product_entity_media_gallery_value_to_entity`) AND `entity_id` IN (SELECT entity_id FROM `catalog_product_entity_int` WHERE attribute_id = ( SELECT attribute_id FROM `eav_attribute` WHERE `attribute_code` = 'status' ) AND `catalog_product_entity_int`.value = 1)

The query worked perfectly, saved it for future use. If you need to modify it to show products that are disabled, switch the “1” at the end of the query to “2”.