How to get recently viewed products by customer id?

Question

I want to expose through a SOAP WS the most recently viewed items of a customer.

How can I reach those items? I know they're stored in 'reports/product_index_viewed'; however, I don't know which is the right way to reach those.

Here's what I got so far:

public function getRecentlyViewedByCustomer($customerId)
{
    Mage::log(__METHOD__);
    $customer = $this->_getCustomer($customerId);
    Mage::log('Getting recently viewed products of '. $customer->getName() .' ('. $customer->getEmail() .'), ID: ' . $customer->getId() );

    $productCollection = Mage::getResourceModel('reports/product_index_viewed');

    Mage::log(print_r($productCollection, true));

    return __METHOD__;
}

public function _getCustomer($customerId)
{
    $customer = Mage::getModel('customer/customer')->load($customerId);
    return $customer;
}

Answer 1

Here's how I ended up solving this problem

public function getRecentlyViewedByCustomer($customerId, $categoryId, $limit = 5){
    $resource = Mage::getSingleton('core/resource');
    $readConnection = $resource->getConnection('core_read');

    $q = "SELECT DISTINCT report_viewed_product_index.product_id, report_viewed_product_index.added_at "  .
    " FROM report_viewed_product_index " .
    " INNER JOIN catalog_category_product ON catalog_category_product.product_id = report_viewed_product_index.product_id " .
    " WHERE customer_id = " . $customerId;

    if($categoryId > 0){
        $categories = $this->_getCategories($categoryId);
        $q = $q . " AND category_id in (" . $categories . ")";
    }

    $q = $q . " ORDER BY added_at desc LIMIT " . $limit;

    return $readConnection->fetchAll($q);
}

Answer 2

Based on @Ramses answer I've created a method that also can return guests viewed products:

public function getRecentlyViewed()
{
    $customerSession = Mage::getSingleton('customer/session');
    if ($customerSession->isLoggedIn()) {
        $field = 'customer_id';
        $id = $customerSession->getCustomerId();
    } else {
        $field = 'visitor_id';
        $id = Mage::getSingleton('core/session')->getData('visitor_data')['visitor_id'];
    }

    $limit = 6;

    $resource = Mage::getSingleton('core/resource');
    $readConnection = $resource->getConnection('core_read');

    $reportsTbl = $resource->getTableName('reports/viewed_product_index');
    $catProdsTbl = $resource->getTableName('catalog/category_product');

    $query = <<<SQL
        SELECT DISTINCT {$reportsTbl}.product_id, {$reportsTbl}.added_at  
         FROM {$reportsTbl} 
         INNER JOIN {$catProdsTbl} ON {$catProdsTbl}.product_id = {$reportsTbl}.product_id 
         WHERE {$field} =  {$id}
         ORDER BY added_at desc LIMIT {$limit};
SQL;

    return $readConnection->fetchAll($query);
}

Answer 3

public function getMostViewedProducts()
{       
    /**
     * Number of products to display
     * You may change it to your desired value
     */
    $productCount = 5; 

    /**
     * Get Store ID
     */
    $storeId    = Mage::app()->getStore()->getId();       

    /**
     * Get most viewed product collection
     */
    $products = Mage::getResourceModel('reports/product_collection')
        ->addAttributeToSelect('*')     
        ->setStoreId($storeId)
        ->addStoreFilter($storeId)
        ->addViewsCount()
        ->setPageSize($productCount); 

    Mage::getSingleton('catalog/product_status')
            ->addVisibleFilterToCollection($products);
    Mage::getSingleton('catalog/product_visibility')
            ->addVisibleInCatalogFilterToCollection($products);

    return $products; 
}

Answer 4

You should add an observer witch detect that a user is viewing a product and return the product id and the customer id and stock it in the database so you could use it