Magento aggregate collections?

Question

I need to get customer life time sales data. Both Qty and Amount.

I have tried below Mysql Query it's worked fine.

How can i change this Mysql query in Magento collection format.

Because this data showing in Magento Grid.

select CAST(SUM( t.qty )as UNSIGNED) AS `qty`,  SUM( t.grand_total )as grand_total, t.customer_id , t.customer_name, t.customer_email_id,  date_format(t.customer_dob, '%d-%m-%Y') as customer_dob from (

SELECT `main_table`.`entity_id`, `main_table`.`customer_id`, `main_table`.`grand_total`, `cusFirstnameTb`.`value` AS `customer_first_name`, `cusLastnameTb`.`value` AS `customer_last_name`, CONCAT(cusFirstnameTb.value, ' ', cusLastnameTb.value) AS `customer_name`, `cusDOBTb`.`value` AS `customer_dob`, `cusEmailTb`.`email` AS `customer_email_id`, `sub`.`qty_canceled`, `sub`.`qty_refunded`, `sub`.`qty_ordered`, CAST(SUM( qty_ordered )as UNSIGNED) AS qty, CAST(SUM( grand_total )as UNSIGNED) AS grand_total1, SUM( grand_total ) AS `grand_total2` FROM `sales_flat_order_grid` AS `main_table` 

INNER JOIN `customer_entity_varchar` AS `cusFirstnameTb` ON main_table.customer_id = cusFirstnameTb.entity_id AND cusFirstnameTb.attribute_id = 5 AND cusFirstnameTb.entity_type_id = 1 

INNER JOIN `customer_entity_varchar` AS `cusLastnameTb` ON main_table.customer_id = cusLastnameTb.entity_id AND cusLastnameTb.attribute_id = 7 AND cusLastnameTb.entity_type_id = 1 

INNER JOIN `customer_entity_datetime` AS `cusDOBTb` ON main_table.customer_id = cusDOBTb.entity_id AND cusDOBTb.attribute_id = 11 AND cusDOBTb.entity_type_id = 1 

INNER JOIN `customer_entity` AS `cusEmailTb` ON main_table.customer_id = cusEmailTb.entity_id AND cusEmailTb.entity_type_id = 1 

INNER JOIN `sales_flat_order_item` AS `sub` ON main_table.entity_id = sub.order_id 

WHERE ( cusDOBTb.value = "2014-08-21" ) 

GROUP BY `order_id` 

ORDER BY `order_id` ASC

) t GROUP BY t.customer_id 

This result i get from PhpMyadmin, run above query .

Thanks for your help

Answer 1

Doesn't seem that hard. Basically you would have to instantiate the customer collection, join it with the sales collections and apply some conditions. The sales table has most of the information you'll need except for the dob.

$collection = Mage::getResourceModel('customer/customer_collection')
        ->joinAttribute('dob','customer/dob', 'entity_id')
        ->join(array('orders'=>'sales_flat_order',
                     'orders.customer_id=main_table.entity_id', 
                     array('total_item_count','base_grand_total', 'customer_firstname', 'customer_lastname')))
        ->getSelect()
        ->reset(Zend_DB_Select::COLUMNS)
        ->columns(array(
                        'SUM(orders.total_item_count)'=>'qty',
                        'SUM(orders.base_grand_total)'=>'grand_total',
                        'orders.customer_firstname',
                        'orders.customer_lastname',
                        'dob'))
        ->group('main_table.entity_id')
        ->where('dob = SOMETHING');

I haven't tested it but this is how I would write it. Be sure to enable SQL logging in your Magento and adjust the collection according to the SQL result.

To format the dob as you wanted, I suggest using a renderer.

If you have any questions, feel free to ask.

Answer 2

Try this

$firstname = Mage::getResourceModel('customer/customer')->getAttribute('firstname');     
    $lastname  = Mage::getResourceModel('customer/customer')->getAttribute('lastname');
    $dob = Mage::getResourceModel('customer/customer')->getAttribute('dob');     

        $Collection=Mage::getResourceModel('sales/order_collection')->addFieldToSelect(array('customer_id','customer_email'))
        ->addFieldToFilter('customer_id',array('notnull'=>true));
        $Collection->getSelect()

            ->columns(array('ordered_qty' => 'SUM(main_table.total_qty_ordered)'))
            ->columns(array('ordered_total' => 'SUM(main_table.grand_total)'))

            ->group('main_table.customer_id');
        $Collection->getSelect()
            ->joinLeft(
            array('customer_lastname_table' => $lastname->getBackend()->getTable()),
            'customer_lastname_table.entity_id = main_table.customer_id
            AND customer_lastname_table.attribute_id = '.(int) $lastname->getAttributeId() . '
            ',
            array('customer_lastname'=>'value')
            )
            ->joinLeft(
            array('customer_firstname_table' =>$firstname->getBackend()->getTable()),
            'customer_firstname_table.entity_id = main_table.customer_id
            AND customer_firstname_table.attribute_id = '.(int) $firstname->getAttributeId() . '
            ',
            array('customer_firstname'=>'value'))
            ->joinLeft(
            array('customer_dob_table' => $dob->getBackend()->getTable()),
            'customer_dob_table.entity_id = main_table.customer_id
            AND customer_dob_table.attribute_id = '.(int) $dob->getAttributeId() . '
            ',
            array('customer_dob'=>'value')
            )


            ->columns(new Zend_Db_Expr("CONCAT(IFNULL(customer_firstname_table.value,''),' ',IFNULL(customer_lastname_table.value,'')) AS fullname"))
            ->columns('*'); 
        echo $Collection->getSelect();