Using join with multiple table in zend social engine

Question

I want to join multiple table more than 3 and remove duplicate rows.

Answer 1

Sample query for

$table = Engine_Api::_ ()->getDbtable ( 'topics', 'group' );
$topicselect = $table->select ()->where ( 'group_id = ?', $group_id );

$topics = $table->fetchAll ( $topicselect );
$topicCount = count ( $topics );

$photoTable = Engine_Api::_ ()->getItemTable ( 'group_photo' );
$photoselect = $photoTable->select ()->where ( 'group_id = ?', $group_id );

$photos = $photoTable->fetchAll ( $photoselect );
$photoCount = count ( $photos );

$groupTable = Engine_Api::_ ()->getItemTable ( 'group' );
$groupInfo = $groupTable->info ( 'name' );
$storage = Engine_Api::_ ()->getDbtable ( 'files', 'storage' );
$storage_files = $storage->info ( 'name' );
$groupMembershipTable = Engine_Api::_ ()->getDbtable ( 'membership', 'group' );
$groupMembershipTableName = $groupMembershipTable->info ( 'name' );
$category_table = Engine_Api::_ ()->getDbtable ( 'categories', 'group' );
$categoryInfo = $category_table->info ( 'name' );

$select = $groupTable->select ()->setIntegrityCheck ( false )->from ( array (
        'group' => $groupInfo 
) )->joinLeft ( array (
        'category' => $categoryInfo 
), 'category.category_id =group.category_id', array (
        'category.title AS categoryname' 
) )->joinLeft ( array (
        'membership' => $groupMembershipTableName 
), "membership.resource_id = group.group_id and membership.user_id= $user_id", array ( 
) )->joinLeft ( array (
        'storage' => $storage_files 
), "storage.file_id=group.photo_id", array (
        'storage.storage_path' 
) )->where ( 'group.group_id = ?', $group_id )->order ( "storage.modified_date DESC" );
// ->group('group.group_id');

$group = $groupTable->fetchAll ( $select );