Best way to do an Inner Join using the Zend Framework?

Question

It seems like there's a few different ways to join two tables using the Zend Framework, but I've never done it before so I don't know which is the best way to do it.

This is what I'm trying to do...

I have 3 tables in my database:

users
    ( id , name )
groups
    ( id , name )
group_members
    ( id , group_id , user_id )

I'm trying to look up the groups that a user belongs to and display that to the user. This SQL statement pretty much does the job (though there may be a better way to write it). It only returns the columns I'm concerned with which are the group's id and title.

    SELECT groups.id, groups.title
    FROM group_members
        INNER JOIN groups
        ON groups.id = group_members.group_id
    WHERE user_id = $userId

How can I do this with the Zend Framework?

Decalogue

Answer 1

Finally figured out how to do it. If you've got a better way, please let me know.

$db = Zend_Db_Table::getDefaultAdapter(); //set in my config file
$select = new Zend_Db_Select($db);
$select->from('groups', array('id', 'title')) //the array specifies which columns I want returned in my result set
    ->joinInner(
        'group_members',
        'groups.id = group_members.group_id',
        array()) //by specifying an empty array, I am saying that I don't care about the columns from this table
    ->where('user_id = ?', $userId);
$resultSet = $db->fetchAll($select);

This will return a table with only the id and title columns. The empty array() was the key to removing the columns I didn't care about. I could then do something with the result set.

foreach ($resultSet as $row) {
    //do something with $row->id or $row->title
}

Decalogue

Answer 2

No need to using Join,we can use Zend_Db_Table instead for the reason about the MVC pattern. I got this idea form here,#10 by Filip.(maybe they call this "Table Data Gateway"?)

Fortnightly