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) "newerpass@aol.com"
  ["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"
}
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');
| improve this answer | |
  • is $collection...->load() really necessary here? – MagePsycho Sep 15 '13 at 6:30
  • Yes, you are right. This is the issue. Answer updated. – user487772 Sep 15 '13 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());?

| improve this answer | |
  • 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 '13 at 0:22

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.