27

I am doing the following to get some orders out of the system for export:

$orders = Mage::getModel('sales/order')->getCollection() ->addFieldToFilter('status', $statusToExport) ->addFieldToFilter('store_id', $this->processingStoreId) ->addFieldToFilter('updated_at', array('gteq' => date('Y-m-d H:i:s', $lastSyncTime)));

I need to add something in where it doesn't export if the order entity_id is in a custom table I have. If I was using SQL, I would do:

left join myTable as mt on main_table.entity_id = mt.entity_id where mt.entity_id is null

But I am not sure how to modify the collection query to do a similar thing.

Note: I did try 

$orders = $orders->getSelect() ->joinLeft( array("t1" => $myTable), "main_table.entity_id = t1.entity_id", array("admin_field_id" => "t1.id") ) ->where("t1.id is null")

but this changes it so it's a query and I want the sales/order collection returned.

I feel I am missing something simple ...

EDIT

Ok, I have tried this:

$orders->getSelect() ->joinLeft( array("t1" => $myTable), "main_table.entity_id = t1.entity_id", array("admin_field_id" => "t1.id") ) ->where("t1.id is null");

When I echo (string)$orders->getSelect() it returns the query I would expect and returns no results when I run it. $orders however, still contains items. I thought this join was meant to modify the collection at this point?

Improve this question

2 Answers 2

Reset to default
54

To change the query you can do 

$collection->getSelect()->doWhateverYouWantWithSelectObject(); foreach($collection as $order) {} // order collection

But to join a table, you can just use $collection->joinTable()

joinTable only exists on Mage_Eav_Model_Entity_Collection, this means: products, categories, orders, invoices, creditmemo, shipping, customers. (Thanks @Fra for the comment)

joinTable()

But magento collections have a method joinTable() it took me 45min to fizzle out how it is used. To avoid this for you, I share it.

$productCollection->joinTable( array('bonus' => 'mycompany/bonus'), 'product_id=entity_id', array('bonus_id' => 'bonus_id') );

The parameters are:

public function joinTable($table, $bind, $fields = null, $cond = null, $joinType = 'inner')
  1. Table is easy, it is the magento namespace/entity format, which you use in your configuration, resource models and the collection. You can use an array of the format array('alias' => 'namespace/entity')
  2. Bind means, the ON statement in your SQL. This was the hardest part and I will explain it in details later. It is important to have your flat table BEFORE and your EAV table AFTER the equal sign. Don't use main_table. before the attribute. Magento will do this for you. More on this later.
  3. Fields is an array. If you use a string instead, you get this: Warning: Invalid argument supplied for foreach() in /var/www/magento-1.6.2.0/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php on line 775. You can use an array of the format array('field1', 'field2', '...') or array('alias' => 'field1', '...')
  4. Condition is a ** WHERE ON condition** in the SQL.
  5. joinType. I hope you know what it is. But you have only the choice between LEFT and INNER. app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php:793

More on this topic

Improve this answer
3
  • 1
    Thanks for that great info. Based on your first point, shouldn't $orders->getSelect() ->joinLeft(array("t1" => $myTable), "main_table.entity_id = t1.entity_id", array("admin_field_id" => "t1.id")) ->where("t1.id is null") exclude the items from my current list? Commented Nov 4, 2013 at 14:15
  • I think you edit the collection after loading. just hook into your script with xdebug and check the isLoaded flag inside the model Commented Nov 4, 2013 at 18:02
  • 2
    worth to mention that joinTable method is only available for Mage_Eav_Model_Entity_Collection Commented Jul 14, 2017 at 16:35
1

I found the reason for this.

I wanted a count of values before I applied the left join so I added

$totalOrderCount = count($orders);

this however caused the collection to load which in turn meant it ignored the left join I was doing further on. Whilst debugging, looking at the var _isCollectionLoaded was a good tip by Fabian.

I am now doing this instead to get the count of the collection:

$countOrdersObjs = clone $orders; $totalOrderCount = count($countOrdersObjs); unset($countOrdersObjs);

This allows me to count the results without loading $orders collection.

Note: Please apply any upvotes to Fabians post above as he guided me on this but I felt the solution warranted it's own answer.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.