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.
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.
Best Answer
Assuming you have a model mapped to your
table1and one mapped totable2you can try something like this.And for the second one.
The examples above are for flat entities.
If you have an EAV entity similar to products or categories use
addAttributeToFilterinstead ofaddFieldToFilteraddExpressionAttributeToSelectinstead ofaddExpressionFieldToSelectEDIT
After an other investigation I found that for flat entities you can use directly the column names. The attribute placeholders
{{...}}are needed only for EAV entities.So your second collection should look like this:
The third parameter for
addExpressionFieldToSelectis required but it can be an empty array. foraddExpressionAttributeToSelectthat last parameter should contain the mapping between the placeholders and the attribute codes. In this case seams that they are useless. I don't know if this is a bug or a feature.