How can I see a list of products that customers have asked to be alerted on?

Question

I was looking in the admin area for a place to see products customers have chosen to be alerted on. This seems like a useful feature considering you may want to re-stock those products since they will sell fast. I couldn't find any way to do it though. I also want to use it for testing to make sure these are working correctly.

Also, while reading this other question (Are stock and price alerts ever removed?) it made me think there was a way for customers to see products they have chosen to be alerted on, and unsubscribe from those alerts, however I can't find a way to do that either.

I found this article with a way to list them out for each customer (http://magenting.wordpress.com/2011/12/05/get-customer-product-alert-subscriptions-list-in-magento/), but I was wondering if there was any built in functionality for this.

Are either of these things possible? I'm using Magento 1.6.2.0

Answer 1

You can show the products alerts statistics in admin for all customers using collection like

protected function _prepareCollection()
{
    $productsTable = Mage::getSingleton('core/resource')->getTableName('catalog/product');
    $c = Mage::getModel('productalert/stock')->getCollection();
    $c->getSelect()
        ->columns(array('cnt' => 'count(*)', 'last_d'=>'MAX(add_date)', 'first_d'=>'MIN(add_date)'))
        ->joinInner(array('e'=> $productsTable), 'e.entity_id = product_id', array('sku'))
        ->where('send_count=0')
        ->group(array('website_id', 'product_id'))
    ;

    $this->setCollection($c);
    return parent::_prepareCollection();
} 

It's form the free alerts extension.