3

I am trying to get all the orders for the specific product by that product_id, and customers that have bought that product. The query at the moment is not working as it should - this is getting all orders. If I put $productId to be non-existing product there is no result, but if I put real product I am getting all the orders. Code:

require_once('app/Mage.php'); //Path to Magento
umask(0);
Mage::app();

$productId = 297;


$orders = Mage::getModel('sales/order')->getCollection();
$orders
     ->getSelect()
     ->joinInner(array('order_item' => Mage::getSingleton('core/resource')->getTableName('sales/order_item'), 'order_item.order_id = main_table.entity_id' ))
     ->where('product_id=?', $productId)
     ->group('main_table.entity_id')
     ->order('main_table.entity_id DESC');

$header = 'date,company,name,city,address,postcode,itemname,quantity,price,sku';
$fields = '';
foreach($orders as $order):

     //$order->getData();
     $order->getIncrementId();
     $billingAddress = $order->getBillingAddress();
     $shippingAddress = $order->getShippingAddress();
     $billingStreet = $billingAddress->getStreet();
     $fields .=
          date('Y-m-d',strtotime($order->getCreatedAt())).','.
          $billingAddress->getCompany().','.
          $billingAddress->getName().','.
          $billingAddress->getCity().','.
          $billingStreet[0].','.
          $billingAddress->getPostcode().','.
          $order->getShippingDescription() .',';

          foreach ($order->getAllItems() as $itemId => $item):
               $itemname =  $item->getName();
               $itemname =  str_replace('&', " ", $itemname);
               $fields .=
                    $itemname. ','.
                    $item->getQtyOrdered().','.
                    $item->getPrice().','.
                    $item->getSku().',';

          endforeach;
     $fields = rtrim($fields, ',');
     $fields .= "\n";    

endforeach;
print "$header\n$fields";
2

hi try this use leftjoint

 $orders
     ->getSelect()
    ->joinLeft(
    array('order_item' =>Mage::getSingleton('core/resource')->getTableName('sales/order_item')),
    'order_item.order_id = main_table.entity_id',
    array('product_id'=>'product_id'))
    ->where('product_id=?', $productId)
    ->group('main_table.entity_id')
     ->order('main_table.entity_id DESC')
     ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.