2 posts tagged

sql

Find Products Without Images

SELECT `p`.`sku` FROM `catalog_product_entity` AS `p`
LEFT JOIN `catalog_product_entity_varchar` AS `attr_images`
	ON `attr_images`.`entity_id` = `p`.`entity_id`
	AND `attr_images`.`attribute_id` IN (SELECT `attribute_id` FROM `eav_attribute`
			WHERE `attribute_code` IN ('image', 'small_image', 'thumbnail') AND `entity_type_id` = 4
		)
LEFT JOIN `catalog_product_entity_media_gallery` AS `gallery`
	ON `gallery`.`entity_id` = `p`.`entity_id`

WHERE (`attr_images`.`value` IS NULL OR `attr_images`.`value` = 'no_selection') AND (`gallery`.`value` IS NULL);
Apr 26   gallery   images   product   sql

Hide all categories without products and show categories with products

Reindex category products index at first and then execute following sql-script.

UPDATE `catalog_category_entity_int` AS `status`
INNER JOIN `eav_attribute` AS `attr` ON `attr`.`attribute_code` = 'is_active'
AND `attr`.`entity_type_id` = 3
AND `status`.`attribute_id` = `attr`.`attribute_id`
SET `status`.`value` = IF((SELECT COUNT(`index`.`product_id`)
	FROM `catalog_category_product_index` AS `index`
	WHERE `index`.`category_id` = `status`.`entity_id` GROUP BY `index`.`category_id`) > 0, 1, 0)
WHERE `status`.`store_id` = 0
2016   category   product   sql