How to join a calculated column from a table to collection

Question

I have a table name stat_customer look like this:

I want to add a column page_view to customer collection to show at grid page. page_view is total page view of product page for each customer. My SQL query looks like:

SELECT sum(page_view) as page_view, customer_id FROM stat_customer WHERE page_type = 'product' GROUP BY customer_id;

But I don't know how to join it with customer collection. Can anyone help me please ?

UPDATE

I'm trying this way:

protected function _prepareCollection()
    {
      $collection = Mage::getResourceModel('customer/customer_collection')
        ->addNameToSelect()
        ->addAttributeToSelect('email')
        ->addAttributeToSelect('created_at')
        ->addAttributeToSelect('group_id')
        ->joinAttribute('company', 'customer_address/company', 'default_billing', null, 'left');
      $collection->addExpressionAttributeToSelect("page_view", "sum({{page_view}})", "page_view");
      $collection->groupByAttribute("page_view");
      $this->setCollection($collection);
      return parent::_prepareCollection();
    }

But getting this error:

Fatal error: Call to a member function getBackend() on a non-object in E:\XAMPP\htdocs\stat\app\code\core\Mage\Eav\Model\Entity\Collection\Abstract.php on line 513

Answer 1

Have a look at the methods Mage_Core_Model_Resource_Db_Collection_Abstract::addExpressionFieldToSelect() for standard models:

/**
 * Add attribute expression (SUM, COUNT, etc)
 * Example: ('sub_total', 'SUM({{attribute}})', 'revenue')
 * Example: ('sub_total', 'SUM({{revenue}})', 'revenue')
 * For some functions like SUM use groupByAttribute.
 *
 * @param string $alias
 * @param string $expression
 * @param array $fields
 * @return Mage_Core_Model_Resource_Db_Collection_Abstract
 */
public function addExpressionFieldToSelect($alias, $expression, $fields)

And Mage_Eav_Model_Entity_Collection_Abstract::addExpressionAttributeToSelect() for EAV models:

/**
 * Add attribute expression (SUM, COUNT, etc)
 *
 * Example: ('sub_total', 'SUM({{attribute}})', 'revenue')
 * Example: ('sub_total', 'SUM({{revenue}})', 'revenue')
 *
 * For some functions like SUM use groupByAttribute.
 *
 * @param string $alias
 * @param string $expression
 * @param string $attribute
 * @return Mage_Eav_Model_Entity_Collection_Abstract
 */
public function addExpressionAttributeToSelect($alias, $expression, $attribute)

Usage

$collection->addExpressionFieldToSelect("page_view", "sum({{page_view}})", ["page_view"]);
//                                      ^ alias      ^ expression          ^ used fields

$collection->getSelect()->group("main_table.entity_id");

or

$collection->addExpressionAttributeToSelect("page_view", "sum({{page_view}})", "page_view");
//                                          ^ alias      ^ expression          ^ used attribute

$collection->getSelect()->group("main_table.entity_id");

Note that the method suggested in the doc block, groupByAttribute() is only used to group by EAV attributes, not static columns like the entity id.

Answer 2

I've found my answered by using the following join:

$statTable = Mage::getSingleton('core/resource')->getTableName('stat_customer');
    $collection->getSelect()
      ->join(array("stat" => $statTable), "e.entity_id = stat.customer_id", array("*","sum(stat.page_view) as page_view" ))
      ->where('stat.page_type = "product"')
      ->group('e.entity_id');

It works great.

Answer 3

First,

Need to add this table magento to Customer collection on prepareCollection() at class Mage_Adminhtml_Block_Customer_Grid

$subSelect =  Mage::getSingleton( 'core/resource' )->getConnection( 'core_read' )->select()
                 ->from(array('o' =>'stat_customer'), 'SUM(o.page_view)')
                 ->where('o.customer_id = e.entity_id ')
        $subSelect->where('o.page_type = ?', 'product') ;

$collection->getSelect()->columns(array('page_view_count' => $subSelect))->group("o.customer_id");

Second,

You need to this column on grid using addColumn function _prepareColumns() (class Mage_Adminhtml_Block_Customer_Grid )

protected function _prepareColumns()
{
    parent::_prepareColumns();
    $this->addColumn('page_view',
        array(
            'header'=> Mage::helper('customer')->__('Page View'),
            'width' => '50px',
            'index' => 'page_view_count',
    ));
    return parent::_prepareColumns();
}