1

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 !

2

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 )

1

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') 
  • can we retrieve it via sql query? is it possible – Hamza Mustafa Mar 24 '18 at 9:46
  • @HamzaMustafa, I had update my answer.. – Ravi Dudhara Mar 24 '18 at 10:07
  • 1
    it is returning disabled products with images but thanks , i am not able to do it without your help ,thanks – Hamza Mustafa Mar 24 '18 at 10:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.