0
require_once('../app/Mage.php');
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

$collection = Mage::getModel('sales/order')->getCollection()
                ->addAttributeToFilter('status', array('neq' => 'complete'))
                ->addAttributeToFilter('status', array('neq' => 'canceled'))
                ->addAttributeToFilter('status', array('neq' => 'closed'))
                ->addAttributeToSort('status', 'ASC')->load();

$collection->getSelect()->joinLeft(array(
    'web' => $collection->getTable('web/web')),
    'web.order_original_id = main_table.entity_id',
    array(
        'web_duedate' => 'duedate',
        'web_ordertype_id' => 'ordertype_id',
        'web_shipbydate' => 'shipbydate',
    )
);

// Show the SQL created
echo $collection->getSelect();

SELECT `main_table`.*, `web`.`duedate` AS `web_duedate`,
     `web`.`ordertype_id` AS `web_ordertype_id`,
     `web`.`shipbydate` AS `web_shipbydate`
FROM `sales_flat_order` AS `main_table` 
LEFT JOIN `web` ON web.order_original_id = main_table.entity_id WHERE (status != 'complete') 
AND (status != 'canceled') AND (status != 'closed') ORDER BY status ASC

I then try to extract the data...

foreach ($collection as $order) {

    // $data = $order->getData();
    // var_dump($data);

    $shipDate = $order->getWebShipbydate();
    var_dump($shipDate);

}

My problem is, If I run the SQL that is created, it gives me all the results and data that I need. However I am unable to get the web_byshipdate column in the collection data, this is a table that is JOINed on but my code below does not allow me to access it. Also if I var_dump the result, it shows all the columns in the result up until the ship date columns that are added on. IT's as if they are missing.

This is confusing since running the war SQL does include those columns. Anyone have any suggestions on how to get the data that is added on with the JOIN?

UPDATE

Running var_dump($order->debug()); outputs this below for each record. paypal_ipn_customer_notified is the last key, so it is not including the JOINED Table in this output.

array(76) {
  ["entity_id"]=>
  string(3) "788"
  ["state"]=>
  string(10) "processing"
  ["status"]=>
  string(8) "assembly"
  ["protect_code"]=>
  string(6) "99728f"
  ["shipping_description"]=>
  string(20) "Free Shipping - Free"
  ["is_virtual"]=>
  string(1) "0"
  ["store_id"]=>
  string(1) "1"
  ["customer_id"]=>
  string(3) "705"
  ["base_discount_amount"]=>
  string(6) "0.0000"
  ["base_discount_invoiced"]=>
  string(6) "0.0000"
  ["base_grand_total"]=>
  string(9) "1250.0000"
  ["base_shipping_amount"]=>
  string(6) "0.0000"
  ["base_shipping_invoiced"]=>
  string(6) "0.0000"
  ["base_shipping_tax_amount"]=>
  string(6) "0.0000"
  ["base_subtotal"]=>
  string(9) "1250.0000"
  ["base_subtotal_invoiced"]=>
  string(9) "1250.0000"
  ["base_tax_amount"]=>
  string(6) "0.0000"
  ["base_tax_invoiced"]=>
  string(6) "0.0000"
  ["base_to_global_rate"]=>
  string(6) "1.0000"
  ["base_to_order_rate"]=>
  string(6) "1.0000"
  ["base_total_invoiced"]=>
  string(9) "1250.0000"
  ["base_total_invoiced_cost"]=>
  string(6) "0.0000"
  ["base_total_paid"]=>
  string(9) "1250.0000"
  ["discount_amount"]=>
  string(6) "0.0000"
  ["discount_invoiced"]=>
  string(6) "0.0000"
  ["grand_total"]=>
  string(9) "1250.0000"
  ["shipping_amount"]=>
  string(6) "0.0000"
  ["shipping_invoiced"]=>
  string(6) "0.0000"
  ["shipping_tax_amount"]=>
  string(6) "0.0000"
  ["store_to_base_rate"]=>
  string(6) "1.0000"
  ["store_to_order_rate"]=>
  string(6) "1.0000"
  ["subtotal"]=>
  string(9) "1250.0000"
  ["subtotal_invoiced"]=>
  string(9) "1250.0000"
  ["tax_amount"]=>
  string(6) "0.0000"
  ["tax_invoiced"]=>
  string(6) "0.0000"
  ["total_invoiced"]=>
  string(9) "1250.0000"
  ["total_paid"]=>
  string(9) "1250.0000"
  ["total_qty_ordered"]=>
  string(6) "1.0000"
  ["customer_is_guest"]=>
  string(1) "0"
  ["customer_note_notify"]=>
  string(1) "1"
  ["billing_address_id"]=>
  string(4) "1575"
  ["customer_group_id"]=>
  string(1) "1"
  ["email_sent"]=>
  string(1) "1"
  ["quote_id"]=>
  string(4) "1800"
  ["shipping_address_id"]=>
  string(4) "1576"
  ["base_shipping_discount_amount"]=>
  string(6) "0.0000"
  ["base_subtotal_incl_tax"]=>
  string(9) "1250.0000"
  ["base_total_due"]=>
  string(6) "0.0000"
  ["shipping_discount_amount"]=>
  string(6) "0.0000"
  ["subtotal_incl_tax"]=>
  string(9) "1250.0000"
  ["total_due"]=>
  string(6) "0.0000"
  ["weight"]=>
  string(6) "4.0000"
  ["increment_id"]=>
  string(9) "100000694"
  ["base_currency_code"]=>
  string(3) "USD"
  ["customer_email"]=>
  string(17) "[email protected]"
  ["customer_firstname"]=>
  string(4) "Don "
  ["customer_lastname"]=>
  string(7) "Wallace"
  ["global_currency_code"]=>
  string(3) "USD"
  ["order_currency_code"]=>
  string(3) "USD"
  ["remote_ip"]=>
  string(14) "108.178.106.25"
  ["shipping_method"]=>
  string(25) "freeshipping_freeshipping"
  ["store_currency_code"]=>
  string(3) "USD"
  ["store_name"]=>
  string(50) "Main Website
Main Website Store
Default Store View"
  ["created_at"]=>
  string(19) "2013-06-28 20:14:30"
  ["updated_at"]=>
  string(19) "2013-09-09 15:08:12"
  ["total_item_count"]=>
  string(1) "1"
  ["hidden_tax_amount"]=>
  string(6) "0.0000"
  ["base_hidden_tax_amount"]=>
  string(6) "0.0000"
  ["shipping_hidden_tax_amount"]=>
  string(6) "0.0000"
  ["base_shipping_hidden_tax_amnt"]=>
  string(6) "0.0000"
  ["base_shipping_hidden_tax_amount"]=>
  string(6) "0.0000"
  ["hidden_tax_invoiced"]=>
  string(6) "0.0000"
  ["base_hidden_tax_invoiced"]=>
  string(6) "0.0000"
  ["shipping_incl_tax"]=>
  string(6) "0.0000"
  ["base_shipping_incl_tax"]=>
  string(6) "0.0000"
  ["paypal_ipn_customer_notified"]=>
  string(1) "0"
}

2 Answers 2

1

Your problem is that you are loading collection with ->load() first and joining tables afterwards.

In fact you don't need to call ->load() at all as the collection will be automatically loaded on the first iteration of foreach loop.

Thanks to MagePsycho for the hint.

Additionally you can simplify this:

$collection = Mage::getModel('sales/order')->getCollection()
            ->addAttributeToFilter('status', array('neq' => 'complete'))
            ->addAttributeToFilter('status', array('neq' => 'canceled'))
            ->addAttributeToFilter('status', array('neq' => 'closed'))
            ->addAttributeToSort('status', 'ASC');

into:

$collection = Mage::getModel('sales/order')->getCollection()
            ->addAttributeToFilter('status', array('nin' => array('complete', 'canceled', 'closed'))
            ->addAttributeToSort('status', 'ASC');
2
  • is $collection...->load() really necessary here?
    – MagePsycho
    Sep 15, 2013 at 6:30
  • Yes, you are right. This is the issue. Answer updated. Sep 15, 2013 at 6:32
1

I would try to simplify it first without specifying columns on the JOIN, so remove this part

array(
    'web_duedate' => 'duedate',
    'web_ordertype_id' => 'ordertype_id',
    'web_shipbydate' => 'shipbydate',
)

and then use $order->getShipbydate();

What is the output of var_dump($order->debug());?

1
  • Thanks for the suggestions. I removed the array from my JOIN. I then tried running var_dump($order->debug()); I have added the output from that to my question above. It seems it is not including the Joined table's columns into that result. Also $order->getShipbydate(); is returning null for everything
    – JasonDavis
    Sep 15, 2013 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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