How to count disabled products with no images from querey in magento2?

Question

Hi i have many products in my store with no pictures , some of them are enable and some of them are disabled , i wants to count those products only via sql query which are disabled as well as no thumbnail or picture stored.

Please help !

Answer 1

To check Product and images via sql query you have to query 4 tables,

1 - catalog_product_entity ( for product ids )

2 - catalog_product_entity_media_gallery_value_to_entity ( for image and product relationship )

3 - catalog_product_entity_media_gallery ( for images path )

4 - catalog_product_entity_int ( for enabled disabled )

Answer 2

Here i am sharing code which is used for retrieve product with disable status and no images.

$collection = $productCollection->create()
        ->addAttributeToSelect('*') >addAttributeToFilter('status',\Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_DISABLED)
        ->addAttributeToFilter('small_image', array('neq' => "no_selection"));


$collectionCount = $collection->count();
var_dump($collectionCount); //Get Collection Count

// Print Product Name
foreach ($collection as $product){
   echo 'Name  =  '.$product->getName().'<br>';
}

Direct Sql Query for retrieve disable product status with no image.

SELECT e.*, IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS status, IF(at_small_image.value_id > 0, at_small_image.value, at_small_image_default.value) AS small_image FROM catalog_product_entity AS e INNER JOIN catalog_product_entity_int AS at_status_default ON (at_status_default.entity_id = e.entity_id) AND (at_status_default.attribute_id = '97') AND at_status_default.store_id = 0 LEFT JOIN catalog_product_entity_int AS at_status ON (at_status.entity_id = e.entity_id) AND (at_status.attribute_id = '97') AND (at_status.store_id = 1) INNER JOIN catalog_product_entity_varchar AS at_small_image_default ON (at_small_image_default.entity_id = e.entity_id) AND (at_small_image_default.attribute_id = '88') AND at_small_image_default.store_id = 0 LEFT JOIN catalog_product_entity_varchar AS at_small_image ON (at_small_image.entity_id = e.entity_id) AND (at_small_image.attribute_id = '88') AND (at_small_image.store_id = 1) WHERE (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '2') AND (IF(at_small_image.value_id > 0, at_small_image.value, at_small_image_default.value) != 'no_selection')