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?

52

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

  • 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? – webnoob Nov 4 '13 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 – Fabian Blechschmidt Nov 4 '13 at 18:02
  • 2
    worth to mention that joinTable method is only available for Mage_Eav_Model_Entity_Collection – Fra Jul 14 '17 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.

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.