Home Coding Magento – Get the list of products which customer wanted to be notify when it is back in stock

Magento – Get the list of products which customer wanted to be notify when it is back in stock

by Benny

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;

You may also like

Leave a Comment