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 Answers 2

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 )

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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