How can I find all products without images in Magento?

Question

I have some thousand products and want to find all products without an image. I tried to search for (no image) in the admin products grid, but no result. How can I make an SQL query that disables all these products?

Answer 1

Stop thinking in terms of SQL. Start thinking in terms of Magento's Models. Magento's models just happen to use SQL as a backend. Querying for things via raw SQL is possible, but is going to vary from version to version of the Magento, and may differ depending on the backend you're using.

Run the following from a test controller action, or somewhere else you can execute Magento code from. It queries the model for products with no image

//this builds a collection that's analagous to 
//select * from products where image = 'no_selection'
$products = Mage::getModel('catalog/product')
->getCollection()
->addAttributeToSelect('*')
->addAttributeToFilter('image', 'no_selection');

foreach($products as $product)
{
    echo  $product->getSku() . " has no image \n<br />\n";
    //var_dump($product->getData()); //uncomment to see all product attributes
                                     //remove ->addAttributeToFilter('image', 'no_selection');
                                     //from above to see all images and get an idea of
                                     //the things you may query for
}       

Answer 2

I know this is super old, but I found it helpful, so I thought I'd post an update.

As an addition to Alan's answer above, I found that there are other scenarios than just the 'no_selection' .. maybe due to plugins, or general bugs in the system? The final nlike will actually find everything, but I left the others just for fun.

Change the collection query as follows:

$products = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToSelect('*')
    ->addAttributeToFilter(array(
        array (
            'attribute' => 'image',
            'like' => 'no_selection'
        ),
        array (
            'attribute' => 'image', // null fields
            'null' => true
        ),
        array (
            'attribute' => 'image', // empty, but not null
            'eq' => ''
        ),
        array (
            'attribute' => 'image', // check for information that doesn't conform to Magento's formatting
            'nlike' => '%/%/%'
        ),
    ));

Answer 3

I tried all of these answers in various combinations, and only got a small subset of my catalogue returned. The reason: I originally imported my products using a bespoke product image import script.

If I didn't specify images for some rows during import, the script did not create NULL or empty attribute values for those images. It simply did not create the attribute rows at all.

Since addAttributeToFilter uses an INNER join by default and there was no image attribute value to join to, the queries posted here didn't catch those SKUs.

The code below returns all products for which image, small_image or thumbnail are null, incorrectly formatted, or the row is missing entirely.

The third parameter to addAttributeToFilter allow you to specify the type of join to be used in conjunction with the OR clauses of the WHERE statement.

$products = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToSelect('*')
    ->addAttributeToFilter(
        array(
            array(
                'attribute' => 'image',
                'null' => '1'
            ),
            array(
                'attribute' => 'small_image',
                'null' => '1'
            ),
            array(
                'attribute' => 'thumbnail',
                'null' => '1'
            ),
            array(
                'attribute' => 'image',
                'nlike' => '%/%/%'
            ),
            array(
                'attribute' => 'small_image',
                'nlike' => '%/%/%'
            ),
            array(
                'attribute' => 'thumbnail',
                'nlike' => '%/%/%'
            )
        ),
        null,
        'left'
    );

If, like me, you want to convert this to a SQL query to export as a CSV from your SQL client, simply print the query from the PHP script:

echo $products->getSelect();

I've seen some SQL posted on StackOverflow which hard-codes the attribute_id integers that refer to the image, small_image and thumbnail attributes, but these can differ from one install to another. In Magento, querying with the ORM is much better than with the SQL.

Answer 4

also, to get the sql that the query Alan describes runs behind the scenes:

echo (string) $products->getSelect();