Magento – How to deal with 61 tables join limit when building magento collection

The problem is that the return value of getSelect() is a Varien_Db_Select object, but what you want is a Varien_Data_Collection_Db object (or something that extends that class like Mage_Core_Model_Resource_Db_Collection_Abstract).

The code can be fixed very simply by separating this into two lines.

// Load collection and apply filters
$collection = Mage::getModel('myModule/permission')->getCollection()
    ->addFieldToSelect('unique_role_identifier')
    ->addFieldToFilter('user_name',array('eq'=>'userToMatch@FindMe.com'))
    ;

// Join role table
$collection->getSelect()
    ->join('theroles',
        'main_table.unique_role_identifier = theroles.unique_role_identifier',
        array('role'=>'role_description'))
    ;

// Iterate collection
foreach ($collection as $c) {         
    echo '<h3>' . $c->getRoleDescription() . '</h3>';
}

If you were to var_dump($collection) (without casting it to a string) you'd see that in the above code you're returning an object Mage_Core_Model_Resource_Db_Collection_Abstract, whereas in the original code you'd see Varien_Db_Select (which is just an extension of Zend_Db_Select and is not a result set).

We can use the join like this:

            $joinConditions = 'u.sku = e.sku';

            $productColelction->getSelect()->join(
                ['u' => $productColelction->getTable('user_product_table')],
                $joinConditions,
                []
            );

Multi conditions:

        $joinConditions[] = "u.sku = e.sku";

        $joinConditions[] = "e.type_id= 'simple' ";

        $joinConditions = implode(
            ' AND ', $joinConditions
        );