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)) ; 
