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.


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'))
| improve this answer | |
  • BTW: the simple $collection->getSelect()->from(array('ord_i' => $collection->getMainTable())); working in my case. Thanks. – Kostanos Jul 9 '14 at 23:42
  • You'll need to add the ->reset(Zend_Db_Select::FROM) before ->from(..) too. – Kostanos Jul 9 '14 at 23:47

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.