Magento: Get products without images using collections

1
votes

I would like to filter a product collection by image not null. The easiest way should be the methods mentioned here, but this doesn't work: the returned collection has no elements.

I think that the problem here is that products that never had an image have no relationship between product/media_gallery tables. As a consequence, when Magento tries to filter using all these conditions, the returned collection is empty. It doesn't take into account that it might not be any type of relationship between the involved tables. 

$collection->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' => '%/%/%'
        ),
    ));

I guess I should use a joinLeft condition, but I have not idea how it should be. Could please anyone help me on this?

I found a very interesting query that should do the trick. But I need to apply this to my collection:

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

Thanks

2
magento

2 Answers

4
votes

To apply the query to your products collection, you can use 

$collection->getSelect()
    ->joinLeft(
        array('_gallery_table' => $collection->getTable('catalog/product_attribute_media_gallery')),
        'e.entity_id = _gallery_table.entity_id',
        array()
    )
    ->where('_gallery_table.value IS NULL');
1
votes

Same answer but for magento 2:

use Magento\Catalog\Model\ResourceModel\Product\CollectionFactory;
........
/**
 * @var CollectionFactory
 */
public $productCollectionFactory;
........
$collection = $this->productCollectionFactory->create();
$collection->addAttributeToSelect('*');
$collection->getSelect()
    ->joinLeft(
        ['gallery_table' => $collection->getTable('catalog_product_entity_media_gallery_value_to_entity')],
        'e.entity_id = gallery_table.entity_id',
        []
    )
    ->where('gallery_table.value_id IS NULL');