How To: Using symfony to add a join between tables

1. simple join two tables

purpose:
generate sql like:   

select * from photo p
    left join artist a on p.artist_id = a.artist_id
      where a.genre = 'something' and p.genre = 'something'

code:           

if(!CriteriaUtil::hasJoin($criteria, ArtistPeer::TABLE_NAME)){
    $criteria->addJoin(PhotoPeer::ARTIST_ID, ArtistPeer::ARTIST_ID, Criteria::LEFT_JOIN);
}
$criteria->add(ArtistPeer::GENRE, $genre);    
$criteria->add(PhotoPeer::GENRE, $genre);

2. join two tables, add AND OR between conditions
purpose:
generate sql like:    

select * from photo p
    left join artist a on p.artist_id = a.artist_id
      where (a.genre = 'some' or p.genre='something')
        and a.name = 'something'

code:   

if(!CriteriaUtil::hasJoin($criteria, ArtistPeer::TABLE_NAME)){
   $criteria->addJoin(PhotoPeer::ARTIST_ID, ArtistPeer::ARTIST_ID, Criteria::LEFT_JOIN);
}
$criteria->add(ArtistPeer::GENRE, $genre);
$c = $criteria->getCriterion(ArtistPeer::GENRE);
if($c != null){
   $c->addOr($criteria->getNewCriterion(PhotoPeer::GENRE, $genre));
}
$criteria->add(ArtistPeer::NAME, $name);

 

Note:
It's a good habit to check if we have joined the table already. to check this, you can use the following util class, it get all the joined tables, and check if the table exists in them.

class CriteriaUtil{
    public static function hasJoin($c, $table_name){
        $joins = $c->getJoins();
        if($joins != null){
            foreach($joins as $join){
                if($join->getRightTableName() == $table_name){
                    return true;
                }
                if($join->getLeftTableName() == $table_name){
                    return true;
                }
            }
        }
        return false;
    }
}