0

As magento storing data in multiple tables is quiet difficult to get all the data required using pure SQL. Found some examples but none actually getting details I need about the order. What I would like to get based on order number is all information about payment and payment status, total amount but no products or quantities using only SQL. How to do it ? Or is it possible to use Magento then display whole query in SQL ?

I tried this:

    $torders = Mage::getModel('sales/order')
        ->getCollection()
        ->addAttributeToSelect('*')
        ->addAttributeToSort('increment_id', 'DESC')
        ->addAttributeToFilter('main_table.created_at', array('from' => $from,'to' => $now))
        ->addAttributeToFilter('status', 'processing')
        ->join(array('payment' => 'sales/order_payment'),'main_table.entity_id=payment.parent_id',array('payment_method' =>'payment.method'))
        //->addAttributeToSort('increment_id', 'DESC')
        ->addAttributeToFilter('main_table.created_at', array('from' => $from,'to' => $now))
        ;

------ I commented out some lines because if enabled getting 500 error ------

    echo $torders->getSelect()->__toString(); <--- this 

gives me a nice SQL query but how to add all the details about payment method ? how to JOIN payment details into "collection" and filter by selected payment method (ex: paypal) ?

I think I got it, don't know yet why it won't work with array " $torders = Mage::getModel('sales/order') ->getCollection() ->addAttributeToSelect('*') ->addAttributeToSort('increment_id', 'DESC') ->addAttributeToFilter('main_table.created_at', array('from' => $from,'to' => $now)) //->addAttributeToFilter('status', array('nin' => array('canceled'))"

because i need all the orders except cancelled but ... eventually I can use a conditions in a foreach loop later.

Final version (working):

$torders = Mage::getModel('sales/order')
        ->getCollection()
        ->addAttributeToSelect('*')
        ->addAttributeToSort('increment_id', 'DESC')
        ->addAttributeToFilter('main_table.created_at', array('from' => 
          $from,'to' => $now))
        ->addFieldToFilter('payment.method', array('eq' => 
          'customercredit'))
        ->addFieldToFilter('state', 'processing')
        //->addAttributeToFilter('status', array('nin' => array('canceled'))
        ->join(array('payment' => 'sales/order_payment'), 
        'main_table.entity_id=payment.parent_id', array('payment_method' 
         =>'payment.method'))
        ->addAttributeToSort('increment_id', 'DESC')
        ->addAttributeToFilter('main_table.created_at', array('from' => 
         $from,'to' => $now))
        ;
0

you can apply this code

 $torders = Mage::getModel('sales/order')->getCollection()
    ->addAttributeToSelect('*')
    ->load();
    foreach ($torders as $order)              
  {
    echo $order;
 }
  • No no no, what I meant is i would like to add payment details into the query before "foreach loop". I'm doing it now like this, so I can get payment details but not while the collection is created. – Rob D. A. Jan 9 '18 at 9:13
  • Maybe my question wasn't precise so I updated it a bit. – Rob D. A. Jan 9 '18 at 9:16

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.