Magento – How to join a calculated column from a table to collection

collection;customergridjoin;sql

I have a table name stat_customer look like this:

stat_customer table

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

Best Answer

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.

Related Topic