Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

From SQL to PHP
 
TimRigg
Jr. Member
 
Total Posts:  9
Joined:  2009-02-26
 

Hi all !

I’m customizing the order grid in the back-end. I would like to display a number in a new column, that corresponds to the sum, for each product of the order, of an attribute I created.

Here is the SQL request that returns me the collection I want:

SELECT `e`.*, SUM(`_table_ma_table`.`value`*`_table_product_id`.`qty_ordered`) AS `total`,
            
FROM `sales_order` AS `e

LEFT JOIN `sales_flat_order_item` AS `_table_product_id
    
ON (_table_product_id.order_id=e.entity_id

LEFT JOIN `catalog_product_entity_varchar` AS `_table_ma_table
    
ON (_table_ma_table.entity_id _table_product_id.product_id) AND (_table_ma_table.attribute_id='493'
    
WHERE (e.entity_type_id '11')

GROUP BY `e`.`entity_id`

But I can’t manage to get that result by using the methods of the collection.

Here is my code :

$collection Mage::getResourceModel('sales/order_collection')

     ->
addAttributeToSelect('*')

     ->
joinField('product_id','sales/order_item','product_id','order_id=entity_id',null'left')

     ->
joinAttribute('mon_attribut''catalog/product''product_id'null'left')

     ->
addExpressionAttributeToSelect'total_mon_attribut''SUM({{mon_attribut}})*{{qty_ordered}}''mon_attribut' ) )

     ->
groupByAttribute('entity_id');

Does anybody know what I do wrong ?

Thanks a lot.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top