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 !
Magento Stack Exchange is a question and answer site for users of the Magento e-Commerce platform. It only takes a minute to sign up.
Sign up to join this communityTo 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 )
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')