Magento Forum

   
SQL Query
 
BoostMyShop
Sr. Member
 
Avatar
Total Posts:  152
Joined:  2008-06-11
France
 

hello smile

i’m trying to retrieve the qty of a product used in all orders.

In SQL, this would be:

select product_idsumqty_ordered )
from sales_flat_order_item 
where product_id 
XXX
group by product_id

However, i dont know how to translate it in Magento code smile

I tried this:

mage::getModel('sales/order_item')->getCollection()
            ->
addAttributeToSelect('*')
            ->
getSelect()
            ->
from(array(), array('nb'  => 'sum(sales_flat_order_item.qty_ordered )'))
            ->
group('main_table.product_id');

But i got an error “Call to undefined method Mage_Sales_Model_Mysql4_Order_Item_Collection::addAttributeToSelect() “

thanks for any help smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
BoostMyShop
Sr. Member
 
Avatar
Total Posts:  152
Joined:  2008-06-11
France
 

i succeed to get the result like this:

$sql "SELECT
                    product_id,
                    sum(qty_ordered) as nb
                FROM
                    sales_flat_order_item 
                WHERe
                    product_id = 44
                GROUP BY
                    product_id
               "
;

        
$data mage::getResourceModel('sales/order_item_collection')->getConnection()->fetchAll($sql);
         foreach (
$data as $item
         
{
             
echo "<p>".$item['nb'];
         
}


        
echo mage::getResourceModel('sales/order_item_collection')
            ->
addAttributeToSelect('name')
            ->
addFieldToFilter('product_id','132')
            ->
getSelect();

But there must be a better way to get it....

 
Magento Community Magento Community
Magento Community
Magento Community
 
nikefido
Guru
 
Avatar
Total Posts:  481
Joined:  2008-07-11
New Haven, CT
 

Where are you writing this code (what class, and what class does it extend)?

 
Magento Community Magento Community
Magento Community
Magento Community
 
BoostMyShop
Sr. Member
 
Avatar
Total Posts:  152
Joined:  2008-06-11
France
 
nikefido - 11 August 2008 04:22 AM

Where are you writing this code (what class, and what class does it extend)?

it doesnt matter as i use mage::getmodel.
However, i’d like to add that method in :

class MDN_MageCustomization_Model_Catalog_Product extends Mage_Catalog_Model_Product

 
Magento Community Magento Community
Magento Community
Magento Community
 
nikefido
Guru
 
Avatar
Total Posts:  481
Joined:  2008-07-11
New Haven, CT
 

I just ran your original (first) code snippet in Magento ver. 1.0.19870.4 and got no error, but than ran it in Magento ver. 1.1.2 and ran into your error....(I also was trying to wade through the muck of code to find Mage_Sales_Model_Mysql4_Order_Item_Collection )

Anyway, don’t have any real solutions for you other than what you figured out, but thought Id point out this difference in versions.

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