How do you iterate the resultset of a join?

Question

I'm working with custom models in Magento, I need to populate a grid and in order to do that I need to join 2 tables, I used collections and the SQL seems to be right as a vardump will print the expected query, but I don't know how to access to the fields as when I iterate the collection nothing prints out.

Here is my code:

    $collection = Mage::getModel('myModule/permission')->getCollection()
    ->addFieldToSelect('unique_role_identifier')
    ->addFieldToFilter('user_name',array('eq'=>'[email protected]'))
    ->getSelect()
    ->join('theroles',
            'main_table.unique_role_identifier = theroles.unique_role_identifier',
            array('role'=>'role_description'));
    var_dump((string) $collection); // prints the correct query
foreach($collection as $c){         
            echo '<h3>'.$c->getRoleDescription().'</h3>'; //prints nothing          
        }

Any help would be greatly appreciated.

Answer 1

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'=>'[email protected]'))
    ;

// 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).