746
In Magento 2.4, customer can request to be notified of product which is back in stock. However, to view which product has customer requested to be notified, we need to go to each of the product detail page and view the information under product alert.
Hence, the other way to retrieve the list at one go is to query from the database.
Below is the SQL to extract the list of products and customers who wish to be notified when the products are back in stock.
SELECT cp.sku, pa.status, pa.add_date, pa.send_date, ce.firstname FROM `product_alert_stock` pa
inner join `catalog_product_entity` cp
on pa.product_id = cp.entity_id
inner join `customer_entity` ce
on pa.customer_id = ce.entity_id
ORDER BY `cp`.`sku` ASC;
And the below SQL to join the media gallery so that you can paste it in google sheet to show the photo image
SELECT cp.sku, pa.status, pa.add_date, pa.send_date, ce.firstname, concat('=IMAGE("https://www.yourdomain.com/media/catalog/product/', cpemg.value, '")') as imageurl FROM `product_alert_stock` pa
inner join `catalog_product_entity` cp
on pa.product_id = cp.entity_id
inner join `customer_entity` ce
on pa.customer_id = ce.entity_id
inner join `catalog_product_entity_media_gallery_value` cpemgv
on cpemgv.entity_id = pa.product_id
inner join catalog_product_entity_media_gallery cpemg
on cpemgv.value_id = cpemg.value_id
where pa.status = 0
group by cpemgv.entity_id
ORDER BY pa.add_date desc;
