Magento - How to use the JoinTable function in EE 1.9

Question

We have a problem after upgrading to 1.9.1 EE of magento. On a custom script we used to make a join with another table by this way, and it always worked fine.

$collection->joinTable('sales_flat_order_item','order_id=entity_id', array('sku', 'qty_ordered', 'qty_invoiced', 'udropship_vendor') , 'sales_flat_order_item.udropship_vendor="'.$this->vendorid.'"', 'right');
$collection->groupByAttribute(array('entity_id')); 

But after the upgrade we've got the error message: Fatal error: Call to undefined method Mage_Sales_Model_Mysql4_Order_Collection::joinTable().

Does anyone know what to do?

Answer 1

Order Collection now is represented by a flat table. Thus you can add filters via standard Varien_Db_Select property of this collection:

$select = $collection->getSelect();
$select->join(
        array('o_item' => 'sales_flat_order_item'),
        'o_item.order_id = main_table.entity_id AND o_item.udropship_vendor = "' . $this->vendorid . '"',
        array('sku', 'qty_ordered', 'qty_invoiced', 'udropship_vendor')
    )
    ->group('main_table.entity_id');

It is the answer to this question.

However, additionally I should point to some issues in the code snippet, you gave in the question:

1. It's better to overwrite this collection for your store and put this logic into newly created public method of a collection model. It is better than manipulate collection's fields directly.
2. The join identifier 'right' was never permitted for joining the EAV collection tables. Internally, as unknown identifier, it was mapped to 'inner join'. It is very different from possibly intended 'right outer join'. Should you rewrite logic? Or does 'inner join' act as intended?
3. The 'sku', 'qty_ordered', 'qty_invoiced' fields are ambiguous. They are not reliable as they are taken from unknown order item (or even from different items), that was joined to the order by 'udropship' condition.
4. It's possible, that you should quote '$this->vendorid', if it's a string. Or cast it to int, if it's integer. It is not safe just to concat vendor id to the string and surround it with double quotes.