Using different table alias with getCollection()

Question

I'm trying to build a subquery using Zend/Magento's way to build queries.

I'm getting the collection this way: $collection = Mage::getModel('sales/order_item')->getCollection();

This will generate the SQL similar to this one:

SELECT `main_table`.* FROM `sales_flat_order_item` AS `main_table`

It there any way to set a different table alias instead of main_table?

In other words, is there any way to specify the custom alias for the table in collection.

Answer 1

So this main_table section is added in the abstract collection Mage_Core_Model_Resource_Db_Collection_Abstract.

protected function _initSelect()
{
    $this->getSelect()->from(array('main_table' => $this->getMainTable()));
    return $this;
}

Sadly throughout magento it is assumed that the main table with have the alias main_table so I would say when dealing with a magento collection it would be tough/fruitless to change this. If you want to see how bad it gets simply grep for main_table :(

If you change this function to set the alias to your_alias then you will get and error as there is a filter added on store_id with the main_table alias so you do not get very far.

But if you are building your own sql then you can simply pass in the alais and table to the from function of the select.

$select = $adapter->select()->from(array('your_alias' => 'your_table'))