Magento1 Collection Query – How to Perform a Left Join

collection;magento-1query

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?

Best Answer

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

Related Topic